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.
Showing posts with label fasteralter. Show all posts
Showing posts with label fasteralter. Show all posts
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:
Comments (Atom)