Help with Query Performance

[dougcoulter]

Hello everyone. I am attempting to run a nested query against an Oracle 9i database and have been experiencing major performance issues. Here it is:

SELECT chars.lot, chars.char, chars.code,
(SELECT MIN(move.mdate) FROM fin.move move WHERE move.lot = chars.lot AND move.mdate > {ts '1991-01-26 00:00:00'}) AS madedate
FROM fin.chars chars
WHERE (chars.code BETWEEN '1215229' AND '1215229')
AND (chars.char BETWEEN 0.0001 AND 110)
ORDER BY madedate

My first issue is that it will not even compile with the ORDER BY clause as is - so I temporarily removed it. After which, it will compile, but it basically goes out to lunch. Based on my best guess, it should return no more than 1000 records.

To simplify it, I reduced the subquery's first WHERE clause to:

move.lot = 'xxxx'

where xxxx is a known lot number. This simplified query works, but it takes a good 30 seconds to execute.

Not having any access to the Oracle database hinders my ability to really troubleshoot this issue, so I was wondering if there is just something obviously inefficient about the query in its original form?

Any help would be greatly appreciated. Thanks.

 

[CRoberts]

Tough problem. You have no access to explain plan, no way to know if any, or what, indexes are being used? Some simple suggestions, may or may not work, depends on the database.

1) Use ORDER BY 4 to sort your results, or select Mandated and order by on it. The ORDER BY can be used by explicitly duplicating a reference column in your SELECTed list, or you can specify the referenced column by position within the SELECTed list.

2) A correlated query can be very bad, or very good. Depends on the circumstances. Above you have a correlated scalar subquery. A coorelated query can work better on large tables where you expect a small result set, or not. A correlated subquery fires once for every row selected on the outer query. Try altering your query by making the correlated subquery into an equi-join query that includes a GROUP BY clause to work with your MIN function.

3) You have chars.code BETWEEN '1215229' AND '1215229', simplify it to chars.code = '1215229'.

4) Is Chars.Char indexed? If this is limiting in any significant you result set, you might put that into its own subquery. Is Chars.Code indexed? What about using both columns in a subquery? Include MDate in the subquery?