Generally speaking, getting a spike above 60% is nothing to worry about. How
long does the processor stay above 60%? If it is for several seconds, then
you might have something to think about.
First, how is your table indexed? I would hope as a minimum you have an
index on the Fault_No and Response_no columns.
Last, yes, generally there is a more efficient way to get these results than
the correlated sub-query like you are using. This does not always give
faster results - especially when the table is small, but bench-mark it and
see.
Select the results of your sub-query into a temp table, and see if that
works better. I'm not 100% sure how oracle handles temp tables, but here is
how I would normally do it - the concept should be the same:
SELECT fault_no, max(response_no) as response_no
INTO #temp
FROM stacy
group by fault_no
then modify your query like this:
SELECT s.fault_no, s.date_occurred, s.one_line_summary, s.category
FROM stacy s
join #temp t on s.fault_no = t.fault_no
and ((s.response_no = t.response_no) or (s.response_no is null and
t.response_no is null))
When you've got the results in #temp already, that second will limit the
results as you want them, and should be faster than the correlated subquery.
You may need to modify that a bit to be oracle specific, but try it and see
if it is faster.
Steve H.
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Sunday, July 29, 2001 8:03 AM
To: DBI Users
Subject: SQL efficiency
Hi all,
With our report/response database, fault_no's can have one, multiple or
null response_no's. The SQL below returns distinct fault_no's regardless
if it has one, multiple or null response's. The SQL does the job, but
can you figure out it uses up a large amount of CPU (>60% on an Ultra
1)?
I'm only dealing with ~ 1400 rows.
Is there a better method?
SELECT s.fault_no, s.date_occurred, s.one_line_summary, s.category
FROM stacy s
WHERE (s.response_no =
(
SELECT max(response_no)
FROM stacy
WHERE fault_no = s.fault_no
) OR response_no is null
)
BTW: This is operating on an Oracle VIEW. I'm using Oracle 7.3.3 via
perl5.6.0/DBI1.14
Regards,
Stacy.
