SQL Server: Query fast, but slow from procedure

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))ASBEGIN    SELECT *     FROM orders    WHERE customerid = @CustIDEND

Fast way:

CREATE PROCEDURE GetOrderForCustomersWithoutPS(@CustID varchar(20))ASBEGIN    DECLARE @LocCustID varchar(20)    SET @LocCustID = @CustID    SELECT *     FROM orders    WHERE customerid = @LocCustIDEND

Hope this helps somebody else, doing this reduced my execution time from 5+ minutes to about 6-7 seconds.

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 GOSET ANSI_NULLS OFF GOCREATE PROCEDURE dbo.ViewOpener_RenamedForCruachan_Slow    @SessionGUID uniqueidentifierASSELECT *FROM Report_Opener_RenamedForCruachanWHERE SessionGUID = @SessionGUIDORDER BY CurrencyTypeOrder, RankGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO

dbo.ViewOpener__RenamedForCruachan__Fast.PRC

SET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOCREATE PROCEDURE dbo.ViewOpener_RenamedForCruachan_Fast    @SessionGUID uniqueidentifier ASSELECT *FROM Report_Opener_RenamedForCruachanWHERE SessionGUID = @SessionGUIDORDER BY CurrencyTypeOrder, RankGOSET QUOTED_IDENTIFIER OFF GOSET 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 uniqueidentifierSET @SessionGUID = 'BCBA333C-B6A1-4155-9833-C495F22EA908'

.

SELECT *FROM Report_Opener_RenamedForCruachanWHERE SessionGUID = @SessionGUIDORDER 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 ONGoCreate Proc spXXXX as....

I was facing the same issue & 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))