Query Analyzer speed vs. SqlCommand.ExecuteReader speed

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

Jon
Wed Jun 16 16:36:51 CDT 2004

John Linn <JohnLinn@discussions.microsoft.com> wrote:
> 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?

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

--
Jon Skeet - <skeet@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

JohnLinn
Wed Jun 16 19:31:03 CDT 2004

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

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

Jon
Thu Jun 17 00:10:19 CDT 2004

John Linn <JohnLinn@discussions.microsoft.com> wrote:
> >
> > How are you executing the SqlCommand? Are you using the same command
> > each time, or a new one? Have you called Prepare?
> >
>
> 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.

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

--
Jon Skeet - <skeet@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

JohnLinn
Thu Jun 17 07:48:02 CDT 2004

Jon,

> Then that's quite possibly the problem. Cache your command and reuse
> it, and call Prepare too.

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

Jon
Thu Jun 17 08:12:44 CDT 2004

John Linn <JohnLinn@discussions.microsoft.com> wrote:
> > Then that's quite possibly the problem. Cache your command and reuse
> > it, and call Prepare too.
>
> By this do you mean utilizing API Cache?

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

--
Jon Skeet - <skeet@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too