Help w/ long query

Help w/ long query

Post by John Wollne » Sun, 31 Dec 1899 09:00:00



Does anyone know how to avoid locking up a machine w/ a long query under
D1?

I need a way to display a progress gauge, or at the very least, allow
the user to kill the query.

Any help would be appreciated.
--
I like my music like I like my life:
        Everything louder than everything else!

 
 
 

1. need help with long query

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

2. OH-ORACLE FINANCIAL APPLICATIONS ANALYST

3. need performance help: avoid starvation of short queries by long-running queries

4. Wrong (ADO) Recorset return from Stored Procedure .....

5. Help Help My DBCC CheckDB run a long long time

6. pgsql/ oc/FAQ_SCO oc/Makefile oc/src/Makefile ...

7. how to query a long long select statements?

8. VA-NoVa-243273--Integration-UNIX-C++-Java-HTML-ORACLE-Ingres-Debugging-Sr. Integration Specialist

9. Query taking too long - please help

10. Help! Extremely long running Query(s)

11. Long queries and progress or SOMETHING...Geezzzzz...Can SOMEONE help me out here

12. Query taking too long - Please Help

13. Help with suddenly long running query, 7.3.4.3/AIX