Query Optimization

From: <wbjones_at_msn.com>
Date: Wed, 09 Jun 1999 14:40:55 GMT
Message-ID: <7jludl$l79$1@nnrp1.deja.com>

Since I'm unsure as to the location of the slowdown (tried using the PLAN_TABLE) and accurately representing it, I am pasting the entire query below. Thank you for any help (suggestions regarding ways to debug queries like this would be very useful).

select count(HTS_SAMPLE.Alternate_ID) as cntComp from HTS_SAMPLE, HTS_PLATE, HTS_ASSAY_RESULT, HTS_COMPOUND_LOT, HTS_SCIENTIST, RS3_STRUCTURE, HTS_ASSAY, HTS_ASSAY_PROTOCOL, HTS_RESULT_TYPE where

HTS_ASSAY.ASSAY_ID = HTS_ASSAY_PROTOCOL.ASSAY_ID and
HTS_ASSAY_PROTOCOL.Alt_Assay_ID = HTS_ASSAY_RESULT.Alt_Assay_ID and
HTS_SAMPLE.Sample_ID = HTS_ASSAY_RESULT.Sample_ID and HTS_PLATE.Plate_ID

(+) = HTS_ASSAY_RESULT.Sample_Plate and HTS_COMPOUND_LOT.Sample_ID =

HTS_SAMPLE.Sample_ID and HTS_SCIENTIST.Scientist_ID =
HTS_COMPOUND_LOT.Scientist_ID and RS3_STRUCTURE.Structure_ID =
HTS_COMPOUND_LOT.Compound_ID and HTS_ASSAY_RESULT.Result_Type =
HTS_RESULT_TYPE.Result_Type

and

HTS_ASSAY_RESULT.Sample_ID in
(select HTS_ASSAY_RESULT.Sample_ID from HTS_ASSAY, HTS_ASSAY_PROTOCOL,

HTS_ASSAY_RESULT, HTS_RESULT_TYPE where  HTS_ASSAY.Assay_ID =
HTS_ASSAY_PROTOCOL.Assay_ID and  HTS_ASSAY_PROTOCOL.Alt_Assay_ID =
HTS_ASSAY_RESULT.Alt_Assay_ID and  HTS_ASSAY_RESULT.Result_Type =
HTS_RESULT_TYPE.Result_Type and  HTS_ASSAY.Assay_Name = 'A2A - YEAST'

and HTS_ASSAY_RESULT.Result_Value > 50 and HTS_RESULT_TYPE.Type_Desc = '%INH') and

HTS_ASSAY_RESULT.Sample_ID in
(select HTS_ASSAY_RESULT.Sample_ID from HTS_ASSAY, HTS_ASSAY_PROTOCOL,

HTS_ASSAY_RESULT, HTS_RESULT_TYPE where  HTS_ASSAY.Assay_ID =
HTS_ASSAY_PROTOCOL.Assay_ID and  HTS_ASSAY_PROTOCOL.Alt_Assay_ID =
HTS_ASSAY_RESULT.Alt_Assay_ID and  HTS_ASSAY_RESULT.Result_Type =
HTS_RESULT_TYPE.Result_Type and  HTS_ASSAY.Assay_Name = 'A2B - YEAST'

and HTS_ASSAY_RESULT.Result_Value < 50 and HTS_RESULT_TYPE.Type_Desc = '%INH') and

HTS_ASSAY_RESULT.Sample_ID in

(select HTS_ASSAY_RESULT.Sample_ID from HTS_ASSAY, HTS_ASSAY_PROTOCOL,

HTS_ASSAY_RESULT, HTS_RESULT_TYPE where  HTS_ASSAY.Assay_ID =
HTS_ASSAY_PROTOCOL.Assay_ID and  HTS_ASSAY_PROTOCOL.Alt_Assay_ID =
HTS_ASSAY_RESULT.Alt_Assay_ID and  HTS_ASSAY_RESULT.Result_Type =
HTS_RESULT_TYPE.Result_Type and  HTS_ASSAY.Assay_Name = 'BK2 - YEAST'

and HTS_ASSAY_RESULT.Result_Value < 50 and HTS_RESULT_TYPE.Type_Desc = '%INH') and HTS_RESULT_TYPE.Type_Desc = '%INH' group by HTS_ASSAY.Assay_Name, HTS_RESULT_TYPE.Type_Desc order by cntComp desc

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Wed Jun 09 1999 - 09:40:55 CDT