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 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 at 9:20

2 Answers

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
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

Your Answer

 
or
required, but never shown
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.