Re: Query Optimization

From: Steven Franklin <steven.franklin_at_wcom.com>
Date: Thu, 10 Jun 1999 12:36:11 GMT
Message-ID: <375FB16B.6750B01C@wcom.com>

wbjones_at_msn.com wrote:

> 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).

You're using three subqueries in this statement. Subqueries are very slow, converting them to joins is much faster. It looks like all you really need to convert the subqueries to a join in this case is an 'OR' clause for each subquery in your 'WHERE' clause.

Maybe something like this:

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_result_type.type_desc = '%INH') AND


/* with this block here replacing your three subqueries */


      (((hts_assay.assay_name = 'A2A - YEAST') AND


      (hts_assay_result.result_value > 50)) OR


      ((hts_assay.assay_name = 'A2B - YEAST') AND


      (hts_assay_result.result_value < 50)) OR


      ((hts_assay.assay_name = 'BK2 - YEAST') AND


      (hts_assay_result.result_value < 50)))


/*****************************************/
GROUP BY hts_assay.assay_name,
         hts_result_type.type_desc

ORDER BY cntComp DESC Received on Thu Jun 10 1999 - 07:36:11 CDT