strong performance problem with a parameterized query

21 Jul 2006 8:49 AM

Silvere

We have a strong performance problem with a quite complex query.
The query executes in approximately 7 seconds in Query Analyser (which is
okay for us), but takes much more time from ADO.NET : it timesout after 30
sec, or after 240 sec if we increase the timeout to that value.

The ADO query is a parameterized query using 4 parameters. If it is replaced
by a parameterless query (parameters are then hard-copied in the query
string), the query takes 11 seconds from ADO.NET.

If we use sp_executesql (used by ADO.NET with parameterized requests) to
execute the query from Query analyser, the query takes a lot of time too
(also killed after 4 minutes).

The ADO.NET command used is ExecuteReader.

It seems the sp_executesql command is responsible for the large amount of
time. We would rather avoid using parameterless queries (design & securities
issues).

The problem doesn't seem to come from bad indexes (the SQL server index
tuning wizard shows that there is no suggestion for new indexes). The
execution plan is correct. We couldn't have an execution plan for the
sp_executesql request (the execution plan returned is 'execute
sp_executesql', with no more information.

Is it possible to fix this problem or do you know a workaround
(desactivating sp_executesql ?).

21 Jul 2006 9:01 AM

Omnibuzz

Why don't you replace the values with local variables and then run the query
in QA and see.

--
-Omnibuzz (The SQL GC)

http://omnibuzz-sql.blogspot.com/

Show quote

"Silvere" wrote:

> We have a strong performance problem with a quite complex query.
> The query executes in approximately 7 seconds in Query Analyser (which is
> okay for us), but takes much more time from ADO.NET : it timesout after 30
> sec, or after 240 sec if we increase the timeout to that value.
>
> The ADO query is a parameterized query using 4 parameters. If it is replaced
> by a parameterless query (parameters are then hard-copied in the query
> string), the query takes 11 seconds from ADO.NET.
>
> If we use sp_executesql (used by ADO.NET with parameterized requests) to
> execute the query from Query analyser, the query takes a lot of time too
> (also killed after 4 minutes).
>
> The ADO.NET command used is ExecuteReader.
>
> It seems the sp_executesql command is responsible for the large amount of
> time. We would rather avoid using parameterless queries (design & securities
> issues).
>
> The problem doesn't seem to come from bad indexes (the SQL server index
> tuning wizard shows that there is no suggestion for new indexes). The
> execution plan is correct. We couldn't have an execution plan for the
> sp_executesql request (the execution plan returned is 'execute
> sp_executesql', with no more information.
>
> Is it possible to fix this problem or do you know a workaround
> (desactivating sp_executesql ?).
>

21 Jul 2006 9:22 AM

Erland Sommarskog

Silvere (Silv***@discussions.microsoft.com) writes:

> We have a strong performance problem with a quite complex query.
> The query executes in approximately 7 seconds in Query Analyser (which is
> okay for us), but takes much more time from ADO.NET : it timesout after 30
> sec, or after 240 sec if we increase the timeout to that value.
>
> The ADO query is a parameterized query using 4 parameters. If it is
> replaced by a parameterless query (parameters are then hard-copied in
> the query string), the query takes 11 seconds from ADO.NET.
>
> If we use sp_executesql (used by ADO.NET with parameterized requests) to
> execute the query from Query analyser, the query takes a lot of time too
> (also killed after 4 minutes).

When you use hard-coded values in a query, SQL Server will use these
exact values to determine the query plan.

When you use a parameterised query (including stored procedures), the
optimizer sniffs the parameters when the query is first executed, and
uses these parameters as guidance. This plan is cached, so that later
invocations can use the same plan.

This goes wrong if the first invocation uses atypical parameters so you
get a plan which is not good for normal business.

There are a number of ways to attack the problem, but before I go
into speculations that may be completely irrelvant, I would prefer if
you posted the code of the query, both the parameterised version and
the unparameterised version.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx