SQL Server Query fast but slow from procedure

Question

A query runs fast   DECLARE  SessionGUID uniqueidentifier SET  SessionGUID    BCBA333C-B6A1-4155-9833-C495F22EA908   SELECT   FROM Report Opener WHERE SessionGUID    SessionGUID ORDER BY CurrencyTypeOrder  Rank   subtree cost  0 502  But putting the same SQL in a stored procedure runs slow  and with a totally different execution plan  CREATE PROCEDURE dbo ViewOpener  SessionGUID uniqueidentifier AS SELECT   FROM Report Opener WHERE SessionGUID    SessionGUID ORDER BY CurrencyTypeOrder  Rank  EXECUTE ViewOpener  SessionGUID   Subtree cost  19 2  I ve run  sp recompile ViewOpener   And it still runs the same  badly   and I ve also changed the stored  procedure to  CREATE PROCEDURE dbo ViewOpener  SessionGUID uniqueidentifier AS SELECT     recompile please  FROM Report Opener WHERE SessionGUID    SessionGUID ORDER BY CurrencyTypeOrder  Rank   And back again  trying to really trick it into recompiling   I ve dropped and recreated the stored procedure in order to get it to generate a new plan   I ve tried forcing recompiles  and prevent parameter sniffing  by using a decoy variable   CREATE PROCEDURE dbo ViewOpener  SessionGUID uniqueidentifier AS  DECLARE  SessionGUIDbitch uniqueidentifier SET  SessionGUIDbitch    SessionGUID  SELECT   FROM Report Opener WHERE SessionGUID    SessionGUIDbitch ORDER BY CurrencyTypeOrder  Rank   I ve also tried defining the stored procedure WITH RECOMPILE   CREATE PROCEDURE dbo ViewOpener  SessionGUID uniqueidentifier  WITH RECOMPILE AS SELECT   FROM Report Opener WHERE SessionGUID    SessionGUID ORDER BY CurrencyTypeOrder  Rank   So that it s plan is never cached  and I ve tried forcing a recompile at execute   EXECUTE ViewOpener  SessionGUID WITH RECOMPILE   Which didn t help   I ve tried converting the procedure to dynamic SQL   CREATE PROCEDURE dbo ViewOpener  SessionGUID uniqueidentifier  WITH RECOMPILE AS DECLARE  SQLString NVARCHAR 500   SET  SQLString   N SELECT      FROM Report OpenerTest    WHERE SessionGUID    SessionGUID    ORDER BY CurrencyTypeOrder  Rank   EXECUTE sp executesql  SQLString  N  SessionGUID uniqueidentifier    SessionGUID   Which didn t help   The entity  Report Opener  is a view  which is not indexed  The view only references underlying tables  No table contains computed columns  indexed or otherwise   For the hell of it I tried creating the view with   SET ANSI NULLS ON SET QUOTED IDENTIFER ON   That didn t fix it   How is it that   the query is fast moving the query to a view  and selecting from the view is fast selecting from the view from a stored procedure is 40x slower    I tried moving the definition of the view directly into the stored procedure  violating 3 business rules  and breaking an important encapsulation   and that makes it only about 6x slower   Why is the stored procedure version so slow  What can possibly account for SQL Server running ad-hoc SQL faster than a different kind of ad-hoc SQL    I d really rather not   embed the SQL in code change the code at all  Microsoft SQL Server  2000 - 8 00 2050  Intel X86  Mar  7 2008 21 29 56 Copyright  c  1988-2003 Microsoft Corporation Standard Edition on Windows NT 5 2  Build 3790  Service Pack 2     But what can account for SQL Server being unable to run as fast as SQL Sever running a query  if not parameter sniffing     My next attempt will be to have StoredProcedureA call StoredProcedureB call StoredProcedureC call StoredProcedureD to query the view   And failing that  have the stored procedure call a stored procedure  call a UDF  call a UDF  call a stored procedure  call a UDF to query the view     To sum up  the following run fast from QA  but slow when put into a stored procedure   The original   --Runs fine outside of a stored procedure SELECT   FROM Report OpenerTest WHERE SessionGUID    SessionGUID ORDER BY CurrencyTypeOrder  Rank   sp executesql   --Runs fine outside of a stored procedure DECLARE  SQLString NVARCHAR 500  SET  SQLString   N SELECT   FROM Report OpenerTest WHERE SessionGUID    SessionGUID ORDER BY CurrencyTypeOrder  Rank   EXECUTE sp executesql  SQLString          N  SessionGUID uniqueidentifier            SessionGUID   EXEC  sql    --Runs fine outside of a stored procedure DECLARE  sql NVARCHAR 500  SET  sql   N SELECT   FROM Report OpenerTest WHERE SessionGUID       CAST  SessionGUID AS varchar 50       ORDER BY CurrencyTypeOrder  Rank   EXEC  sql      Execution Plans  The good plan          --Sort ORDER BY   Expr1020  ASC   Currencies   Rank  ASC               --Compute Scalar DEFINE   Expr1020  If   Currencies   CurrencyType   ctCanadianCash   then 1 else If   Currencies   CurrencyType   ctMiscellaneous   then 2 else If   Currencies   CurrencyType   ctTokens   then 3 else If   Currencies   CurrencyType                   --Nested Loops Left Outer Join  OUTER REFERENCES   Openers   OpenerGUID                          --Filter WHERE     Currencies   IsActive  lt  gt 0 AND  Currencies   OnOpener  lt  gt 0  AND         Currencies   CurrencyType   ctUSCoin  OR  Currencies   CurrencyType   ctMiscellaneousUS   OR  Currencies   CurrencyType   ctUSCash   OR  Currencies                              --Nested Loops Left Outer Join  OUTER REFERENCES   Currencies   CurrencyGUID    Openers   OpenerGUID   WITH PREFETCH                                  --Nested Loops Left Outer Join                                       --Bookmark Lookup BOOKMARK   Bmk1016    OBJECT   GrobManagementSystemLive   dbo   Windows                                              --Nested Loops Inner Join  OUTER REFERENCES   Openers   WindowGUID                                                   --Bookmark Lookup BOOKMARK   Bmk1014    OBJECT   GrobManagementSystemLive   dbo   Openers                                                        --Index Seek OBJECT   GrobManagementSystemLive   dbo   Openers   IX Openers SessionGUID    SEEK   Openers   SessionGUID    SessionGUID   ORDERED FORWARD                                                 --Index Seek OBJECT   GrobManagementSystemLive   dbo   Windows   IX Windows    SEEK   Windows   WindowGUID   Openers   WindowGUID   ORDERED FORWARD                                       --Clustered Index Scan OBJECT   GrobManagementSystemLive   dbo   Currencies   IX Currencies CurrencyType                                    --Clustered Index Seek OBJECT   GrobManagementSystemLive   dbo   OpenerDetails   IX OpenerDetails OpenerGUIDCurrencyGUID    SEEK   OpenerDetails   OpenerGUID   Openers   OpenerGUID  AND  OpenerDetails   CurrencyGUID   Currenc                       --Hash Match Cache  HASH   Openers   OpenerGUID    RESIDUAL   Openers   OpenerGUID   Openers   OpenerGUID                               --Stream Aggregate DEFINE   Expr1006  SUM If      Currencies   CurrencyType   ctMiscellaneous  OR  Currencies   CurrencyType   ctTokens   OR  Currencies   CurrencyType   ctChips   OR  Currencies   CurrencyType   ctCanadianCoin   OR                                   --Nested Loops Inner Join  OUTER REFERENCES   OpenerDetails   CurrencyGUID   WITH PREFETCH                                       --Nested Loops Inner Join                                            --Index Seek OBJECT   GrobManagementSystemLive   dbo   Openers   IX Openers OneOpenerPerSession    SEEK   Openers   OpenerGUID   Openers   OpenerGUID   ORDERED FORWARD                                            --Clustered Index Seek OBJECT   GrobManagementSystemLive   dbo   OpenerDetails   IX OpenerDetails OpenerGUIDCurrencyGUID    SEEK   OpenerDetails   OpenerGUID   Openers   OpenerGUID   ORDERED FORWARD                                       --Index Seek OBJECT   GrobManagementSystemLive   dbo   Currencies   PK Currencies CurrencyGUID    SEEK   Currencies   CurrencyGUID   OpenerDetails   CurrencyGUID   ORDERED FORWARD    The bad plan          --Sort ORDER BY   Expr1020  ASC   Currencies   Rank  ASC                --Compute Scalar DEFINE   Expr1020  If   Currencies   CurrencyType   ctCanadianCash   then 1 else If   Currencies   CurrencyType   ctMiscellaneous   then 2 else If   Currencies   CurrencyType   ctTokens   then 3 else If   Currencies   Currency                   --Nested Loops Left Outer Join  OUTER REFERENCES   Openers   OpenerGUID                           --Filter WHERE     Currencies   IsActive  lt  gt 0 AND  Currencies   OnOpener  lt  gt 0  AND         Currencies   CurrencyType   ctUSCoin  OR  Currencies   CurrencyType   ctMiscellaneousUS   OR  Currencies   CurrencyType   ctUSCash   OR  Currenc                             --Nested Loops Left Outer Join  OUTER REFERENCES   Currencies   CurrencyGUID    Openers   OpenerGUID   WITH PREFETCH                                   --Filter WHERE   Openers   SessionGUID    SessionGUID                                          --Concatenation                                            --Nested Loops Left Outer Join                                                  --Table Spool                                                      --Hash Match Inner Join  HASH   Windows   WindowGUID     Openers   WindowGUID    RESIDUAL   Windows   WindowGUID   Openers   WindowGUID                                                              --Clustered Index Scan OBJECT   GrobManagementSystemLive   dbo   Windows   IX Windows CageGUID                                                              --Table Scan OBJECT   GrobManagementSystemLive   dbo   Openers                                                    --Table Spool                                                      --Clustered Index Scan OBJECT   GrobManagementSystemLive   dbo   Currencies   IX Currencies CurrencyType                                               --Compute Scalar DEFINE   Openers   OpenerGUID  NULL   Openers   SessionGUID  NULL   Windows   UseChipDenominations  NULL                                                   --Nested Loops Left Anti Semi Join                                                       --Clustered Index Scan OBJECT   GrobManagementSystemLive   dbo   Currencies   IX Currencies CurrencyType                                                         --Row Count Spool                                                           --Table Spool                                  --Clustered Index Seek OBJECT   GrobManagementSystemLive   dbo   OpenerDetails   IX OpenerDetails OpenerGUIDCurrencyGUID    SEEK   OpenerDetails   OpenerGUID   Openers   OpenerGUID  AND  OpenerDetails   CurrencyGUID   Cu                        --Hash Match Cache  HASH   Openers   OpenerGUID    RESIDUAL   Openers   OpenerGUID   Openers   OpenerGUID                                --Stream Aggregate DEFINE   Expr1006  SUM  partialagg1034     Expr1007  SUM  partialagg1035     Expr1008  SUM  partialagg1036     Expr1009  SUM  partialagg1037     Expr1010  SUM  partialagg1038     Expr1011  SUM  partialagg1039                                   --Nested Loops Inner Join                                        --Stream Aggregate DEFINE   partialagg1034  SUM If      Currencies   CurrencyType   ctMiscellaneous  OR  Currencies   CurrencyType   ctTokens   OR  Currencies   CurrencyType   ctChips   OR  Currencies   CurrencyType                                               --Nested Loops Inner Join  OUTER REFERENCES   OpenerDetails   CurrencyGUID   WITH PREFETCH                                                  --Clustered Index Seek OBJECT   GrobManagementSystemLive   dbo   OpenerDetails   IX OpenerDetails OpenerGUIDCurrencyGUID    SEEK   OpenerDetails   OpenerGUID   Openers   OpenerGUID   ORDERED FORWARD                                                  --Index Seek OBJECT   GrobManagementSystemLive   dbo   Currencies   PK Currencies CurrencyGUID    SEEK   Currencies   CurrencyGUID   OpenerDetails   CurrencyGUID   ORDERED FORWARD                                        --Index Seek OBJECT   GrobManagementSystemLive   dbo   Openers   IX Openers OneOpenerPerSession    SEEK   Openers   OpenerGUID   Openers   OpenerGUID   ORDERED FORWARD    The bad-one is eager spooling 6 million rows  the other one isn t      Note  This isn t a question about tuning a query  I have a query that runs lightning fast  I just want SQL Server to run fast from a stored procedure

