CODEWITHSUNDEEP
.netsqldatareadersqlconnection
babboon
babboon

Reputation: 793

faster than SqlConnection and SqlDataReader in .net

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.

Upvotes: 1

Views: 1099

Answers (2)

Marc Gravell
Marc Gravell

Reputation: 1063774

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.

Upvotes: 4

Codo
Codo

Reputation: 78955

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.

Upvotes: 2

Related Questions