Re: Performance Tuning.

On 21 Oct 2004 01:14:35 -0700, satishsanthanam_at_yahoo.com (Satish) wrote:

>Hi,
>
>I have a loop control structure as below. This seem to take a lot of
>time. Is there any way by which I can improve the performance by
>changing the loop control structure..
>
> for v_Counter in 1..v_schedule.count loop
> v_CouponRecord := v_schedule(v_Counter);
> if (v_CouponRecord.pay_date >= i_start) Then
> i_t := calculate_years_between_ytdt(i_start,v_CouponRecord.pay_date,security_record,v_schedule,
>FALSE);
> if ( i_eType = eFactor) then
> nfactor := factor(i_r,i_t,
>security_record.yield_frequency);
> end if;
> if (i_eType = eDFactor) then
> nfactor := dFactor(i_r,i_t,
>security_record.yield_frequency);
> end if;
> couponAmount :=
>getCouponAmount(v_CouponRecord.pay_date,security_record,v_schedule);
> if (v_CouponRecord.pay_date = security_record.maturity_date)
>then
> couponAmount := couponAmount + 1.0;
> end if;
> total := total + couponAmount * nfactor;
> end if;
> end loop;
>
>Thanks,
>Satish

Difficult to see due to line wrapping.
However, you seem to reject records procedurally you have already selected (if (v_CouponRecord.pay_date >= i_start) Then) so it looks like you need to modify you sql (guideline: never do anything procedurally you can do using sql) You could also change the two tests on i_etype into an if then elseif structure.

--
Sybrand Bakker, Senior Oracle DBA