User · Answer

Have you tried rebuilding the statistics and/or the indexes on the Report_Opener table. All the recomplies of the SP won't be worth anything if the stats still show data from when the database was first inauguarated.

The initial query itself works quickly because the optimiser can see that the parameter will never be null. In the case of the SP the optimiser cannot be sure that the parameter will never be null.

User · Answer

This may sound silly and seems obvious from the name SessionGUID  but is the column a uniqueidentifier on Report Opener  If not  you may want to try casting it to the correct type and give it a shot or declare your variable to the correct type   The plan created as part of the sproc may work unintuitively and do an internal cast on a large table

User · Answer

I had the same problem as the original poster but the quoted answer did not solve the problem for me  The query still ran really slow from a stored procedure     I found another answer here  Parameter Sniffing   Thanks Omnibuzz  Boils down to using  local Variables  in your stored procedure queries  but read the original for more understanding  it s a great write up  e g   Slow way   CREATE PROCEDURE GetOrderForCustomers  CustID varchar 20   AS BEGIN     SELECT        FROM orders     WHERE customerid    CustID END   Fast way   CREATE PROCEDURE GetOrderForCustomersWithoutPS  CustID varchar 20   AS BEGIN     DECLARE  LocCustID varchar 20      SET  LocCustID    CustID      SELECT        FROM orders     WHERE customerid    LocCustID END   Hope this helps somebody else  doing this reduced my execution time from 5  minutes to about 6-7 seconds

