>Dear Experts
>
>I have following codes, it work fine but it take more thant 4 minutes.
>Is it possible to make these more speedy
>
>
>
>ldstart = Thisform.text1.Value
>ldend = Thisform.text2.Value
>
>Select stock_r
>Zap
>
>Public f_date1,t_date1
>acc_code1=Thisform.text11.Value
>
>Do While ldstart <= ldend
>
>
>
> Select t1.Code, t1.Desc, Cast(Nvl(t1.qty,0) As Numeric(6))As Open, ;
> CAST(Nvl(t2.qty,0) + Nvl(t3.qty,0) As Numeric(6))As purchased,;
> CAST(Nvl(t4.qty,0) + Nvl(t5.qty,0) As Numeric(6))As sold,;
> CAST(Nvl(t1.qty,0) + Nvl(t2.qty,0) +;
> Nvl(t3.qty,0)- Nvl(t4.qty,0)- Nvl(t5.qty,0)As Numeric(6)) As closing ;
> from master1 t1 ;
> left Join ;
> (Select acc_code,Nvl(Sum(qty),0) As qty ;
> from Product ;
> where Product.Date<m.ldstart ;
> group By 1) t2 On t1.Code = t2.acc_code ;
> left Join ;
> (Select pcode,Nvl(Sum(qty),0) As qty ;
> from cashprp ;
> where cashprp.Date<m.ldstart ;
> group By 1) t3 On t1.Code = t3.pcode ;
> left Join ;
> (Select acc_code,Nvl(Sum(qty),0) As qty ;
> from crsalp ;
> where crsalp.Date<m.ldstart ;
> group By 1) t4 On t1.Code = t4.acc_code ;
> left Join ;
> (Select pcode,Nvl(Sum(qty),0) As qty ;
> from cashsalp ;
> where cashsalp.Date<m.ldstart ;
> group By 1) t5 On t1.Code = t5.pcode ;
> into Cursor crsopening ;
> nofilter
>
>
>
> Select t1.Code, t1.Desc, t2.qty As sno, Cast(Nvl(t1.qty,0) As Numeric(6))As Open, ;
> CAST(Nvl(t2.qty,0) + Nvl(t3.qty,0) As Numeric(6))As purch,;
> CAST(Nvl(t1.qty,0) + Nvl(t2.qty,0) As Numeric(6))As Total,;
> CAST(Nvl(t4.qty,0) + Nvl(t5.qty,0) As Numeric(6))As sold,;
> CAST(Nvl(t1.qty,0) + Nvl(t2.qty,0) + Nvl(t3.qty,0)- Nvl(t4.qty,0)- Nvl(t5.qty,0)As Numeric(6)) As closing ;
> from (Select Code, Desc, closing As qty From crsopening) t1 ;
> left Join ;
> (Select acc_code,Sum(qty) As qty ;
> from Product ;
> where Product.Date= ldstart ;
> group By 1) t2 On t1.Code = t2.acc_code ;
> left Join ;
> (Select pcode,Sum(qty) As qty ;
> from cashprp ;
> where cashprp.Date=ldstart ;
> group By 1) t3 On t1.Code = t3.pcode ;
> left Join ;
> (Select pcode,Sum(qty) As qty ;
> from cashsalp ;
> where cashsalp.Date= ldstart ;
> group By 1) t4 On t1.Code = t4.pcode ;
> left Join ;
> (Select acc_code,Sum(qty) As qty ;
> from crsalp ;
> where crsalp.Date= ldstart ;
> group By 1) t5 On t1.Code = t5.acc_code ;
> into Cursor crssecond Readwrite
>
> Select crssecond
> ptot0=open
> ptot1=purch
> ptot3=total
> ptot4=sold
> ptot6=closing
>
> Select stock_r
> Append Blank
> Replace Date With ldstart,opening With ptot0
> Replace pur With m.ptot1,;
> total With Opening+m.ptot1, sold With m.ptot4,;
> closing WITH Opening+m.ptot1-m.ptot4
>
> ldstart = ldstart+1
>ENDDO
>
>
Could you run some of those derived tables once ahead of time and then use them inside the loop rather than doing all of them each time through the loop?
Tamar
