Stored Procedure Tuning. [message #37621] |
Sun, 17 February 2002 21:13 |
Shankar Balakrishnan
Messages: 2 Registered: February 2002
|
Junior Member |
|
|
Of the 8 procedures, 1 was instantly improved after indexing the transaction_id column of the table. The second procedure has a NOT IN (select * ...) type of query which is very slow. Indexing also doesn't seem to help.
The remaining 6 procedures are 2 identical sets of 3 procedures. These 3 procedures do the following -
1 & 3 are identical - here's the pseudocode
Open a cursor of t_id, account_no
Open another cursor with rows of type 'ADD' and
t_id, account_no
Compute price of 'ADD' for that t_id, account_no
Open another cursor with rows of type 'REMOVE' and
t_id, account_no
Compute price of 'REMOVE' for that t_id, account_no
DBMS_OUTPUT if REMOVE price > ADD price indicating
a downgrade order!
Close the main cursor.
The 2nd one is - Counts distinct account_nos from the table - uses Index-By Table.
Right now, I don't have the exact code but will put it on display ASAP ...
Please let me know if anyone has any inputs looking at the algorithm above.
Thanks!
Regards,
Shankar Balakrishnan.
|
|
|
Re: Stored Procedure Tuning. [message #37638 is a reply to message #37621] |
Mon, 18 February 2002 08:32 |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
NOT IN doesn't use index - each candidate entry in the outer look needs to be compared to the NOT in subquery. The bigger the NOT IN list gets, the slower. Re-write using NOT EXISTS or something like this - it all depends on the quantities od data involved and the % to exclude.
Try replacing this:
select * from emp where emp_no not in (select emp_no from... where...);
with something like this:
select * from emp where emp_no
minus
select * from emp where emp_no in (select emp_no from... where...);
|
|
|