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