2017年4月16日 星期日

[SQL Server] Query fast, but slow from stored procedure

1. ANSI_NULLS Issue
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
....

2. procedure cache issue
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.

REF: 
http://chhtai.blogspot.tw/2016/01/normal-0-0-2-false-false-false-en-us-zh.html
http://stackoverflow.com/questions/440944/sql-server-query-fast-but-slow-from-procedure

沒有留言:

張貼留言