Re: Stored proc slower than direct sql query. Help needed

This is extremely ugly for a number of reasons. Here are a few thoughts though they don't directly relate to your issue.

1. Define default values when you declare your variables. For example:

CURRENTUSERID VARCHAR2(15) := 'joseph';

2. Define your variables based on fields. For example:

NetIdent rptactivity.vclistuser%TYPE;

3. Get rid of variables you aren't using. For example:

PROCCODESLIST := NULL; ... Why declare a VARCHAR2(1000), make it null (it already is) and then not use it. Totally superflous.

Same thing goes for: SDATE := TO_DATE('01/01/1900','MM/DD/YYYY'). SDate is never used.

Clean up your mess and then I'll be happy to look at it again. Right now looking at your code is like trying to swim in vaseline.

Daniel Morgan

JGP wrote:

> 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
> TO_DATE('01/01/2000','MM/DD/YYYY') ) --!!Here and below
> 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
> TO_DATE('01/01/2000','MM/DD/YYYY') )
> OR ( RecordDateType2 = 'A' AND r.dtadmitdate BETWEEN
> TO_DATE('01/01/1900','MM/DD/YYYY') AND
> TO_DATE('01/01/2000','MM/DD/YYYY') )
> OR ( RecordDateType2 = 'D' AND r.dtdischargedate BETWEEN
> TO_DATE('01/01/1900','MM/DD/YYYY') AND
> TO_DATE('01/01/2000','MM/DD/YYYY') )
> OR ( RecordDateType2 = 'R' AND r.dtcreateddate BETWEEN
> TO_DATE('01/01/1900','MM/DD/YYYY') AND
> TO_DATE('01/01/2000','MM/DD/YYYY') )
> OR ( RecordDateType2 = 'M' AND o.dtadjustmentmadedate
> BETWEEN TO_DATE('01/01/1900','MM/DD/YYYY') AND
> TO_DATE('01/01/2000','MM/DD/YYYY') )
> OR ( RecordDateType2 = 'Q' AND o.dtadjustmentrequestdate
> BETWEEN TO_DATE('01/01/1900','MM/DD/YYYY') AND
> TO_DATE('01/01/2000','MM/DD/YYYY') )
> )
> 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
> );
> Counter := SQL%ROWCOUNT;
> DBMS_OUTPUT.PUT_LINE(Counter);
>
>
> 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
> >
> > Regards
Received on Thu Apr 11 2002 - 14:49:43 CDT