User · Answer

I found the problem  here s the script of the slow and fast versions of the stored procedure   dbo ViewOpener  RenamedForCruachan  Slow PRC  SET QUOTED IDENTIFIER OFF  GO SET ANSI NULLS OFF  GO  CREATE PROCEDURE dbo ViewOpener RenamedForCruachan Slow      SessionGUID uniqueidentifier AS  SELECT   FROM Report Opener RenamedForCruachan WHERE SessionGUID    SessionGUID ORDER BY CurrencyTypeOrder  Rank GO  SET QUOTED IDENTIFIER OFF  GO SET ANSI NULLS ON  GO   dbo ViewOpener  RenamedForCruachan  Fast PRC  SET QUOTED IDENTIFIER OFF  GO SET ANSI NULLS ON  GO  CREATE PROCEDURE dbo ViewOpener RenamedForCruachan Fast      SessionGUID uniqueidentifier  AS  SELECT   FROM Report Opener RenamedForCruachan WHERE SessionGUID    SessionGUID ORDER BY CurrencyTypeOrder  Rank GO  SET QUOTED IDENTIFIER OFF  GO SET ANSI NULLS ON  GO   If you didn t spot the difference  I don t blame you  The difference is not in the stored procedure at all  The difference that turns a fast 0 5 cost query into one that does an eager spool of 6 million rows   Slow  SET ANSI NULLS OFF  Fast  SET ANSI NULLS ON    This answer also could be made to make sense  since the view does have a join clause that says    table column IS NOT NULL    So there is some NULLs involved     The explanation is further proved by returning to Query Analizer  and running  SET ANSI NULLS OFF      DECLARE  SessionGUID uniqueidentifier SET  SessionGUID    BCBA333C-B6A1-4155-9833-C495F22EA908       SELECT   FROM Report Opener RenamedForCruachan WHERE SessionGUID    SessionGUID ORDER BY CurrencyTypeOrder  Rank   And the query is slow     So the problem isn t because the query is being run from a stored procedure  The problem is that Enterprise Manager s connection default option is ANSI NULLS off  rather than ANSI NULLS on  which is QA s default   Microsoft acknowledges this fact in KB296769  BUG  Cannot use SQL Enterprise Manager to create stored procedures containing linked server objects   The workaround is include the ANSI NULLS option in the stored procedure dialog   Set ANSI NULLS ON Go Create Proc spXXXX as

