Results 1 to 3 of 3

Thread: Slow Date Join

  1. #1
    Join Date
    Apr 2006

    Question Unanswered: Slow Date Join


    I have a problem with several queries joining to a certain table being very slow.

    The table that causes the slow down contains shift data for workers. Certain events happen during shifts so when retrieving the events I need to know whih shifts are involved.

    The tables are joined as follows

    The related part of the where clause of the query is as follows

    AND shift_table.shift_type = event_table.shift_type
    AND shift_table.shift_code = event_table.shift_code
    AND ((shift_table.start_time >= event_table.start_time
                 AND shift_table.start_time <= event_table.end_time)
    	OR (shift_table.start_time <= event_table.start_time
    	     AND shift_table.end_time >= event_table.start_time))
    In addition the user specifies a date range so the following clauses will be added:

    AND (
            --Events started in middle of date range
            (event_table.start_time >= user_from_date 
    	AND event_table.start_time <= user_to_date)
            --Events started before date range
            OR (event_table.start_time <= user_from_date
    	AND event_table.end_time >= user_from_date)
    The shift_table will be holding several rows for each day of the year. The event table will grow and hold thousands of events.

    Is there an appropriate index or query re-write to improve performance? I have tried adding indexes on the shift ables shift type and shift code columns. I think the problem is the comparison of data ranges and the volume of data involved in the join.

    EDIT: I tried an index for each table on its start_time and end_time on it's own i.e. two indexes. This degraded performance considerably.

    Any suggestions would be most welcome.

    Thanks in advance.

    Last edited by aMacdonald; 04-03-06 at 08:36.

  2. #2
    Join Date
    Sep 2002
    Provided Answers: 1
    Try using this overlap syntax that doesn't involve ORs:

    AND shift.start_time <= event.end_time
    AND shift.end_time >= event.start_time
    AND event.start_time <= user_to_date
    AND event.end_time >= user_from_date

    You could try indexing (shift_type, shift_code, start_time, end_time) on shift and (start_time, end_time) on event.

    Be sure to gather statistics on the tables!

  3. #3
    Join Date
    Apr 2006
    Thanks for the reponse.

    I should have stated that my pk for the shift table was already what you suggested.

    The index suggested for the event table did help , big thanks. I had previously tried two seperate indexes for each date column, but your way seemed to take a bit of time off. I'm not sure if it's enough, the real problem is on our client site, where they are getting crashes from this.

    I had a quick look into gathering statistics and that might well be a big help. I might try running this over night and see how big the improvements are.

    Thanks for your help, it's been most useful.

Posting Permissions

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