Re: slow SELECT

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
Received on Fri Jun 13 2003 - 00:29:45 CEST