User · Answer

This is probably unlikely  but given that your observed behaviour is unusual it needs to be checked and no-one else has mentioned it   Are you absolutely sure that all objects are owned by dbo and you don t have a rogue copies owned by yourself or a different user present as well   Just occasionally when I ve seen odd behaviour it s because there was actually two copies of an object and which one you get depends on what is specified and who you are logged on as   For example it is perfectly possible to have two copies of a view or procedure with the same name but owned by different owners - a situation that can arise where you are not logged onto the database as a dbo and forget to specify dbo as object owner when you create the object     In note that in the text you are running some things without specifying owner  eg   sp recompile ViewOpener   if for example there where two copies of viewOpener present owned by dbo and  some other user  then which one you actually recompile if you don t specify is dependent upon circumstances   Ditto with the Report Opener view - if there where two copies  and they could differ in specification or execution plan  then what is used depends upon circumstances - and as you do not specify owner it is perfectly possible that your adhoc query might use one and the compiled procedure might use use the other   As I say  it s probably unlikely but it is possible and should be checked because your issues could be that you re simply looking for the bug in the wrong place

User · Answer

-- Here is the solution   create procedure GetOrderForCustomers  CustID varchar 20    as  begin  select   from orders  where customerid   ISNULL  CustID       end   -- That s it

User · Answer

Have you tried rebuilding the statistics and/or the indexes on the Report_Opener table. All the recomplies of the SP won't be worth anything if the stats still show data from when the database was first inauguarated.

The initial query itself works quickly because the optimiser can see that the parameter will never be null. In the case of the SP the optimiser cannot be sure that the parameter will never be null.

User · Answer

This is probably unlikely  but given that your observed behaviour is unusual it needs to be checked and no-one else has mentioned it   Are you absolutely sure that all objects are owned by dbo and you don t have a rogue copies owned by yourself or a different user present as well   Just occasionally when I ve seen odd behaviour it s because there was actually two copies of an object and which one you get depends on what is specified and who you are logged on as   For example it is perfectly possible to have two copies of a view or procedure with the same name but owned by different owners - a situation that can arise where you are not logged onto the database as a dbo and forget to specify dbo as object owner when you create the object     In note that in the text you are running some things without specifying owner  eg   sp recompile ViewOpener   if for example there where two copies of viewOpener present owned by dbo and  some other user  then which one you actually recompile if you don t specify is dependent upon circumstances   Ditto with the Report Opener view - if there where two copies  and they could differ in specification or execution plan  then what is used depends upon circumstances - and as you do not specify owner it is perfectly possible that your adhoc query might use one and the compiled procedure might use use the other   As I say  it s probably unlikely but it is possible and should be checked because your issues could be that you re simply looking for the bug in the wrong place

User · Answer

