Slow performance of SqlDataReader

Question

I've query executing ~2 secs in MSSMS (returning 25K of rows)

Same query used in .NET (sqlReader) exetuting few minutes!

I've also tried to execute only reader

(commented all code in while loop just leaving reader.Read() ) - still same!

Any idea what's up?

Answer 1

I would check to see how long the actual retrieval is taking.

for instance:

  Private Sub timeCheck()
    'NOTE: Assuming you have a sqlconnection object named conn

    'Create stopwatch
    Dim sw As New System.Diagnostics.Stopwatch

    'Setup query
    Dim com As New SqlClient.SqlCommand("QUERY GOES HERE", conn)

    sw.Start()

    'Run query
    Dim dr As SqlClient.SqlDataReader = com.ExecuteReader()

    sw.Stop()

    'Check the time
    Dim sql_query_time As String = CStr((sw.ElapsedMilliseconds / 1000)) & " seconds"
  End Sub

This will allow you to see whether the hold-up is in the retrieval, or in the execution of the reader.

Answer 2

If you ar executing the reader in a loop, where it executes many times, then make sure you are using CommandBehavior.CloseConnection

    SqlCommand cmd = new SqlCommand();
    SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

If you don't, each time the loop processes the line, when it finishes and the rdr and the connection object drops out of scope, the connection object will not be explicitly closed, so it will only get closed and released back to the pool when the Garbage Collector finally gets around to finalizing it...

Then, if your loop is fast enough, (which is very likely), you will run out of connections. (The pool has a maximum limit it can generate)

This will cause extra latency and delays as the code keeps creating extra unnecessary connections, (up to the maximum) and waiting for the GC to "catch up" with the loop that is using them...

Answer 3

I would set up a trace in SQL Server Profiler to see what SET options settings the connection is using when connecting from .NET code, and what settings are being used in SSMS. By SET options settings, I mean

ARITHABORT
ANSI_NULLS
CONCAT_NULL_YIELDS_NULL
//etc

Take a look at MSDN for a table of options

I have seen the problem before where the options were different (in that case, ARITHABORT) and the performance difference was huge.

Answer 4

Also, query analyzer does not download the full contents of the large text or large binary fields. Your SqlDataReader could take longer because it does download the full contents.

Answer 5

I'm not DBA and not priviledged to play with Profiler - will ask my DBA and let all know.

In the meantime I'm noticed essential performance boost after adding "WITH RECOMPILE" param to SP I'm talking

So, from my perspective it seems to be the case with execution plan... What do you think?

[EDIT] Also what I've checked was performing below query from QA and .NET

select @@options

My understanding is it shall return same value for both environements. (If not differnet ex.plans will be used) Am I right?

[EDIT2] I've read (from http://www.sqldev.net/misc/fn_setopts.htm) that ARITHABOIRT=ON in QA (in .NET it is off)

Does enybody know how to force ARITHABOIRT=ON for every .NET connections?

Answer 6

I had that problem to. Tick the "arithmetic abort" setting in the Connection Settings of the DB server.