SQL Server: Query fast, but slow from procedure

This may sound silly and seems obvious from the name SessionGUID, but is the column a uniqueidentifier on Report_Opener? If not, you may want to try casting it to the correct type and give it a shot or declare your variable to the correct type.

The plan created as part of the sproc may work unintuitively and do an internal cast on a large table.

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.

[To see links please register here]

> At the end of your query, add OPTION (OPTIMIZE FOR (@now UNKNOWN))

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"][1], 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

Doing this reduced my execution time from 5+ minutes to about 6-7 seconds.

[1]:

[To see links please register here]

"Parameter Sniffing"

The solution i found was that the parameter was not declared with the same type as the actual column definition. Causing SQL Server to try to find the fastest way = Nested Loops.

ex. DECLARE MyParameter nvarchar(50)
Actual column: varchar(8)