current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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.

share|improve this question
4  
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 Jan 2 '13 at 9:16
1  
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♦ Jan 2 '13 at 9:20
add comment

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
add comment
up vote 4 down vote

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.
share|improve this answer
    
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. –  Richard Collette Aug 16 '13 at 14:50
    
@Richard even if you dispose? –  Marc Gravell♦ Aug 16 '13 at 18:32
    
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 Aug 16 '13 at 19:26
    
My comment above is incorrect in that cancel should be called on the IDbCommand, not on the reader. –  Richard Collette Aug 16 '13 at 19:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.