Re: Query Optimization

i think I would further move the where clause elements that link on assay_id TO THE END of the list. (assuming they are fairly restrictive parts of the link)
i.e not many records with the same assay_id vs the total number of records...

then I would create an index on each tables assay_id.

you may be amazed (or frightened) by the difference this can make.

good luck.. maybe an explain plan and some table stats like #rows in each, and a degree of uniqueness to those rows is a good place to begin the analysis.

this replacement of the nested stuff with OR logic seems good :) randy

Steven Franklin wrote:

> 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 Fri Jun 11 1999 - 20:37:48 CDT