Lynn Ranen 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)) ;
Just guessing that that the SQL is not being executed on the
database server,
then sending the result set to the web server?
Received on Wed May 06 1998 - 22:06:30 CDT
