Re: Oracle 9: Date Compare Performance
From: Ed Prochak <edprochak_at_gmail.com>
Date: Thu, 29 Jan 2009 07:35:19 -0800 (PST)
Message-ID: <e8c7d105-f739-40ef-af53-95aa08f2409b_at_b38g2000prf.googlegroups.com>
On Jan 29, 9:36 am, "MrBana..._at_googlemail.com" <MrBana..._at_googlemail.com> wrote:
> Hi,
>
> I ve got a table named schedule which includes epg information round
> about 60.000 records. I would like to check if there are logical
> conflicts.
> One kind of conflict is that a program start stop time frame includes
> other programs...
>
> Therefore I ve got this select statement:
> SELECT Distinct schd.chan_recid, schd.SCHD_START_DATE,
> schd.SCHD_END_DATE, schd.schd_import_refnum,
> include_schd.schd_import_refnum as
> overlapping_schd_import_refnum,
> include_schd.schd_start_date as overlapping_start,
> include_schd.schd_end_date as overlapping_end
> FROM SCHEDULE schd
> LEFT JOIN ova_schedule include_schd
> on (schd.CHAN_RECID = include_schd.CHAN_RECID and
> include_schd.schd_recid != schd.schd_recid and
> include_schd.schd_start_date >= schd.schd_start_date and
> include_schd.schd_end_date <= schd.schd_end_date)
> WHERE (include_schd.schd_import_refnum is not null)
>
> To my suprise it takes over a minute to complete!!!
>
> If I change the date compares from >= and <= to just = :
> include_schd.schd_start_date = schd.schd_start_date and
> include_schd.schd_end_date = schd.schd_end_date
>
> It just takes round about 10 seconds.
>
> Does anybody know why the date comparison operators > and < are so
> slow?
Date: Thu, 29 Jan 2009 07:35:19 -0800 (PST)
Message-ID: <e8c7d105-f739-40ef-af53-95aa08f2409b_at_b38g2000prf.googlegroups.com>
On Jan 29, 9:36 am, "MrBana..._at_googlemail.com" <MrBana..._at_googlemail.com> wrote:
> Hi,
>
> I ve got a table named schedule which includes epg information round
> about 60.000 records. I would like to check if there are logical
> conflicts.
> One kind of conflict is that a program start stop time frame includes
> other programs...
>
> Therefore I ve got this select statement:
> SELECT Distinct schd.chan_recid, schd.SCHD_START_DATE,
> schd.SCHD_END_DATE, schd.schd_import_refnum,
> include_schd.schd_import_refnum as
> overlapping_schd_import_refnum,
> include_schd.schd_start_date as overlapping_start,
> include_schd.schd_end_date as overlapping_end
> FROM SCHEDULE schd
> LEFT JOIN ova_schedule include_schd
> on (schd.CHAN_RECID = include_schd.CHAN_RECID and
> include_schd.schd_recid != schd.schd_recid and
> include_schd.schd_start_date >= schd.schd_start_date and
> include_schd.schd_end_date <= schd.schd_end_date)
> WHERE (include_schd.schd_import_refnum is not null)
>
> To my suprise it takes over a minute to complete!!!
>
> If I change the date compares from >= and <= to just = :
> include_schd.schd_start_date = schd.schd_start_date and
> include_schd.schd_end_date = schd.schd_end_date
>
> It just takes round about 10 seconds.
>
> Does anybody know why the date comparison operators > and < are so
> slow?
Have you looked at the EXPLAIN PLAN for each. They are very different queries. One thing I would take a WAG at is the DISTINCT. IOW, with the >= version, a large number of rows are returned which are then sorted to perform the DISTINCT. While the = version has a much smaller set to sort through.
>
> Does anybody have a workaround?
Add the conditions to avoid the DISTINCT. (again a WAG)
>
> Thanks a lot in advance...
> Volker
Welcome. Let us know what you find out.
Ed Received on Thu Jan 29 2009 - 09:35:19 CST