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 all,
>
>I have tried various indexes to run the following commands
>
>System - intel Dualcore , 512 mb ram Sata harddisk
>
>Datasession - global
>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 PK
> INDEX ON SYMBOL TAG SY
> index on date tag da
>
>Table size = about 300 MB
>record count = 750000
>
>records returnd in dist1 = 1200 approximate
>records returned in array = abt 650 for each record in dist1
>
>Code what i want to use
>

>
>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
>set order to tag sy
>select Dist1
>
>scan
> select open from bhav_data where symbol = dist1.symbol 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
>set order to 1
>select Dist1
>set relation to symbol into bhav_data
>scan
> select open from bhav_data where symbol = dist1.symbol into array aopen order by date
>endscan
>
>*The above runs verry verry fast any tim first time or second time
>
>* tried variations
>* without setting the order and relation,
>* tried using already built IDX Or CDX instead fresh build
>* all the other methods runs very slow the first time and the n fast second time without restarting the system or VFP (maybe uses cached copy)
>
>

>
>
>The problem is i need to use index ... to in code atleast once before the scan..endscan
>Index to tag not as fast as index to idx
>
>Also even if i use the alreasy build idx like
>use bhav_data index symbol.idx
>
>does not speed up execution of the scan .. endscan
>
>there are no data changes made to the free table , no insert, no delete , no update.
>Tried with restarting the system each time to remove cached copied of the table
>
>
>Any advise would help
>TIA
>Suhashegde