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
