>Hi Suhas,
>
>first of all, unless you have an extreme situation, do NOT, I repeat, do NOT use idx, add tags to the cdx instead. To speed up the select statements you need index tags which EXACTLY match the where statements. And index tags are created ONCE, and ONLY ONCE! If you create an index tag today, that index tag will stay updated and available forever.
>
>See inline comments

>Table use - exclusive on
>Table structure
>bhav_data.dbf free
>(symbol c(20),series c(20),open f(20,2),high f(20,2),low f(20,2),close f(20,2),;
>last f(20,2),prevclose f(20,2),tottrdqty f(20),tottrdval f(20,2),;
>sma9 f(20,4),sma12 f(20,4),sma26 f(20,4),;
>timestamp c(20),date d,delqty f(20),delpercent f(20,2))
>
>INDEX ON DTOS(DATE)+SYMBOL TAG Primary key
>INDEX ON SYMBOL TAG SYMBOL && It's easier to remember SYMBOL than SY
>index on date tag date && Same as above
>*By the way, I would change the name of the data field since DATE is a reserved word!
>
>
>Select dist symbol from bhav_data into cursor dist1 nofilter && runs fast
>select Dist1
>scan
> select open from bhav_data where symbol = dist1.symbol into array aopen order by date
>endscan
>
>
>*The above runs verrrry slow
>
>Select dist symbol from bhav_data into cursor dist1 nofilter && runs fast
>
>Select bhav_data
>* index on symbol tag sy && Ignore, you already have this index tag"
>set order to symbol
>select Dist1
>
>scan
> lcSymbol=dist1.symbol && It's faster to use a variable so VFP must not evaluate dist1.symbol for every record
> select open from bhav_data where symbol = lcSymbol into array aopen order by date
>endscan
>
>*The above runs slow
>
>Select dist symbol from bhav_data into cursor dist1 nofilter && runs fast
>
>Select bhav_data
>* index on symbol to symbol.idx Remove, you already have an index tag on symbol!
>* set order to 1 && Dangerous syntax, do you always know the index order
>set order to symbol
>select Dist1
>set relation to symbol into bhav_data
>scan
> lcSymbol=dist1.symbol && It's faster to use a variable so VFP must not evaluate dist1.symbol for every record
> select open from bhav_data where symbol = lcSymbol into array aopen order by date
>endscan

>
>
>
>

Hi,
thanx for the reply

Yes I know that the index tags created once and if the index is open other than the structural index it is always updated.

If i rebuild the index (.idx) in code I get verry fast speeds may be up to 3x fast

But if i use the already built indexes either cdx or idx doesnt matter it is slow

even if i rebuilt the CDX symbol it will be 2x fast

Now How should i proceed ?

this is a standalone program with no network users and ofcourse single instance only.

I have tried maybe all ways i know and ended up with idx. I know IDX can be dangarous But in terms of speed ?? what else Can i use

Users would need to run the above procedure for many analysis and if each start taking up 5 minuts (conservative estimate) i am as good as dead.

But with IDX it speeds up to complete in under 1.5 minutes which means I can stay alive

BUT Question if CDX is fast enough then How can i utilise it ?

Thnax for your views

suhashegde