Re: Oracle 9: Date Compare Performance
From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Thu, 29 Jan 2009 10:18:32 -0800 (PST)
Message-ID: <affab7c3-6e3a-445d-92c1-8ca622460ed3_at_h5g2000yqh.googlegroups.com>
On Jan 29, 10:35 am, Ed Prochak <edproc..._at_gmail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
Date: Thu, 29 Jan 2009 10:18:32 -0800 (PST)
Message-ID: <affab7c3-6e3a-445d-92c1-8ca622460ed3_at_h5g2000yqh.googlegroups.com>
On Jan 29, 10:35 am, Ed Prochak <edproc..._at_gmail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
As stated look at the explain plan. Also realize that the optimizer pretty much has to assume you will read X percentage of the data for range scans bounded only on one side. Even a minor change to the SQL can be a major change to the optimizer (as stated).
Make sure the optimizer statistics are current for all objects involved in the query.
HTH -- Mark D Powell -- Received on Thu Jan 29 2009 - 12:18:32 CST