| SQL Tuning...? [message #572582] |
Thu, 13 December 2012 07:26  |
 |
gblackiv
Messages: 15
Registered: August 2012
Location: Springfield VA
|
Junior Member |
|
|
I have a select statement within a procedure that seems, to me, less than optimal.
I have never delved into performance, so I don't know if this is an issue or not... but I am running a procedure that is taking a very long time and am trying to minimize possible performance issues. The procedure below is being called many times within a larger proc and I was wondering if this might be the culprit.
Unfortunately I don't know how (or even if) this code can (or should) be modified to perform faster... I was hoping someone on this site can take a look and give me some pointers... or at least tell me if modifying it would be a waste of time.
Thanks in advance for any help...
Here's the code:
CREATE OR REPLACE PROCEDURE GBLACK.S0730_REF_CURSOR(v_INT IN NUMBER,
v_STOREROOM IN VARCHAR2,
v_NSN_STATUS_TYPE IN VARCHAR2,
v_REFCURSOR OUT SYS_REFCURSOR)
IS
BEGIN
IF v_INT = 4 THEN
OPEN v_REFCURSOR FOR
SELECT S.STRATIFICATION_ID
FROM GBLACK.S0550_STRAT_STOREROOM_HOLDER S
JOIN (SELECT DISTINCT NSN_A NSN, STOREROOM
FROM A0100_SUB_LIST
WHERE NSN_B IN (SELECT DISTINCT NSN
FROM GBLACK.P0525_STOREROOM_HOLDER
WHERE NSN_STATUS_TYPE = v_NSN_STATUS_TYPE
AND STOREROOM = v_STOREROOM
AND STRATIFIED_AS_SUBSTITUTE = 'P')
AND STOREROOM = v_STOREROOM) A
ON (S.STOREROOM = A.STOREROOM AND S.NSN = A.NSN)
ORDER BY STRATIFICATION_ID;
ELSE
OPEN v_REFCURSOR FOR
SELECT S.STRATIFICATION_ID
FROM GBLACK.S0550_STRAT_STOREROOM_HOLDER S
JOIN (SELECT DISTINCT NSN, STOREROOM, STRATIFIED_AS_SUBSTITUTE
FROM GBLACK.P0525_STOREROOM_HOLDER
WHERE NSN_STATUS_TYPE = v_NSN_STATUS_TYPE
AND STOREROOM = v_STOREROOM) P
ON (S.STOREROOM = P.STOREROOM AND S.NSN = P.NSN)
WHERE STRATIFIED_AS_SUBSTITUTE = 'P';
END IF;
END S0730_REF_CURSOR;
/ |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: SQL Tuning...? [message #572599 is a reply to message #572589] |
Thu, 13 December 2012 10:41  |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You've got 130K records and 500K records and a process taking 23 hrs?
Ouch.
I don't know what your process is, or what hardware you are on, but I'd expect just about any process with those data volumes to take no more than an hour or so. Those data volumes are pretty small.
I suspect your problem is too much procedural code and lots of loops.
You want to do as much work as possible in a single sql statement.
So if you're reading data from one table and inserting it into another then in an ideal world that would be a single insert/select statement.
I'm guessing you've got lots of loops and single row insert/update/deletes.
The select at the top may be optimal (we really can't tell without additional information about your tables/system) but the fact that you're calling it many times probably isn't optimal.
You want to get all the data you need in as few select statements as possible.
One comment on the selects themselves - you never need to distinct an in sub-query.
So:
WHERE NSN_B IN (SELECT DISTINCT NSN
Can be:
WHERE NSN_B IN (SELECT NSN
Whether that makes any noticable difference I have no idea.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
