timing concerns on view, stored proc giving errors....

[LMCRYER]

I had a view that worked, but was really slow.
The view is this:

SELECT
PLGR.PROJ_ELEM_ID,
PLGR.BID_CTGY,
' ' AS RES,
'N' AS OT,
(SUBSTR(PLGR.PRD,2,4)) as ERP_PRD,
decode (PLGR.PRD,
'10212' ,
'200203',
'10301' ,
'200204',
'10302' ,
'200205',
'10303' ,
'200206',
'10304' ,
'200207',
'10305' ,
'200208',
'10306' ,
'200209',
'10307' ,
'200210',
'10308' ,
'200211',
'10309' ,
'200212',
'10310' ,
'200301',
'10311' ,
'200302',
'10312' ,
'200303',
'10401' ,
'200304',
'10402' ,
'200305',
'10403' ,
'200306',
'10404' ,
'200307',
'10405' ,
'200308',
'10406' ,
'200309',
'10407' ,
'200310',
'10408' ,
'200311',
'10409' ,
'200312',
'10410' ,
'200401',
'10411' ,
'200402',
'10412' ,
'200403',
'10501' ,
'200404',
'10502' ,
'200405',
'10503' ,
'200406',
'10504' ,
'200407',
'10505' ,
'200408',
'10506' ,
'200409',
'10507' ,
'200410',
'10508' ,
'200411',
'10509' ,
'200412',
'10510' ,
'200501',
'10511' ,
'200502',
'10512' ,
'200503',
'10601' ,
'200504',
'10602' ,
'200505',
'10603' ,
'200506',
'10604' ,
'200507',
'10605' ,
'200508',
'10606',
'200509',
'10607',
'200510',
'10608',
'200511',
'10609',
'200512',
'10610',
'200601',
'10611',
'200602',
'10612',
'200603',
'10701',
'200604',
'10702',
'200605',
'10703',
'200606',
'10704',
'200607',
'10705',
'200608',
'10706',
'200609',
'10707',
'200610',
'10708',
'200611',
'10709',
'200612',
'10710',
'200701',
'10711',
'200702',
'10712',
'200703',
'10801',
'200704',
'10802',
'200705',
'10803',
'200706',
'10804',
'200707',
'10805',
'200708',
'10806',
'200709',
'10807',
'200710',
'10808',
'200711') AS MPM_PRD,
SUM_UNIT,
(nvl(PLGR.CUR_ACT ,
PLGR.PRD_ACT) ) AS AMT
FROM
CSIOWNER.PLGR_ELK PLGR,
CSIOWNER.PELM PELM
WHERE
PLGR.PROJ_ELEM_ID = PELM.PROJ_ELEM_ID AND
PLGR.ORG_ID = PELM.ORG_ID AND
PLGR.ORG_ID = '001' AND
PLGR.BID_CTGY <> '**' AND
PELM.COST_MODE = 'D' AND
SUBSTR(PLGR.PROJ_ELEM_ID,1,4) = '2984' AND
(SUBSTR(PLGR.PRD,2,4)) = '0405'
group by PLGR.PRD,PLGR.PROJ_ELEM_ID, PLGR.BID_CTGY, PLGR.SUM_UNIT, PLGR.CUR_ACT, PLGR.PRD_ACT
ORDER BY PROJ_ELEM_ID, BID_CTGY

ACTION - SELECT * FROM MPM_1
RESULTS - 92 RECORDS, 6 SECONDS

However, the user needs to be able to plug in some of the values at runtime, so I had to make modifications:

ACTION : removed lines from view
SUBSTR(PLGR.PROJ_ELEM_ID,1,4) = '2984' AND
SUBSTR(PLGR.PRD,2,4)) = '0405'
ACTION: SELECT * FROM MPM_1 WHERE SUBSTR(PLGR.PROJ_ELEM_ID,1,4) = '2984' AND
(SUBSTR(PLGR.PRD,2,4)) = '0405'
RESULTS - 92 RECORDS, 45 SECONDS

This timing (45 seconds) is not acceptable.
I've been trying a lot of different things, mostly becuase I dont know Oracle syntax and I think I am getting somewhere but need some guidance.

I made a stored procedure (see below) -- (note, due to the length I have omitted some of the decode part)...

CREATE OR REPLACE procedure mpm_tester_090804


