Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107

    Unanswered: 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...

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •