Showing posts with label fasteralter. Show all posts
Showing posts with label fasteralter. Show all posts

Monday, March 19, 2012

Make Stored Proc Faster

Anyone got any alternatives to the SQL Statement below that would make it run a bit faster:

ALTER PROCEDURE sproc_ReturnAvailability

@.ExtractDate DateTime,
@.DateFrom DateTime,
@.DateTo DateTime,
@.96hrPlusFlag int,
@.AppointmentsCount int OUTPUT

AS

IF @.96hrPlusFlag = 0

BEGIN
SELECT @.AppointmentsCount = COUNT(tbl_SurgerySlot.SurgerySlotKey)
FROM tbl_SurgerySlot
INNER JOIN tbl_SurgerySlotDescription ON (tbl_SurgerySlot.Label = tbl_SurgerySlotDescription.Label AND tbl_SurgerySlot.PracticeCode = tbl_SurgerySlotDescription.PracticeCode)
AND tbl_SurgerySlot.ExtractDate = @.ExtractDate
AND tbl_SurgerySlot.StartTime BETWEEN @.DateFrom AND @.DateTo
AND tbl_SurgerySlotDescription.NormalBookable = 1
AND tbl_SurgerySlot.SurgerySlotKey NOT IN(
SELECT tbl_Appointment.SurgerySlotKey
FROM tbl_Appointment
WHERE tbl_Appointment.ExtractDate = @.ExtractDate
AND tbl_Appointment.Deleted = 0
AND tbl_Appointment.Cancelled = 0
)
END
ELSE

BEGIN
IF @.96hrPlusFlag = 1

SELECT @.AppointmentsCount = COUNT(tbl_SurgerySlot.SurgerySlotKey)
FROM tbl_SurgerySlot
INNER JOIN tbl_SurgerySlotDescription ON (tbl_SurgerySlot.Label = tbl_SurgerySlotDescription.Label AND tbl_SurgerySlot.PracticeCode = tbl_SurgerySlotDescription.PracticeCode)
AND tbl_SurgerySlot.ExtractDate = @.ExtractDate
AND tbl_SurgerySlot.StartTime >@.DateTo
AND tbl_SurgerySlotDescription.NormalBookable = 1
AND tbl_SurgerySlot.SurgerySlotKey NOT IN(
SELECT tbl_Appointment.SurgerySlotKey
FROM tbl_Appointment
WHERE tbl_Appointment.ExtractDate = @.ExtractDate
AND tbl_Appointment.Deleted = 0
AND tbl_Appointment.Cancelled = 0
)

END

Cheers...You need to LEFT OUTER JOIN tbl_Appointment rather than use NOT IN on it. And in the WHERE clause filter in records where SurgerySlotKey IS NOT NULL.