SQLCommand calls sp_executesql implicitly

[Aamir Mahmood]

Hi,

I am using SQLCommand object to create datasets and open data readers.

I found that on various occassions the same query if I run in Management
Studio runs instantaneously, but within code that query takes too long to
execute.

To investigate I started looking at the SQL Server Profiler, I found a weird
issue.
All my queries that I run through SQLCommand are converted into
"sp_executesql" call.

Why is this happening, and how can I avoid it.

When I run same queries from Management Studio, the profiler shows query
being run without the wrapper of sp_executesql.

Please help.

Thanks.
AM.

 

[Frans Bouma [C# MVP]]

Hi,

I am using SQLCommand object to create datasets and open data readers.

I found that on various occassions the same query if I run in Management
Studio runs instantaneously, but within code that query takes too long to
execute.

To investigate I started looking at the SQL Server Profiler, I found a weird
issue.
All my queries that I run through SQLCommand are converted into
"sp_executesql" call.

Why is this happening, and how can I avoid it.

When I run same queries from Management Studio, the profiler shows query
being run without the wrapper of sp_executesql.

sp_executesql is used by the SqlCommand execute methods to be sure the
compiled execution plan of the query is cached by the server after the
connection is closed so other executions of the same query are re-using
the compiled execution plan.

that the query is 'very fast' in management studio doesn't mean
anything, as executing the same query multiple times simply returns the
same results.

For more information, please see the documentation on sp_executesql in
BOL. You can't get rid of it when executing dynamic sql nor should you
want to: it makes your applications run faster.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------