slow SELECT

From: Paul Fell <penzina_at_iinet.net.au>
Date: 10 Jun 2003 20:33:20 -0700
Message-ID: <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 Wed Jun 11 2003 - 05:33:20 CEST