SQL Server: Query fast, but slow from procedure

sql-server performance stored-procedures
Ian Boyd picture Ian Boyd · Jan 13, 2009 · Viewed 202.8k times · Source

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]<>0 AND [Currencies].[OnOpener]<>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]<>0 AND [Currencies].[OnOpener]<>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.

Answer

Adam Marshall picture Adam Marshall · Jul 8, 2009

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.

Related questions

SQL Performance Slow (Improve Insert Into Temp Table)

I have been working on an old auditing stored procedure that is running slowly, I have had some success from applying indexing and making the query more sargable. However the stored procedure is still taking over one minute to complete. …

Read More
Which is faster in SQL, While loop, Recursive Stored proc, or Cursor?

Which is faster in SQL, While loop, Recursive Stored proc, or Cursor? I want to optimize the performance in a couple of spots in a stored procedure. The code I'm optimizing formats some strings for output to a file.

Read More
LIKE vs CONTAINS on SQL Server

Which one of the following queries is faster (LIKE vs CONTAINS)? SELECT * FROM table WHERE Column LIKE '%test%'; or SELECT * FROM table WHERE Contains(Column, "test");

Read More