strong performance problem with a parameterized query
1 réponse
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 ?).
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire
Veuillez sélectionner un problème
Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
Sylvain Lafontaine
Bad query plans: try adding the "With Recompile" option to the stored procedures. Don't forget to look at the statistics for the indexes.
-- Sylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF
"Silvere" wrote in message news:
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 ?).
Bad query plans: try adding the "With Recompile" option to the stored
procedures. Don't forget to look at the statistics for the indexes.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Silvere" <Silvere@discussions.microsoft.com> wrote in message
news:B475EEFB-5D9D-40DF-BC10-36E69E4F5A5D@microsoft.com...
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 ?).
Bad query plans: try adding the "With Recompile" option to the stored procedures. Don't forget to look at the statistics for the indexes.
-- Sylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF
"Silvere" wrote in message news:
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 ?).
