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.
Monday, March 19, 2012
Make Stored Proc Faster
Labels:
alternatives,
below,
bit,
database,
extractdate,
fasteralter,
microsoft,
mysql,
oracle,
proc,
procedure,
run,
server,
sproc_returnavailability,
sql,
statement,
stored
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment