>hi,
>can the below code be modified to process much faster ?
>
>Bhav_data contains distinct 1000 symbols and 1000 dates so total records is 1000*1000 = 1000000
>
>bhav_data has indexs on padr(symbol,20) and date
>
>
>select symbol from bhav_data group by 1 into cursor distsymbol nofilter
>scan
> wait window distsymbol.symbol nowait
> select date,close from bhav_data where symbol = distsymbol.symbol into cursor s1 nofilter
> use dbf('s1') in 0 alias s2 again
> select s1
> set relation to recno()+1 into s2
> create cursor gainloss(date D,gain f(10,2),loss f(10,2))
> select s1
> scan
> if eof('s2')
> exit
> endif
> m.diff = s1.close - s2.close
> if m.diff > 0
> insert into gainloss values(s2.date,m.diff,0)
> else
> insert into gainloss values(s2.date,0,m.diff*-1.00)
> endif
> endscan
> use in select('s2')
> use in select('s1')
>
> select gainloss
> index on date tag date1 desc
> locate
> use dbf('gainloss') in 0 alias gainloss1 again
> select gainloss1
> index on date tag date2 desc
> select gainloss
> set relation to date into gainloss1
> scan
> select gainloss1
> average gain,loss next 14 to m.gain,m.loss
> m.rs = m.gain/m.loss
> update bhav_data set RSI = (100-(100/(1+m.rs))) ;
> where date = gainloss.date and ;
> symbol = distsymbol.symbol
> endscan
> use in select('gainloss')
> use in select('gainloss1')
>endscan
>
>>
>it takes abt 15 - 20 minutes to process
>
>is it ok or can it be made faster ?
Suhas,
I can say it's not OK at all. A VFP coder would never wait 15-20 mins for such a calculation. It's somewhat self confidence (or confidence to VFP) to say this but I expect it to be done much quicker.
1) You have select sql inside scan...endscan which to your saying would be done at least 1000 times! That byself is a bottleneck. Instead scan .. endscan inside scan .. endscan might yield to an extreme performance difference.
2) Instead of selecting a symbol set into cursor, using again and relating, you might utilize old good "copy to array aSet while symbol = curSymbol" and work with array members. It'd advance the pointer and thus you'd in effect be doing a single scan...endscan.
In both approaches you'd skip -1 to move the pointer 1 back (endscan advances).
Cetin
