locked
sp_executesql extremely slow RRS feed

  • Question

  • I have a query with some parameters when executed in sp_executesql it's extremely slow (so slow I havn't let it run until it's done yet), when I execute the query direct it takes a couple of seconds. It's a select with some joins and matching ids from a subquery, nothing odd.

    First of all I'd like to know why sp_executesql is so much slower.

    Second, since I use parametrized queries in ADO.Net it automatically uses sp_executesql. Is there a way to still use parametrized queries in ADO.Net and not use sp_executesql?

    • Moved by Tom Phillips Monday, May 31, 2010 4:45 PM TSQL Question (From:SQL Server Database Engine)
    Monday, May 31, 2010 1:19 PM

Answers

  • Second, since I use parametrized queries in ADO.Net it automatically uses sp_executesql. Is there a way to still use parametrized queries in ADO.Net and not use sp_executesql?

    Note that the ADO.NET SqlClient API does not wrap the statement in sp_executesql.  SQL Trace shows RPC calls in this way so that you can easily see both the parameterized statement and supplied values.  I touched on this a little in a blog post a while back http://weblogs.sqlteam.com/dang/archive/2008/02/18/Why-Parameters-are-a-Best-Practice.aspx.

    With a parameterized query, the existing execution plan use reused regardless of the current values supplied.  This can be a problem when the current plan is suboptimal for the current values.  Query hingts (e.g. RECOMPILE for OPTIMIZE FOR) can address that issue.

    Another possible issue is data type mismatches between the parameters and the column data types, resulting in non-sargable expressions and a bad plan.  Please post your query and table DDL if you need further help.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Proposed as answer by Naomi N Monday, May 31, 2010 5:40 PM
    • Marked as answer by KJian_ Friday, June 4, 2010 8:21 AM
    Monday, May 31, 2010 5:16 PM

All replies

  • Would say, try using SQL Server profiler and see the query plan in Performance event. Also, CPU, Reads, Writes and Duration column can be used to see the difference. Compare the performance for both sp_executesql and direct SQL.

    Hope this will help to some extent.

    Regards

    Santy

     


    MS SQL Server DBA
    Monday, May 31, 2010 4:25 PM
  • Second, since I use parametrized queries in ADO.Net it automatically uses sp_executesql. Is there a way to still use parametrized queries in ADO.Net and not use sp_executesql?

    Note that the ADO.NET SqlClient API does not wrap the statement in sp_executesql.  SQL Trace shows RPC calls in this way so that you can easily see both the parameterized statement and supplied values.  I touched on this a little in a blog post a while back http://weblogs.sqlteam.com/dang/archive/2008/02/18/Why-Parameters-are-a-Best-Practice.aspx.

    With a parameterized query, the existing execution plan use reused regardless of the current values supplied.  This can be a problem when the current plan is suboptimal for the current values.  Query hingts (e.g. RECOMPILE for OPTIMIZE FOR) can address that issue.

    Another possible issue is data type mismatches between the parameters and the column data types, resulting in non-sargable expressions and a bad plan.  Please post your query and table DDL if you need further help.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Proposed as answer by Naomi N Monday, May 31, 2010 5:40 PM
    • Marked as answer by KJian_ Friday, June 4, 2010 8:21 AM
    Monday, May 31, 2010 5:16 PM
  • Thanks, I'll try some query hints and see if I get different results.
    Tuesday, June 1, 2010 3:14 PM