This is probably unlikely  but given that your observed behaviour is unusual it needs to be checked and no-one else has mentioned it   Are you absolutely sure that all objects are owned by dbo and you don t have a rogue copies owned by yourself or a different user present as well   Just occasionally when I ve seen odd behaviour it s because there was actually two copies of an object and which one you get depends on what is specified and who you are logged on as   For example it is perfectly possible to have two copies of a view or procedure with the same name but owned by different owners - a situation that can arise where you are not logged onto the database as a dbo and forget to specify dbo as object owner when you create the object     In note that in the text you are running some things without specifying owner  eg   sp recompile ViewOpener   if for example there where two copies of viewOpener present owned by dbo and  some other user  then which one you actually recompile if you don t specify is dependent upon circumstances   Ditto with the Report Opener view - if there where two copies  and they could differ in specification or execution plan  then what is used depends upon circumstances - and as you do not specify owner it is perfectly possible that your adhoc query might use one and the compiled procedure might use use the other   As I say  it s probably unlikely but it is possible and should be checked because your issues could be that you re simply looking for the bug in the wrong place

User · Answer

This may sound silly and seems obvious from the name SessionGUID  but is the column a uniqueidentifier on Report Opener  If not  you may want to try casting it to the correct type and give it a shot or declare your variable to the correct type   The plan created as part of the sproc may work unintuitively and do an internal cast on a large table

User · Answer

Though I m usually against it  though in this case it seems that you have a genuine reason   have you tried providing any query hints on the SP version of the query  If SQL Server is preparing a different execution plan in those two instances  can you use a hint to tell it what index to use  so that the plan matches the first one   For some examples  you can go here   EDIT  If you can post your query plan here  perhaps we can identify some difference between the plans that s telling   SECOND  Updated the link to be SQL-2000 specific  You ll have to scroll down a ways  but there s a second titled  Table Hints  that s what you re looking for   THIRD  The  Bad  query seems to be ignoring the  IX Openers SessionGUID  on the  Openers  table - any chance adding an INDEX hint to force it to use that index will change things

User · Answer

Do this for your database  I have the same issue - it works fine in one database but when I copy this database to another using SSIS Import  not the usual restore   this issue happens to most of my stored procedures  So after googling some more  I found the blog of Pinal Dave  which btw  I encountered most of his post and did help me a lot so thanks Pinal Dave    I execute the below query on my database and it corrected my issue   EXEC sp MSforeachtable  command1  print     DBCC DBREINDEX            80   GO EXEC sp updatestats GO    Hope this helps  Just passing the help from others that helped me

User · Answer

I was facing the same issue  amp  this post was very helpful to me but none of the posted answers solved my specific issue  I wanted to post the solution that worked for me in hopes that it can help someone else   https   stackoverflow com a 24016676 814299     At the end of your query  add OPTION  OPTIMIZE FOR   now UNKNOWN

User · Answer

I ve got another idea  What if you create this table-based function   CREATE FUNCTION tbfSelectFromView          -- Add the parameters for the function here      SessionGUID UNIQUEIDENTIFIER   RETURNS TABLE  AS RETURN        SELECT       FROM Report Opener     WHERE SessionGUID    SessionGUID     ORDER BY CurrencyTypeOrder  Rank   GO   And then selected from it using the following statement  even putting this in your SP    SELECT   FROM tbfSelectFromView  SessionGUID    It looks like what s happening  which everybody has already commented on  is that SQL Server just makes an assumption somewhere that s wrong  and maybe this will force it to correct the assumption  I hate to add the extra step  but I m not sure what else might be causing it

User · Answer

I ve got another idea  What if you create this table-based function   CREATE FUNCTION tbfSelectFromView          -- Add the parameters for the function here      SessionGUID UNIQUEIDENTIFIER   RETURNS TABLE  AS RETURN        SELECT       FROM Report Opener     WHERE SessionGUID    SessionGUID     ORDER BY CurrencyTypeOrder  Rank   GO   And then selected from it using the following statement  even putting this in your SP    SELECT   FROM tbfSelectFromView  SessionGUID    It looks like what s happening  which everybody has already commented on  is that SQL Server just makes an assumption somewhere that s wrong  and maybe this will force it to correct the assumption  I hate to add the extra step  but I m not sure what else might be causing it

User · Answer

Have you tried rebuilding the statistics and/or the indexes on the Report_Opener table. All the recomplies of the SP won't be worth anything if the stats still show data from when the database was first inauguarated.

The initial query itself works quickly because the optimiser can see that the parameter will never be null. In the case of the SP the optimiser cannot be sure that the parameter will never be null.

User · Answer

Though I m usually against it  though in this case it seems that you have a genuine reason   have you tried providing any query hints on the SP version of the query  If SQL Server is preparing a different execution plan in those two instances  can you use a hint to tell it what index to use  so that the plan matches the first one   For some examples  you can go here   EDIT  If you can post your query plan here  perhaps we can identify some difference between the plans that s telling   SECOND  Updated the link to be SQL-2000 specific  You ll have to scroll down a ways  but there s a second titled  Table Hints  that s what you re looking for   THIRD  The  Bad  query seems to be ignoring the  IX Openers SessionGUID  on the  Openers  table - any chance adding an INDEX hint to force it to use that index will change things

