Any way to avoid using SP_EXECUTESQL in ADO.NET?

13 Jul 2006 7:21 PM

Brad P

Hi,

Our development team has encountered some performance problems when
using ADO.NET (using the SqlCommand and SqlDataAdapter objects) to
perform queries on large data tables. We've drilled into the issue
considerably, and the problem seems to be related to how ADO.NET uses
the SP_EXECUTESQL when running text queries. I say this because when we
run our query in Query Analyzer by itself the performance is less than
a second, but when running it with the SP_EXECUTESQL command (which is
what Profiler is telling us is being used), it takes nearly half a
minute.

>From what I know about SP_EXECUTESQL, it uses dynamic cursors to

execute the command it is given. Our query involves User Defined
Functions (UDFs) that call UDFs, Group By statements, and Aggregate
functions that are not necessarily good for dynamic cursors (or at
least that is what I have heard), so the issue seems to make sense.

Anyways, what I was wondering was whether or not there is a way to have
ADO.NET run our query without using SP_EXECUTESQL. If this is not
possible, then I am interested in knowing whether or not it is a bad
practice to have UDFs calling other UDFs, as opposed to having a larger
query that does all the work but is not as reusable.

Thanks for your time

13 Jul 2006 7:44 PM

David Browne

Show quote

"Brad P" <br***@cu.net> wrote in message
news:1152818503.486755.310220@h48g2000cwc.googlegroups.com...
> Hi,
>
> Our development team has encountered some performance problems when
> using ADO.NET (using the SqlCommand and SqlDataAdapter objects) to
> perform queries on large data tables. We've drilled into the issue
> considerably, and the problem seems to be related to how ADO.NET uses
> the SP_EXECUTESQL when running text queries. I say this because when we
> run our query in Query Analyzer by itself the performance is less than
> a second, but when running it with the SP_EXECUTESQL command (which is
> what Profiler is telling us is being used), it takes nearly half a
> minute.
> . . .

It is unlikely that SP_EXECUTESQL is the real cause of your problem. It is
more likely that you are getting a sub-opitmal execution plan when running
SP_EXECUTESQL because of the different amount of statistical evidence
available to the optimizer.

When you run the queries in Query Analyzer are you using local variables and
parameter markers the same way SP_EXECUTESQL is? What are the parameter
values passed, and are they statistically typical or atypical?

David

13 Jul 2006 10:02 PM

Brad P

Thanks for the response,

When we run the queries in Query Analyzer we are directly inserting the
parameter values into the query. From what I can tell, the parameter
values themselves are typical for the data we are working with. Does
SP_EXECUTESQL use statistics differently than running the raw query in
Query Analyzer?

Thanks,

Brad P.

David Browne wrote:
Show quote

> "Brad P" <br***@cu.net> wrote in message
> news:1152818503.486755.310220@h48g2000cwc.googlegroups.com...
> > Hi,
> >
> > Our development team has encountered some performance problems when
> > using ADO.NET (using the SqlCommand and SqlDataAdapter objects) to
> > perform queries on large data tables. We've drilled into the issue
> > considerably, and the problem seems to be related to how ADO.NET uses
> > the SP_EXECUTESQL when running text queries. I say this because when we
> > run our query in Query Analyzer by itself the performance is less than
> > a second, but when running it with the SP_EXECUTESQL command (which is
> > what Profiler is telling us is being used), it takes nearly half a
> > minute.
> > . . .
>
> It is unlikely that SP_EXECUTESQL is the real cause of your problem. It is
> more likely that you are getting a sub-opitmal execution plan when running
> SP_EXECUTESQL because of the different amount of statistical evidence
> available to the optimizer.
>
> When you run the queries in Query Analyzer are you using local variables and
> parameter markers the same way SP_EXECUTESQL is? What are the parameter
> values passed, and are they statistically typical or atypical?
>
> David

13 Jul 2006 11:16 PM

David Browne

"Brad P" <br***@cu.net> wrote in message
news:1152828152.024295.156500@h48g2000cwc.googlegroups.com...
> Thanks for the response,
>
> When we run the queries in Query Analyzer we are directly inserting the
> parameter values into the query. From what I can tell, the parameter
> values themselves are typical for the data we are working with. Does
> SP_EXECUTESQL use statistics differently than running the raw query in
> Query Analyzer?
>

No, but plugging in the values directly into the SQL is different than using
parameters. When you use parameters SQL Server will compile a query plan
that is reused for subsequent queries with the same parameters, so it has to
make guesses about what the parameter values are going to be. It will
either use statistical information about the columns or use the "sniff" the
parameter values from the first time the query is run.

When you hard-code the values SQL builds a plan that is optimized for those
exact parameter values, and is never reused. This is bad because compiling
plans is expensive and the memory used to cache plans is limited. It is
sometimes good, however, for expensive queries when getting the best
possible plan is more important than reducing query plan compilation and
maximizing plan reuse.

See

Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

David

14 Jul 2006 4:38 AM

JT

Hi Brad,
If you want to avoid SP_EXECUTESQL, set the CommandType property of the
SQLCommand to CommandType.StoredProcedure. This should work for both sproc's
and UDF's. You would want to do this BEFORE adding any command params.

From Chapter 12 - Improving ADO.NET Performance of Patterns and Practices...

Use CommandType.StoredProcedure with SqlCommand
If you are using the SqlCommand object, use CommandType.StoredProcedure when
you call stored procedures. Do not use CommandType.Text because it requires
extra parsing. The following code fragment shows how to set the CommandType
property to avoid extra parsing on the server.

SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand("UpdateCustomerProcedure", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconUsingStoredProceduresWithCommand.asp

--
John

Show quote

"Brad P" wrote:

> Hi,
>
> Our development team has encountered some performance problems when
> using ADO.NET (using the SqlCommand and SqlDataAdapter objects) to
> perform queries on large data tables. We've drilled into the issue
> considerably, and the problem seems to be related to how ADO.NET uses
> the SP_EXECUTESQL when running text queries. I say this because when we
> run our query in Query Analyzer by itself the performance is less than
> a second, but when running it with the SP_EXECUTESQL command (which is
> what Profiler is telling us is being used), it takes nearly half a
> minute.
>
> >From what I know about SP_EXECUTESQL, it uses dynamic cursors to
> execute the command it is given. Our query involves User Defined
> Functions (UDFs) that call UDFs, Group By statements, and Aggregate
> functions that are not necessarily good for dynamic cursors (or at
> least that is what I have heard), so the issue seems to make sense.
>
> Anyways, what I was wondering was whether or not there is a way to have
> ADO.NET run our query without using SP_EXECUTESQL. If this is not
> possible, then I am interested in knowing whether or not it is a bad
> practice to have UDFs calling other UDFs, as opposed to having a larger
> query that does all the work but is not as reusable.
>
> Thanks for your time
>
>