| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Stored proc slower than direct sql query. Help needed
Intentionally vague..that's a curious phrase! Anyways, here's the
code:
DECLARE
Counter INTEGER;
CurrentUserID VARCHAR2(15);
SavingsByCombo VARCHAR2(2);
DivisionsList VARCHAR2(1000);
Ex_Divisions CHAR(1);
ClaimTypesList VARCHAR2(1000);
Ex_ClaimTypes CHAR(1);
ClaimNumber VARCHAR2(1000);
ProvidersList VARCHAR2(1000);
Ex_Providers CHAR(1);
RecordDateType CHAR(1);
BeginDate VARCHAR2(20);
EndDate VARCHAR2(20);
AuditNumber VARCHAR2(1000);
AuditTypesList VARCHAR2(1000);
Ex_AuditTypes CHAR(1);
SavingsGreaterThanZero CHAR(1);
AppealDecsList VARCHAR2(1000);
Ex_AppealDecs CHAR(1);
ReviewersList VARCHAR2(1000) ;
Ex_Reviewers CHAR(1);
DispCodesList VARCHAR2(1000) ;
Ex_DispCodes CHAR(1);
GrossNetChk VARCHAR2(5);
IdentConfChk VARCHAR2(5);
OrderByCombo CHAR(1);
RecordDateType2 CHAR(1);
BeginDate2 VARCHAR2(20);
EndDate2 VARCHAR2(20);
SDate DATE;
EDate DATE;
SDate2 DATE;
EDate2 DATE;
EditCodesList VARCHAR2(1000);
Ex_EditCodes CHAR(1);
DRGCodesList VARCHAR2(1000) ;
Ex_DRGCodes CHAR(1);
DiagCodesList VARCHAR2(1000) ;
Ex_DiagCodes CHAR(1);
ProcCodesList VARCHAR2(1000) ;
Ex_ProcCodes CHAR(1);
ClientId VARCHAR2(20);
BEGIN
CURRENTUSERID := 'joseph';
SAVINGSBYCOMBO := NULL;
DIVISIONSLIST := NULL;
EX_DIVISIONS := NULL;
CLAIMTYPESLIST := NULL;
EX_CLAIMTYPES := NULL;
CLAIMNUMBER := '9904332054400';
PROVIDERSLIST := NULL;
EX_PROVIDERS := NULL;
RECORDDATETYPE := 'D';
BEGINDATE := NULL;
ENDDATE := NULL;
AUDITNUMBER := NULL;
AUDITTYPESLIST := NULL;
EX_AUDITTYPES := NULL;
SAVINGSGREATERTHANZERO := NULL;
APPEALDECSLIST := NULL;
EX_APPEALDECS := NULL;
REVIEWERSLIST := NULL;
EX_REVIEWERS := NULL;
DISPCODESLIST := NULL;
EX_DISPCODES := NULL;
GROSSNETCHK := 'Gross';
IDENTCONFCHK := 'Ident';
ORDERBYCOMBO := NULL;
RECORDDATETYPE2 := 'A';
BEGINDATE2 := NULL;
ENDDATE2 := NULL;
SDATE := TO_DATE('01/01/1900','MM/DD/YYYY');
EDATE := TO_DATE('01/01/2000','MM/DD/YYYY');
SDATE2 := TO_DATE('01/01/1900','MM/DD/YYYY');
EDATE2 := TO_DATE('01/01/2000','MM/DD/YYYY');
EDITCODESLIST := NULL;
EX_EDITCODES := NULL;
DRGCODESLIST := NULL;
EX_DRGCODES := NULL;
DIAGCODESLIST := NULL;
EX_DIAGCODES := NULL;
PROCCODESLIST := NULL;
EX_PROCCODES := NULL;
CLIENTID := '1';
INSERT INTO cas.RPTACTIVITY
(vclistuser, sortid1, vcnumber, vcprovidername, dtdischargedate,
rcoveredcharges, sidrg, sinewdrg, ractualpay, vcreviewername,
savings)
SELECT CURRENTUSERID, --!!Here
DECODE (
'1',
'1', p.vcname,
'2', ( u.vclastname
|| ' '
|| u.vcfirstname
),
'3', NULL
),
r.vcnumber, p.vcname, r.dtdischargedate, r.rcoveredcharges,
r.sidrg,
o.sinewdrg, DECODE (GrossNetChk, 'Gross', o.rgrosspay,
'Net', o.rnetpay), --!!Here
( u.vclastname
|| ' '
|| u.vcfirstname
),
DECODE (
GrossNetChk||IdentConfChk, --!!Here
'GrossIdent', ( r.rgrosspay
- o.rnewgrosspay
),
'GrossConf', ( r.rgrosspay
- o.rauditedgrosspay
),
'NetIdent', ( r.rnetpay
- o.rnewnetpay
),
'NetConf', ( r.rnetpay
- o.rauditednetpay
),
NULL
)
FROM cas.REVIEWS r,
cas.AUDITCLAIMS ac,
cas.users u,
cas.AUDITS a,
cas.OUTCOMES o,
cas.DIVISIONS d,
cas.DIVISIONS d1,
(SELECT ip.*, id.sicode, id.sidefaultdivision
FROM cas.PROVIDERS ip, cas.DIVISIONS id
WHERE ip.sidivisioncode = id.sidefaultdivision) p
WHERE (r.uclaimkey = ac.uclaimkey AND r.iauditnumber =
ac.iauditnumber)
AND (u.vcuserid = r.vcauditorid AND u.vcclientid = CLIENTID)
AND (ac.iauditnumber = a.iauditnumber AND a.vcauditstatus = 'C')
AND ( o.uclaimkey = ac.uclaimkey(+)
AND o.iauditnumber = ac.iauditnumber(+)
)
AND r.sidivisioncode = d.sicode(+)
AND d.sidefaultdivision = d1.sicode(+)
AND r.sidivisioncode = p.sicode(+)
AND r.vcprovidernumber = p.vcnumber(+)
AND ( ( RecordDateType = 'C' AND a.dtclosedate BETWEEN
TO_DATE('01/01/1900','MM/DD/YYYY') AND
OR ( RecordDateType = 'A' AND r.dtadmitdate BETWEEN
TO_DATE('01/01/1900','MM/DD/YYYY') AND
TO_DATE('01/01/2000','MM/DD/YYYY') )
OR ( RecordDateType = 'D' AND r.dtdischargedate BETWEEN
TO_DATE('01/01/1900','MM/DD/YYYY') AND
TO_DATE('01/01/2000','MM/DD/YYYY') )
OR ( RecordDateType = 'R' AND r.dtcreateddate BETWEEN
TO_DATE('01/01/1900','MM/DD/YYYY') AND
TO_DATE('01/01/2000','MM/DD/YYYY') )
OR ( RecordDateType = 'M' AND o.dtadjustmentmadedate
BETWEEN TO_DATE('01/01/1900','MM/DD/YYYY') AND
TO_DATE('01/01/2000','MM/DD/YYYY') )
OR ( RecordDateType = 'Q' AND o.dtadjustmentrequestdate
BETWEEN TO_DATE('01/01/1900','MM/DD/YYYY') AND
TO_DATE('01/01/2000','MM/DD/YYYY') )
)
AND ( ( RecordDateType2 = 'C' AND a.dtclosedate BETWEEN
TO_DATE('01/01/1900','MM/DD/YYYY') AND
)
AND ( NULL IS NULL
OR NULL IS NOT NULL
AND DECODE (
'GrossIdent',
'GrossIdent', r.rgrosspay
- o.rnewgrosspay,
'GrossConf', r.rgrosspay
- o.rauditedgrosspay,
'NetIdent', r.rnetpay
- o.rnewnetpay,
'NetConf', r.rnetpay
- o.rauditednetpay
) > 0.0
);
COMMIT;
END;
Thanks for any help.
"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:<ub97bg51rhb911_at_corp.supernews.com>...
> "JGP" <deja_at_chothu.mailshell.com> wrote in message > news:1e9cd49d.0204101107.486f31fb_at_posting.google.com... > > Hi, > > > > I've run into a frustrating problem and I just can't seem to find a > > solution. I have a function which has a fairly complicated Insert > > statement. When I compile and execte the function with parameters, it > > takes about 5-10 mins and the oracle machine CPU becomes extremely > > loaded. When I directly run the Insert statement which was in the > > function, it does it much faster within 30 secs. Can someone please > > help. Thanks. > > > > JGP > > If you want help please don't be intentionally vague and post the source of > your procedure. Crystal balls are not being sold here > > RegardsReceived on Thu Apr 11 2002 - 11:05:54 CDT
 |
 |