User · Answer

Though I m usually against it  though in this case it seems that you have a genuine reason   have you tried providing any query hints on the SP version of the query  If SQL Server is preparing a different execution plan in those two instances  can you use a hint to tell it what index to use  so that the plan matches the first one   For some examples  you can go here   EDIT  If you can post your query plan here  perhaps we can identify some difference between the plans that s telling   SECOND  Updated the link to be SQL-2000 specific  You ll have to scroll down a ways  but there s a second titled  Table Hints  that s what you re looking for   THIRD  The  Bad  query seems to be ignoring the  IX Openers SessionGUID  on the  Openers  table - any chance adding an INDEX hint to force it to use that index will change things

User · Answer

This is probably unlikely  but given that your observed behaviour is unusual it needs to be checked and no-one else has mentioned it   Are you absolutely sure that all objects are owned by dbo and you don t have a rogue copies owned by yourself or a different user present as well   Just occasionally when I ve seen odd behaviour it s because there was actually two copies of an object and which one you get depends on what is specified and who you are logged on as   For example it is perfectly possible to have two copies of a view or procedure with the same name but owned by different owners - a situation that can arise where you are not logged onto the database as a dbo and forget to specify dbo as object owner when you create the object     In note that in the text you are running some things without specifying owner  eg   sp recompile ViewOpener   if for example there where two copies of viewOpener present owned by dbo and  some other user  then which one you actually recompile if you don t specify is dependent upon circumstances   Ditto with the Report Opener view - if there where two copies  and they could differ in specification or execution plan  then what is used depends upon circumstances - and as you do not specify owner it is perfectly possible that your adhoc query might use one and the compiled procedure might use use the other   As I say  it s probably unlikely but it is possible and should be checked because your issues could be that you re simply looking for the bug in the wrong place

User · Answer

I had the same problem as the original poster but the quoted answer did not solve the problem for me  The query still ran really slow from a stored procedure     I found another answer here  Parameter Sniffing   Thanks Omnibuzz  Boils down to using  local Variables  in your stored procedure queries  but read the original for more understanding  it s a great write up  e g   Slow way   CREATE PROCEDURE GetOrderForCustomers  CustID varchar 20   AS BEGIN     SELECT        FROM orders     WHERE customerid    CustID END   Fast way   CREATE PROCEDURE GetOrderForCustomersWithoutPS  CustID varchar 20   AS BEGIN     DECLARE  LocCustID varchar 20      SET  LocCustID    CustID      SELECT        FROM orders     WHERE customerid    LocCustID END   Hope this helps somebody else  doing this reduced my execution time from 5  minutes to about 6-7 seconds

User · Answer

Though I m usually against it  though in this case it seems that you have a genuine reason   have you tried providing any query hints on the SP version of the query  If SQL Server is preparing a different execution plan in those two instances  can you use a hint to tell it what index to use  so that the plan matches the first one   For some examples  you can go here   EDIT  If you can post your query plan here  perhaps we can identify some difference between the plans that s telling   SECOND  Updated the link to be SQL-2000 specific  You ll have to scroll down a ways  but there s a second titled  Table Hints  that s what you re looking for   THIRD  The  Bad  query seems to be ignoring the  IX Openers SessionGUID  on the  Openers  table - any chance adding an INDEX hint to force it to use that index will change things

User · Answer

I found the problem  here s the script of the slow and fast versions of the stored procedure   dbo ViewOpener  RenamedForCruachan  Slow PRC  SET QUOTED IDENTIFIER OFF  GO SET ANSI NULLS OFF  GO  CREATE PROCEDURE dbo ViewOpener RenamedForCruachan Slow      SessionGUID uniqueidentifier AS  SELECT   FROM Report Opener RenamedForCruachan WHERE SessionGUID    SessionGUID ORDER BY CurrencyTypeOrder  Rank GO  SET QUOTED IDENTIFIER OFF  GO SET ANSI NULLS ON  GO   dbo ViewOpener  RenamedForCruachan  Fast PRC  SET QUOTED IDENTIFIER OFF  GO SET ANSI NULLS ON  GO  CREATE PROCEDURE dbo ViewOpener RenamedForCruachan Fast      SessionGUID uniqueidentifier  AS  SELECT   FROM Report Opener RenamedForCruachan WHERE SessionGUID    SessionGUID ORDER BY CurrencyTypeOrder  Rank GO  SET QUOTED IDENTIFIER OFF  GO SET ANSI NULLS ON  GO   If you didn t spot the difference  I don t blame you  The difference is not in the stored procedure at all  The difference that turns a fast 0 5 cost query into one that does an eager spool of 6 million rows   Slow  SET ANSI NULLS OFF  Fast  SET ANSI NULLS ON    This answer also could be made to make sense  since the view does have a join clause that says    table column IS NOT NULL    So there is some NULLs involved     The explanation is further proved by returning to Query Analizer  and running  SET ANSI NULLS OFF      DECLARE  SessionGUID uniqueidentifier SET  SessionGUID    BCBA333C-B6A1-4155-9833-C495F22EA908       SELECT   FROM Report Opener RenamedForCruachan WHERE SessionGUID    SessionGUID ORDER BY CurrencyTypeOrder  Rank   And the query is slow     So the problem isn t because the query is being run from a stored procedure  The problem is that Enterprise Manager s connection default option is ANSI NULLS off  rather than ANSI NULLS on  which is QA s default   Microsoft acknowledges this fact in KB296769  BUG  Cannot use SQL Enterprise Manager to create stored procedures containing linked server objects   The workaround is include the ANSI NULLS option in the stored procedure dialog   Set ANSI NULLS ON Go Create Proc spXXXX as

