Query Analyzer speed vs. SqlCommand.ExecuteReader speed

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

All,

I have an SP that takes about 5 seconds to run before any caching performed by SQL server (I run DBCC DROPCLEANBUFFERS to make sure in my tests).

When I hook the SP into a SqlCommand object, the SP Cmd.ExecuteReader line takes about 5 seconds every time I reload the page. But if I run it through Query Analyzer it takes 5 seconds for the first run and every subsequent request is less than half a second. I assume this is behavior of Sql Server caching the tables/results.

My question is why would Cmd.ExecuteReader take 5 seconds each time if Sql Server is indeed caching teh tables/results (as evidenced by the fast subsequent QA calls)? It is possible there is SET environment variable being called by SqlCommand, or a lack of variable in the connection string that causes the ExecuteReader to take so long each time?

Thanks,

JL
 
John Linn said:
I have an SP that takes about 5 seconds to run before any caching
performed by SQL server (I run DBCC DROPCLEANBUFFERS to make sure in
my tests).

When I hook the SP into a SqlCommand object, the SP Cmd.ExecuteReader
line takes about 5 seconds every time I reload the page. But if I run
it through Query Analyzer it takes 5 seconds for the first run and
every subsequent request is less than half a second. I assume this is
behavior of Sql Server caching the tables/results.

My question is why would Cmd.ExecuteReader take 5 seconds each time
if Sql Server is indeed caching teh tables/results (as evidenced by
the fast subsequent QA calls)? It is possible there is SET
environment variable being called by SqlCommand, or a lack of
variable in the connection string that causes the ExecuteReader to
take so long each time?
Click to expand...

How are you executing the SqlCommand? Are you using the same command
each time, or a new one? Have you called Prepare?
 
How are you executing the SqlCommand? Are you using the same command
each time, or a new one? Have you called Prepare?
Click to expand...

Jon,

Here is the VB code to execute the SP:

....variable setup...
Dim Cn As New System.Data.SqlClient.SqlConnection(Me.DbConnString)
Dim Cmd As New System.Data.SqlClient.SqlCommand("usp_select_summary", Cn)
Cmd.CommandType = CommandType.StoredProcedure
Cmd.Parameters.Add("@num_days", SqlDbType.Int).Value = 90
Cmd.Parameters.Add("@group_id", SqlDbType.Int).Value = 1
Cmd.Parameters.Add("@check_summary_visible", SqlDbType.Bit).Value = True
Trace.Write("Executing usp_select_summary (on params above)...")
Cn.Open()
Dim Dr As System.Data.SqlClient.SqlDataReader = Cmd.ExecuteReader()
Trace.Write("Complete.") <--- this output indicated that it took 5 seconds to get here since the previous Trace.
....code to Dr.Read() and cleanup...

No, I don't currently utilize Prepare.

JL
 
John Linn said:
Jon,

Here is the VB code to execute the SP:

...variable setup...
Dim Cn As New System.Data.SqlClient.SqlConnection(Me.DbConnString)
Dim Cmd As New System.Data.SqlClient.SqlCommand("usp_select_summary", Cn)
Cmd.CommandType = CommandType.StoredProcedure
Cmd.Parameters.Add("@num_days", SqlDbType.Int).Value = 90
Cmd.Parameters.Add("@group_id", SqlDbType.Int).Value = 1
Cmd.Parameters.Add("@check_summary_visible", SqlDbType.Bit).Value = True
Trace.Write("Executing usp_select_summary (on params above)...")
Cn.Open()
Dim Dr As System.Data.SqlClient.SqlDataReader = Cmd.ExecuteReader()
Trace.Write("Complete.") <--- this output indicated that it took 5
seconds to get here since the previous Trace.
...code to Dr.Read() and cleanup...

No, I don't currently utilize Prepare.
Click to expand...

Then that's quite possibly the problem. Cache your command and reuse
it, and call Prepare too.
 
Jon,
Then that's quite possibly the problem. Cache your command and reuse
it, and call Prepare too.
Click to expand...

By this do you mean utilizing API Cache? How would this speed up calls on subsequent pages? Are there any affinity issues to keep a lookout for?

I'll add code for Prepare() right now.

JL
 
John Linn said:
By this do you mean utilizing API Cache?
Click to expand...

No. I mean by creating the command once and storing it in, say, a
static variable. There's no need to create the command separately each
time unless the SQL itself changes, and I'm assuming you're using
appropriately parameterised SQL which doesn't change between calls
(with only the parameter values changing instead).
 
Back
Top