Re: slow SELECT

penzina_at_iinet.net.au (Paul Fell) wrote in message news:<bea9531b.0306121429.20025e90_at_posting.google.com>...
> Thanks. I prefer to use ansi 92 joins.
>
> The answer was........ generate statistics for tables.
>
> Paul
>
>
> "Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.com> wrote in message news:<ChyFa.1199965$S_4.1219030_at_rwcrnsc53>...
> > also use bind variables.
> > Jim
> > "Paul Fell" <penzina_at_iinet.net.au> wrote in message
> > news:bea9531b.0306101933.c79efc3_at_posting.google.com...
> > > I'm running the SQL below on Oracle 9i. Table info :
> > >
> > > tblDHSampleDuplicates : PK DataSet (varchar2(60),
> > > SampleID(varchar2(60))
> > > non-unique Indexe on SampleID
> > >
> > > tblAssay : PK
> > > dataset NVARCHAR2(60)
> > > NOT NULL
> > > , sampleid NVARCHAR2(60)
> > > NOT NULL
> > > , genericmethod NVARCHAR2(20)
> > > NOT NULL
> > > , element NVARCHAR2(40)
> > > NOT NULL
> > > , repeat NVARCHAR2(20)
> > > NOT NULL
> > > , preferred NUMBER (5)
> > > NOT NULL
> > > , origmethod NVARCHAR2(40)
> > > NOT NULL
> > > , labcode NVARCHAR2(40)
> > > NOT NULL
> > > , batch_no NVARCHAR2(50)
> > > NOT NULL
> > >
> > > non-unique indexes on SampleID, element
> > >
> > > SELECT tblDHSampleDuplicates.DataSet,
> > > tblDHSampleDuplicates.BatchNo,tblDHSampleDuplicates.SampleID,
> > > tblDHSampleDuplicates.DuplicateCategory,
> > > tblDHSampleDuplicates.AssociatedSampleID,
> > > RepeatTable.sysResult RepeatResult,OrigTable.sysResult OriginalResult,
> > > OrigTable.GenericMethod,
> > > OrigTable.Element,
 OrigTable.UnitCode,OrigTable.LimitLower,OrigTable.LabCode,
> > > RepeatTable.Repeat RepeatRepeatType,OrigTable.Repeat OrigRepeatType
> > > FROM tblDHSampleDuplicates INNER JOIN (tblAssay OrigTable INNER JOIN
> > > tblAssay RepeatTable
> > > ON OrigTable.Element = RepeatTable.Element) ON
> > > (tblDHSampleDuplicates.DataSet = OrigTable.DataSet)
> > > AND (tblDHSampleDuplicates.AssociatedSampleID = OrigTable.SampleID)
> > > AND (tblDHSampleDuplicates.DataSet = RepeatTable.DataSet)
> > > AND (tblDHSampleDuplicates.SampleID = RepeatTable.SampleID)
> > > WHERE (RepeatTable.Element='Ni')
> > >
> > > This query, when the WHERE clause is omitted, returns resultset in
> > > less than 4 seconds. When the WHERE is included it takes over an hour.
> > > The same query, with the WHERE clause included, runs fine on SQL
> > > Server 2000 and returns resultsets in less than 3 seconds. Table
> > > structures/PK's/Indexes are exactly the same on both platforms. Any
> > > ideas how I can tune SQL and/or Tables?
> > >
> > > Paul

Paul,

I think it is due to block "clean out" problem. What you can do is to run ANALYZE to update statistics and clean out all of the unused blocks as well. Try next time.

By the way, I like the way Jim uses alias to make statement neat.

Regards,

Gary Received on Fri Jun 13 2003 - 08:54:21 CEST