(IN_PROJ_NBR IN CHAR,
IN_PRD IN NUMBER,
OUT_PROJ_ELEM_ID OUT CHAR,
OUT_BID_CTGY OUT CHAR,
OUT_RES OUT CHAR,
OUT_OT OUT CHAR,
OUT_ERP_PRD OUT CHAR,
OUT_MPM_PRD OUT CHAR,
OUT_SUM_UNIT OUT CHAR,
OUT_AMT OUT NUMBER)


AS
BEGIN


SELECT
PLGR.PROJ_ELEM_ID,
PLGR.BID_CTGY,
' ' AS RES,
'N' AS OT,
(SUBSTR(PLGR.PRD,2,4)) as ERP_PRD,
decode (PLGR.PRD,
'10212' ,
'200203',
'10301' ,
'200204',
'10302' ,
'200205',
'10303' ,
'200206',
'10304' ,
'200207',
'10305' ,
'200208',
'10306' ,
'200209',
'10307' ,
'200210',
'10308' ,
'200211') AS MPM_PRD,
SUM_UNIT,
(nvl(PLGR.CUR_ACT ,
PLGR.PRD_ACT) ) AS AMT
INTO OUT_PROJ_ELEM_ID, OUT_BID_CTGY, OUT_RES, OUT_OT, OUT_ERP_PRD,OUT_MPM_PRD,OUT_SUM_UNIT,OUT_AMT
FROM
CSIOWNER.PLGR_ELK PLGR,
CSIOWNER.PELM PELM
WHERE
PLGR.PROJ_ELEM_ID = PELM.PROJ_ELEM_ID AND
PLGR.ORG_ID = PELM.ORG_ID AND
PLGR.ORG_ID = '001' AND
PLGR.BID_CTGY <> '**' AND
PELM.COST_MODE = 'D' AND
SUBSTR(PLGR.PROJ_ELEM_ID,1,4) = IN_PROJ_NBR AND
(SUBSTR(PLGR.PRD,2,4)) = IN_PRD;
END;
/

ACTION: Tried to "call" the stored procedure like this:

call MPM_TESTER_090804 ('2984',0405,:OUT_PROJ_ELEM_ID,
:OUT_BID_CTGY,
:OUT_RES,
:OUT_OT,
:OUT_ERP_PRD,
:OUT_MPM_PRD,
:OUT_SUM_UNIT,
:OUT_AMT)


RESULTS: Error message!!!
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "MRPOWNER.MPM_TESTER_090804", line 20
ORA-06512: at line 1

I'm at a loss again. I was so excited to figure out how to do the input and output stuff in the stored procedure and now I cant seem to EXEC it (or CALL, or whatever I'm supposed to do!!)

Any ideas on how to improve this? Help???

LMC





LMC

 

[carp]

First - for the performance issue, are there any indexes on your tables? If so, which columns are they on? And can you add indexes if you need to?

Secondly - for you procedure: the error you are getting stems from trying to stuff more than one value into one variable. How to get around this depends a lot on what you want to do with the results once you get them - display them on a web page, write them to a file, ....???

 

[LMCRYER]

Yes, there are indexes, and I've used them already, there was a huge improvement from doing so (as expected) but yet not enough to be usable.

This is a commercial app and we cannot add indexes. We can make views though (hey, can you put an index on a view in Oracle? I assumed that if you make a view that it just uses the indexes on the tables that are underneath of it, right?) My assumptions tend to get me in trouble - am I wrong about this?

The intent is the following:

Data will be 1. called in through a vb application and displayed 2. data will be formatted and written to a text file for push to the ERP system and 3. data will be used for several crystal reports and distributed to the users via Crystal Enterprise).

It seems to me that Oracle is just so darn...messy! What I could do in 5 minutes in a stored procedure in SQL requires an awful lot of extra work in Oracle, no?

All I really need to do is get that user input pushed to the WHERE statement at runtime, and the only way to do that is through a procedure, yes?



LMC

 

[sem]

Try to replace

SUBSTR(PLGR.PROJ_ELEM_ID,1,4) = '2984'

by

PLGR.PROJ_ELEM_ID like '2984%'

and add a condition

.. and ROWNUM=1

to limit a number of records returned. BTW you may also consider using REF CURSOR.

I think that almost anything may be done with stored procedures in Oracle, but you should be familiar with pl/sql at least. Are you?

I also find T-SQL and VB more then messy, because I don't know them and also find your PL/SQL code really lame because of the opposite.

Regards, Dima

http://seminihin.narod.ru