Stored procedure is slower than a query
I have converted my query into the stored procedure. The stored procedure executes a lot slower than the query itself. I am using sql server 2008 R2. In sql management studio it takes about 4 sec to return 4 records in the code it takes 15 seconds
GO
/****** Object: StoredProcedure [dbo].[sp_GetCancellations] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_GetCancellations] AS
BEGIN
SELECT S.sessionKey,
SU.sessionStart,
S.SubDesc,
L.Name AS LocationName,
U.UserKey,
U.FirstName,
U.LastName,
U2.FirstName as insFirstName,
U2.LastName as insLastName,
U.Email,
U.guardianEmail,
SMP.sessionMapKey,
(
SELECT COUNT(1)
FROM sessionMap SM
WHERE S.sessionKey = SM.sessionKey
AND SM.userKey != 0
),
L.city,L.state,L.address1,
(CONVERT(int, CONVERT(varchar, SU.sessionStart, 112)) - CONVERT(int, CONVERT(varchar, U.dateOfBirth, 112)))/10000 as searcherAge
,L.zip
FROM session S
INNER JOIN sessionUnit SU ON S.sessionKey = SU.sessionKey
INNER JOIN product P ON P.productKey = S.productKey AND P.productTypeKey IN (2,4)
INNER JOIN lkup_availState LS ON S.officeStateKey = LS.availStateKey
INNER JOIN sessionMap SMP ON S.sessionKey = SMP.sessionKey
INNER JOIN location L ON S.locationKey = L.locationKey
INNER JOIN users U ON SMP.userKey = U.userKey
INNER JOIN users U2 ON SU.instructorKey = U2.userKey
WHERE LS.availStateKey = 2
AND S.status = 'enabled'
AND DateDiff(day, GetDate(), SU.sessionStart) < 6
AND (
SELECT COUNT(1)
FROM sessionMap SM
WHERE S.sessionKey = SM.sessionKey
AND SM.userKey != 0
) > 0
AND (
SELECT COUNT(1)
FROM sessionMap SM
WHERE S.sessionKey = SM.sessionKey
AND SM.userKey != 0
) < IsNull(SU.btwSeatsOverride, S.Seats)
AND SMP.TYPE<> 'SBW'
ORDER BY SU.sessionStart
ENDASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
the queries are identical
The SETtings are probably not, since SSMS and SQL typically have different default SETtings.
That could, in theory, produce different query plans. Are the query plans identical? It's quite possible they are. The delay might be due to blocking or some other specific situation.
That could, in theory, produce different query plans. Are the query plans identical? It's quite possible they are. The delay might be due to blocking or some other specific situation.
ASKER
AND SU.sessionStart >= DATEADD(DAY, -5, CAST(GetDate() AS date)) /*(2); verify this date logic*/
this logic will not work , because it will give all records I need records within next 5 days
this logic will not work , because it will give all records I need records within next 5 days
Hmm, I'm assuming sessionStart is a datetime sometime in the past, i.e., before GETDATE().
If that's true, I believe the logic above gets anything within the last 5 days, which I surmised was the intent of the original code.
But the actual original code:
If that's true, I believe the logic above gets anything within the last 5 days, which I surmised was the intent of the original code.
But the actual original code:
AND DateDiff(day, GetDate(), SU.sessionStart) < 6will be true for all/every earlier date, since the result will be negative, and thus always less than 6.
ASKER
sessionStart is both is in the past and the future I only need 5 days in the future
Ok, didn't expect that. Something more like this then:
AND SU.sessionStart >= GETDATE() AND SU.sessionStart < DATEADD(DAY, 6, CAST(GetDate() AS date))
AND SU.sessionStart >= GETDATE() AND SU.sessionStart < DATEADD(DAY, 6, CAST(GetDate() AS date))
1) SET STATISTICS IO ON before running your query and your procedure. Are the equal?
2) SET STATISTICS TIME ON before running your query and your procedure. Are the equal?
3) Compare (post) the actual execution plan of the query and the procedure.