faster than SqlConnection and SqlDataReader in .net

Question

I use this code:

    Dim connection As SqlConnection
Dim connetionString As String
Dim sqlq As String = "select c.* from(..."
    connetionString = "Data Source=...;Initial Catalog=...;User ID=...;Password=..."
    connection = New SqlConnection(connetionString)
                track3.Text = "Connection... " & DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")
        Using connection
            connection.Open()
                track4.Text = "SqlCommand... " & DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")
            Dim command As SqlCommand = New SqlCommand(sqlq, connection)
                track5.Text = "SqlDataReader... " & DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")
            Dim reader As SqlDataReader = command.ExecuteReader()
                track6.Text = "Filling RTB... " & DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")
            If reader.HasRows Then
                ........
                Do While reader.Read()
                    .......
            reader.Close()
        End Using
                track7.Text = "Done " & DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")

And I have noticed that command 'command.ExecuteReader()' consumes most time. This time range is between 1 to 19 seconds sometimes, which is too long for me. Is there any better way to do what I do? I need to read some data from database and display it (not everything what is received from DB) on rich text box.

What profiling have you done? My guess is that your query (which clearly isn't just "select c.* from(...") is slow... but we can't possibly give you advice on how to speed it up without knowing more about the query. What happens if you execute the same query in SQL server management studio? And what does this question have to do with RichTextBox? — Jon Skeet
– Jon Skeet, Commented Jan 2, 2013 at 9:16
Also: read about the Using statement, and use it. Your connection, command and reader are all IDisposable, and should all have their own Using statement. — Marc Gravell
– Marc Gravell, Commented Jan 2, 2013 at 9:20

Marc Gravell · Accepted Answer · 2013-01-02 09:18:58Z

4

There are various performance parts of ExecuteReader:

the performance of the query itself (this impacts the latency in particular, i.e. the time that ExecuteReader takes to start getting data)
the amount of data returned (this impacts the bandwidth, the row-count, and the number of iterations of your While loop)
the performance of your row-processing code (the code inside the While loop)

From your description, it sounds like the first is the problem. This has absolutely nothing to do with ADO.NET, and everything to do with your query. So: write a better query, or add appropriate indexing.

Other things that can impact this:

high server load / network load
locking from other connections
different SET configuration (ADO.NET has different SET defaults to the SSMS window, so the performance can differ wildly in some cases)
parameter sniffing; if you are running an atypical query at some point (very biased data), a bad query plan can be cached - in which case the OPTIMIZE FOR query hint can help, if you have confirmed that parameter sniffing is the issue.

answered Jan 2, 2013 at 9:18

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Richard Collette Over a year ago

When you close the reader, at least a sqlclient one, prior to reading all rows, the close operation will finish reading the remaining rows from the database. This is addressed by calling cancel on the reader. This has driven people batty because it isn't apparent through straight code debugging. It is clearly documented but often overlooked because you don't see many examples where Cancel is used. I'm not sure why MS didn't execute a cancel on close when there were remaining rows.

Marc Gravell Over a year ago

@Richard even if you dispose?

Richard Collette Over a year ago

Absolutely. This gist is my test code (gist.github.com/rcollette/6252733) I ran it against a table with 4k records of Google Analytics data. Here are the results (sorry can't paste in a nice picture) gist.github.com/rcollette/6252767 Using RedGate Ants, it will drill down into the FCL code and you can clearly see the iterations happening during dispose. Using Sql Profiler you'll also see the extra data over the wire.

Richard Collette Over a year ago

My comment above is incorrect in that cancel should be called on the IDbCommand, not on the reader.

Codo · Accepted Answer · 2013-01-02 09:17:51Z

2

If ExecuteReader() is your performance bottleneck, then you'll need to write better SQL queries, better organize your data in the database (e.g. set up indexes) and generally optimze things on the database.

You can't do much on the client side becuase ExecuteReader() basically just sends the query to the database and waits for the result to arrive.

answered Jan 2, 2013 at 9:17

Codo

79.7k18 gold badges176 silver badges218 bronze badges

Collectives™ on Stack Overflow

faster than SqlConnection and SqlDataReader in .net

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related