User · Answer

Have you tried rebuilding the statistics and/or the indexes on the Report_Opener table. All the recomplies of the SP won't be worth anything if the stats still show data from when the database was first inauguarated.

The initial query itself works quickly because the optimiser can see that the parameter will never be null. In the case of the SP the optimiser cannot be sure that the parameter will never be null.

User · Answer

I found the problem  here s the script of the slow and fast versions of the stored procedure   dbo ViewOpener  RenamedForCruachan  Slow PRC  SET QUOTED IDENTIFIER OFF  GO SET ANSI NULLS OFF  GO  CREATE PROCEDURE dbo ViewOpener RenamedForCruachan Slow      SessionGUID uniqueidentifier AS  SELECT   FROM Report Opener RenamedForCruachan WHERE SessionGUID    SessionGUID ORDER BY CurrencyTypeOrder  Rank GO  SET QUOTED IDENTIFIER OFF  GO SET ANSI NULLS ON  GO   dbo ViewOpener  RenamedForCruachan  Fast PRC  SET QUOTED IDENTIFIER OFF  GO SET ANSI NULLS ON  GO  CREATE PROCEDURE dbo ViewOpener RenamedForCruachan Fast      SessionGUID uniqueidentifier  AS  SELECT   FROM Report Opener RenamedForCruachan WHERE SessionGUID    SessionGUID ORDER BY CurrencyTypeOrder  Rank GO  SET QUOTED IDENTIFIER OFF  GO SET ANSI NULLS ON  GO   If you didn t spot the difference  I don t blame you  The difference is not in the stored procedure at all  The difference that turns a fast 0 5 cost query into one that does an eager spool of 6 million rows   Slow  SET ANSI NULLS OFF  Fast  SET ANSI NULLS ON    This answer also could be made to make sense  since the view does have a join clause that says    table column IS NOT NULL    So there is some NULLs involved     The explanation is further proved by returning to Query Analizer  and running  SET ANSI NULLS OFF      DECLARE  SessionGUID uniqueidentifier SET  SessionGUID    BCBA333C-B6A1-4155-9833-C495F22EA908       SELECT   FROM Report Opener RenamedForCruachan WHERE SessionGUID    SessionGUID ORDER BY CurrencyTypeOrder  Rank   And the query is slow     So the problem isn t because the query is being run from a stored procedure  The problem is that Enterprise Manager s connection default option is ANSI NULLS off  rather than ANSI NULLS on  which is QA s default   Microsoft acknowledges this fact in KB296769  BUG  Cannot use SQL Enterprise Manager to create stored procedures containing linked server objects   The workaround is include the ANSI NULLS option in the stored procedure dialog   Set ANSI NULLS ON Go Create Proc spXXXX as

User · Answer

