query performance

In article <3550B465.3737D3AD_at_ix.netcom.com>#1/1,   ranen_at_ix.netcom.com wrote:
>
> Hi --
>
> The following SQL query is the basis for a PL/SQL procedure using
> DBMS_SQL
>
> that needs to be run several
> times during a WWW-based product. The query takes so long that the
> user's
>
> browsers are timing out!
> When I run the following in SQL*Plus it takes 1.2 seconds (returns about
> 40
>
> records)
>
> These are the Table sizes:
> chpa_test_product = 249 records
> chpa_insurer = 38 records
> chpa_county = 66 records
>
> Does this seem reasonable to anyone? How can Oracle be the number 2
> selling
> software in the world with performance like this? I changed the
> ordering of
> the WHERE CLAUSE to every possible combination and still only managed to
>
> shave off about 2 seconds. The DB is Oracle Version 7, running on Sun
>
> Solaris. I'm thinking it has to be a hardware issue, or that the DB is
> really messed up. Unfortunately, the DBA and UNIX Admin won't even look
> at
>
> the situation. Is this query REALLY that bad??
> Any thoughts appreciated.
>
> Thanks -- Lynn
>
> SELECT
> A.full_product_number, A.ffn , A.plan_type, A.deductible,
>
> A.benefit_level,A.tobacco, A.effdate, A.county_code, A.contribid,
> A.trendid,
>
> A.copay, A.pcp, A.pos, B.insname
> FROM chpa_test_product A,
> chpa_insurer B
> WHERE B.ffn = A.ffn
> AND A.county_id IN (select distinct F.id
> from chpa_county F
> where lower(F.county) IN ('broward', 'dade'))
> AND A.plan_type IN ('HMO','PPO')
> AND A.deductible IN (0,100,250,500,750,2000)
> AND LOWER(A.benefit_level) IN
> ('basic','standard','street','plusplan')
> 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));
>
>

Try reversing the tables in the 'Where' clause for one.

Next, I presume all your tables are indexed for the qualifying fields.

Third, you have several select distincts. Each will require a sort before the main qualifications are checked.

Fourth, even if you are indexed on D.county, the use of the functioin will disable the index. Try upper(ucase?) on the county names. Same for benefit_level, etc. Each disablement becomes a full table scan.

I hope that gives you a start, at least.

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Thu May 07 1998 - 09:51:48 CDT