PL/SQL Performance
Date: 1998/05/16
Message-ID: <355DF4CF.BF7AAE3B_at_ix.netcom.com>#1/1
Hi All --
I’m still having an uphill battle with performance issues. Last week, I posted for help with a “long queryâ€. Since then, I have looked harder at the issue and find that overall performance is horrible and completely unacceptable. Frankly, I can’t believe that the level of performance I am seeing could be considered acceptable by a company that did over $6 billion in sales last year.
Therefore, I must assume that something is terribly wrong with the system.
I developed a benchmarking package to time and record every function call in
the most troublesome procedure. After noticing that most function/procedure
calls (except those accessing DB tables) took about 0.40 seconds, I became
suspicious. I decided to devise a “foolproof†test to see what is REALLY
going on. The variable assignment,
FOO := 1; where FOO is a NUMBER,
also takes between 0.40 and 2.47 seconds. Unbelievable!!! Imagine how this
affects the entire application when it’s stuck inside of a 900 iteration
loop.
Has anyone performed similar benchmark tests? How were you’re times? I am
thinking to upgrade to Oracle 8 and just forget the whole issue. Will this
help me? My company just got a $10 million contract and I am REALLY hesitant
to develop another product on Oracle if this is the performance I can
expect. Any and all feedback appreciated.
Lynn
PS: for those who are interested or assisted me with query advice. Time
ORIGINAL QUERY:
TIME = 1.03 seconds ROWS returned = 134
SELECT
chpa_insurer B
A.full_product_number, A.ffn ... B.insname
FROM chpa_test_product A,
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','HMO','HMO','HMO')
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)) ;
MODIFICATION #1 DROP THE LAST SUB-QUERY (which was not actually needed as
several people pointed out -- thank you !!)
TIME = 0.82 seconds ROWS returned = 134
<snip>
('basic','standard','street','plusplan')
from chpa_county D
MODIFCATION # 2 USE EXISTS INSTEAD OF SUB-QUERY (I’m not sure if I used this
feature properly -- but I didn’t get the correct results and it took
forever)
<snip>
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','HMO','HMO','HMO')
AND A.deductible IN (0,100,250,500,750,2000)
AND LOWER(A.benefit_level) IN
AND A.ffn IN (select distinct D.ffn
where lower(D.county) IN ('broward','dade'));
TIME = 11.45 seconds ROWS returned = 222
WHERE B.ffn = A.ffn
('basic','standard','street','plusplan')
AND EXISTS (select count(*)
from chpa_county F
where A.county_id = F.id
and A.ffn = F.ffn
and lower(F.county) IN ('broward', 'dade'));
Received on Sat May 16 1998 - 00:00:00 CEST