I found the problem  here s the script of the slow and fast versions of the stored procedure   dbo ViewOpener  RenamedForCruachan  Slow PRC  SET QUOTED IDENTIFIER OFF  GO SET ANSI NULLS OFF  GO  CREATE PROCEDURE dbo ViewOpener RenamedForCruachan Slow      SessionGUID uniqueidentifier AS  SELECT   FROM Report Opener RenamedForCruachan WHERE SessionGUID    SessionGUID ORDER BY CurrencyTypeOrder  Rank GO  SET QUOTED IDENTIFIER OFF  GO SET ANSI NULLS ON  GO   dbo ViewOpener  RenamedForCruachan  Fast PRC  SET QUOTED IDENTIFIER OFF  GO SET ANSI NULLS ON  GO  CREATE PROCEDURE dbo ViewOpener RenamedForCruachan Fast      SessionGUID uniqueidentifier  AS  SELECT   FROM Report Opener RenamedForCruachan WHERE SessionGUID    SessionGUID ORDER BY CurrencyTypeOrder  Rank GO  SET QUOTED IDENTIFIER OFF  GO SET ANSI NULLS ON  GO   If you didn t spot the difference  I don t blame you  The difference is not in the stored procedure at all  The difference that turns a fast 0 5 cost query into one that does an eager spool of 6 million rows   Slow  SET ANSI NULLS OFF  Fast  SET ANSI NULLS ON    This answer also could be made to make sense  since the view does have a join clause that says    table column IS NOT NULL    So there is some NULLs involved     The explanation is further proved by returning to Query Analizer  and running  SET ANSI NULLS OFF      DECLARE  SessionGUID uniqueidentifier SET  SessionGUID    BCBA333C-B6A1-4155-9833-C495F22EA908       SELECT   FROM Report Opener RenamedForCruachan WHERE SessionGUID    SessionGUID ORDER BY CurrencyTypeOrder  Rank   And the query is slow     So the problem isn t because the query is being run from a stored procedure  The problem is that Enterprise Manager s connection default option is ANSI NULLS off  rather than ANSI NULLS on  which is QA s default   Microsoft acknowledges this fact in KB296769  BUG  Cannot use SQL Enterprise Manager to create stored procedures containing linked server objects   The workaround is include the ANSI NULLS option in the stored procedure dialog   Set ANSI NULLS ON Go Create Proc spXXXX as

User · Answer

-- Here is the solution   create procedure GetOrderForCustomers  CustID varchar 20    as  begin  select   from orders  where customerid   ISNULL  CustID       end   -- That s it

User · Answer

This time you found your problem  If next time you are less lucky and cannot figure it out  you can use plan freezing and stop worrying about wrong execution plan

User · Answer

This time you found your problem  If next time you are less lucky and cannot figure it out  you can use plan freezing and stop worrying about wrong execution plan

User · Answer

I ve got another idea  What if you create this table-based function   CREATE FUNCTION tbfSelectFromView          -- Add the parameters for the function here      SessionGUID UNIQUEIDENTIFIER   RETURNS TABLE  AS RETURN        SELECT       FROM Report Opener     WHERE SessionGUID    SessionGUID     ORDER BY CurrencyTypeOrder  Rank   GO   And then selected from it using the following statement  even putting this in your SP    SELECT   FROM tbfSelectFromView  SessionGUID    It looks like what s happening  which everybody has already commented on  is that SQL Server just makes an assumption somewhere that s wrong  and maybe this will force it to correct the assumption  I hate to add the extra step  but I m not sure what else might be causing it

User · Answer

I ve got another idea  What if you create this table-based function   CREATE FUNCTION tbfSelectFromView          -- Add the parameters for the function here      SessionGUID UNIQUEIDENTIFIER   RETURNS TABLE  AS RETURN        SELECT       FROM Report Opener     WHERE SessionGUID    SessionGUID     ORDER BY CurrencyTypeOrder  Rank   GO   And then selected from it using the following statement  even putting this in your SP    SELECT   FROM tbfSelectFromView  SessionGUID    It looks like what s happening  which everybody has already commented on  is that SQL Server just makes an assumption somewhere that s wrong  and maybe this will force it to correct the assumption  I hate to add the extra step  but I m not sure what else might be causing it

User · Answer

Do this for your database  I have the same issue - it works fine in one database but when I copy this database to another using SSIS Import  not the usual restore   this issue happens to most of my stored procedures  So after googling some more  I found the blog of Pinal Dave  which btw  I encountered most of his post and did help me a lot so thanks Pinal Dave    I execute the below query on my database and it corrected my issue   EXEC sp MSforeachtable  command1  print     DBCC DBREINDEX            80   GO EXEC sp updatestats GO    Hope this helps  Just passing the help from others that helped me

User · Answer

I was facing the same issue  amp  this post was very helpful to me but none of the posted answers solved my specific issue  I wanted to post the solution that worked for me in hopes that it can help someone else   https   stackoverflow com a 24016676 814299     At the end of your query  add OPTION  OPTIMIZE FOR   now UNKNOWN

User · Answer

I was experiencing this problem  My query looked something like   select a  b  c from sometable where date  gt   20140101    My stored procedure was defined like   create procedure my procedure   dtFrom date  as select a  b  c from sometable where date  gt   dtFrom   I changed the datatype to datetime and voila  Went from 30 minutes to 1 minute   create procedure my procedure   dtFrom datetime  as select a  b  c from sometable where date  gt   dtFrom

User · Answer

I was experiencing this problem  My query looked something like   select a  b  c from sometable where date  gt   20140101    My stored procedure was defined like   create procedure my procedure   dtFrom date  as select a  b  c from sometable where date  gt   dtFrom   I changed the datatype to datetime and voila  Went from 30 minutes to 1 minute   create procedure my procedure   dtFrom datetime  as select a  b  c from sometable where date  gt   dtFrom

[sql-server] SQL Server: Query fast, but slow from procedure

Examples related to sql-server

Examples related to performance

Examples related to stored-procedures