Re: need help with long query

Lynn,

Are you sure that the bottleneck is at the server side?

Still you can try to optimize your SQL. The general idea is to avoud sub-queries where possible, or at least IN (select...) conditions.

Try to use EXISTS statement instead of IN, as follows:

> AND A.county_id IN (select distinct F.id
> from chpa_county F
> where lower(F.county) IN ('broward', 'dade'))
AND EXISTS ( SELECT f.id FROM chpa_county f WHERE f.id = a.county_id AND LOWER(f.county) IN ('broward', 'dade'))

>AND A.ffn IN (select distinct D.ffn
> from chpa_county D
> where lower(D.county) IN ('broward','dade')
AND EXISTS (SELECT d.ffn FROM chpa_county d WHERE a.ffn=d.ffn AND lower(D.county) IN ('broward','dade'))

> AND D.ffn IN (select distinct C.ffn
> from chpa_insurer C));

AND EXISTS SELECT (c.ffn FROM chpa_insurer C WHERE d.ffn=c.ffn)

The further step could be to avoid 2nd level sub-query:

>AND A.ffn IN (select distinct D.ffn
> from chpa_county D
> where lower(D.county) IN ('broward','dade')
> AND D.ffn IN (select distinct C.ffn
> from chpa_insurer C));

AND EXISTS (SELECT d.ffn FROM chpa_county D, chpa_insurer C WHERE a.ffn=d.ffn AND c.ffn = d.ffn AND LOWER(d.county) IN ('broward', 'dade'))

BTW, can you please explain what is the reason of introducing this condition (I mean the additional link to chpa_county from chpa_test_product via ffn)?

HTH
Anton Received on Thu May 07 1998 - 03:48:25 CDT