7

I have a view in my database with around 200k entries. I'm reading it with the following code:

using (SqlConnection conn = new SqlConnection....)
{
    conn.Open();

    string query = "SELECT * FROM SP3DPROJECT.dbo.XSystemHierarchy";

    using (SqlCommand comm = new SqlCommand())
    {
        comm.CommandText = query;
        comm.Connection = conn;

        using (SqlDataReader reader = comm.ExecuteReader())
        {
            DateTime start = DateTime.Now;

            while (reader.Read())
            {
                // Code goes here, but for performance 
                // test I'm letting this empty
            }

            DateTime end = DateTime.Now;
            TimeSpan elapsed = (end- start).TotalSeconds;
        }
    }
}

The view has only 2 columns of GUID type. The ExecuteCommand() is very fast, but the while loop (even with no code, just the looping) takes ~150 seconds.

Is there a better or fast way to do this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
rbasniak
  • 4,484
  • 11
  • 51
  • 100
  • 2
    DataSet will not be any faster, since filing a DataSet uses a DataAdapter which is implemented on top of SqlDataReader.Read(). So filling a DataSet does strictly more work than RBasniak's code. – Jared Moore May 14 '15 at 20:03
  • What about the view itself? How quickly does selecting the rows from your view happen? – Sean Lange May 14 '15 at 20:07
  • Why do you need all rows at the client side? – Alex May 14 '15 at 20:07
  • What do you mean by ExecuteCommang() is fast? The code here uses ExecuteReader and it holds a open connection until done. So the time taken by the while loops includes the execution time as well. Try adding proper index and keys to the table. – Kar May 14 '15 at 20:08
  • @Kar, how is an index going to help on a "select *" without a "where" clause? – Alex May 14 '15 at 20:09
  • A covered index does, although not sure if it is needed for 200K records – Kar May 14 '15 at 20:18
  • 3
    [DateTime is not the recommended way to performance test code](http://stackoverflow.com/questions/28637/is-datetime-now-the-best-way-to-measure-a-functions-performance). It's possible to have extermely varying results with it. – Erik Philips May 14 '15 at 20:21
  • 4
    @ErikPhilips: That is true, but when the result is 150 seconds, it's not because of the low precision of the clock timer. – Guffa May 14 '15 at 20:25
  • @Guffa unless you only ran it once and during the run the machine updated the local clock. It's always best to just use the correct way. Anyone reading this thinking it's a good production way to performance test code will see code runing in negative time or over an hour during daylights savings. – Erik Philips May 14 '15 at 20:28
  • 2
    My money is on your view being expensive. – Zer0 May 14 '15 at 20:29
  • There are not really any any bulk select techniques available, however there are bulk update and insert techniques. Your select statement's bottleneck is the transport. Unless you come up with a way to compress the data and send it as a lump sum and then unpack and iterate, however, that is out of scope for this solution. – Ross Bush Apr 30 '21 at 02:23

2 Answers2

0

Try using the Load method on a DataTable. It is significantly faster.

Example:

DataTable dt = new DataTable();
dt.Load(cmd.ExecuteReader());
return dt;
Marc Johnston
  • 1,276
  • 1
  • 7
  • 16
  • The `Load` method is also reading from the `DataReader`, why do you think that it is faster? – Guffa May 14 '15 at 21:17
  • Maybe this helps: http://stackoverflow.com/questions/14999143/sqldatareader-vs-sqldataadapter-which-one-is-better-for-returning-datatable-perf – Marc Johnston May 14 '15 at 21:57
  • I would agree with what's said in the first answer; "The difference will be negligible". – Guffa May 14 '15 at 22:21
  • Note that 200,000 rows with 2 columns of GUIDs, which is 16 bytes in length is 6.4mb of data across the wire. Check to see if the bottleneck is the volume of data. If your client is running on the same box as the database, using named pipes might help. – Marc Johnston May 14 '15 at 22:48
0

Thank you all. The comments led me to think about reading the view vs table and I changed the query to the exact same query used to make the view in SQL Server (instead of reading all the contents of the view directly).

Instead of using SELECT * FROM MyView, I went to SQL Server, opened the view in design mode, and copied the SQL query that was generated for me. Then I used that query in my code.

I have no idea why this made so much difference, but now the time needed to get all the data dropped from 150s to less than 2s, which is more than enough for my purposes.

Connor Low
  • 5,900
  • 3
  • 31
  • 52
rbasniak
  • 4,484
  • 11
  • 51
  • 100
  • 1
    please add more details to your answer, this answer makes me sift through all the comments above and understand what was discussed..! – Amit May 15 '15 at 19:51
  • 1
    @Amit instead of using SELECT * FROM MyView, I went to SQL Server opened the view in design mode and copied the SQL query that it generates for me. Then I used that query in my code. – rbasniak May 19 '15 at 14:40