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))
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.

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
....

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.