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

Sorry about that guys. Here's the revised code. I noticed that when I replace the variables below with hard-coded values directly in the query, it becomes a lot slower (similar to the stored proc). Your suggestions are appreciated.

DECLARE
    Counter INTEGER;

    CurrentUserID		   VARCHAR2(15);
    GrossNetChk            VARCHAR2(5);
    IdentConfChk           VARCHAR2(5);
    RecordDateType         VARCHAR2(20);
    RecordDateType2        VARCHAR2(20);
    SDate                  DATE;
    EDate                  DATE;
    SDate2                 DATE;
    EDate2                 DATE;
    ClientId               VARCHAR2(20);
  

BEGIN
  CURRENTUSERID := 'joseph';
  GROSSNETCHK := 'Gross';
  IDENTCONFCHK := 'Ident';
  RECORDDATETYPE := 'D';
  RECORDDATETYPE2 := 'A';
  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');   CLIENTID := '1'; INSERT INTO CAS.RPTACTIVITY

         (vcListUser,
          SortId1,
          vcNumber,
          vcProviderName,
          dtDischargeDate,
          rCoveredCharges,
          siDRG,
          siNewDRG,
          rActualPay,
          vcReviewerName,
          Savings)
   SELECT
       CurrentUserId,
		  DECODE(OrderByCombo, '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),
       (U.vcLastName || ' ' || U.vcFirstName),
	   DECODE(GrossNetChk||IdentConfChk, '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           --Joins start here
          R.iAuditNumber = AC.iAuditNumber)

     AND (U.vcUserId = R.vcAuditorId AND
          U.vcClientId = ClientId)

     AND (AC.iAuditNumber = A.iAuditNumber AND  -- filter out all
except Closed Audits
            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		 	   				--Where clause starts
here
          A.dtCloseDate BETWEEN SDate AND EDate
          OR
          RecordDateType = 'A' AND
          R.dtAdmitDate BETWEEN SDate AND EDate
          OR
          RecordDateType = 'D' AND
          R.dtDischargeDate BETWEEN SDate AND EDate
          OR
          RecordDateType = 'R' AND
          R.dtCreatedDate BETWEEN SDate AND EDate
          OR
          RecordDateType = 'M' AND
          O.dtAdjustmentMadeDate BETWEEN SDate AND EDate
          OR
          RecordDateType = 'Q' AND
          O.dtAdjustmentRequestDate BETWEEN SDate AND EDate
          ) AND
         (RecordDateType2 = 'C' AND
          A.dtCloseDate BETWEEN SDate2 AND EDate2
          OR
          RecordDateType2 = 'A' AND
          R.dtAdmitDate BETWEEN SDate2 AND EDate2
          OR
          RecordDateType2 = 'D' AND
          R.dtDischargeDate BETWEEN SDate2 AND EDate2
          OR
          RecordDateType2 = 'R' AND
          R.dtCreatedDate BETWEEN SDate2 AND EDate2
          OR
          RecordDateType2 = 'M' AND
          O.dtAdjustmentMadeDate BETWEEN SDate2 AND EDate2
          OR
          RecordDateType2 = 'Q' AND
          O.dtAdjustmentRequestDate BETWEEN SDate2 AND EDate2
          ) AND
          (SavingsGreaterThanZero IS NULL
           OR
           SavingsGreaterThanZero IS NOT NULL AND
    	   DECODE(GrossNetChk||IdentConfChk, 'GrossIdent' , R.rGrossPay -
O.rNewGrossPay,
                                             'GrossConf' , R.rGrossPay
- O.rAuditedGrossPay,
                                             'NetIdent' , R.rNetPay -
O.rNewNetPay,
                                             'NetConf' , R.rNetPay -
O.rAuditedNetPay) > 0.0
          );

COMMIT; Counter := SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE(Counter);
END; Daniel Morgan <damorgan_at_exesolutions.com> wrote in message news:<3CB5E8D5.7033BAFA_at_exesolutions.com>...

> 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:
>