>>>hi,
>>>
>>>I always find Xbase commands faster than Sql(statement) commands.
>>>
>>>
>>>Any case where Sql statments are faster ?
>>>Also i would like to know best prsctices where to use what ??
>>>
>>>
>>>TIA
>>>suhashegde
>>
>>Post a example where XBASE is faster.
>
>Hi,
>
>I have this problem.
>
>the table is
>
>create table 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,2),tottrdval f(20,2),timestamp c(20),date d,delqty i,delpercent f(20,2))
>
>contains 266928 records
>size 65,951 kb
>
>test machine AMD k6 500mhz , 256 mb ram , 40Gb hd 5600rpm
>
>It is index like
>
>Index on timestamp tag t1
>index on timestamp tag tq unique additive
>
>cdx file size is 1,237 kb
>
>when i use this sql
>
>tt = seconds()
>select dist timestamp from bhav_data into cursor test nofilter
>?tt-seconds()
>
>it runs at 0.819 sec returning 341 recs
>
>and when i do
>
>m.t1 = seconds()
>
>use bhav_data in 0 order tq
>create cursor test(timestamp c(20))
>index on t1 tag t1
>select bhav_data
>scan for !seek(timestamp,'test','t1')
> insert into test(timestamp) values(bhav_data.timestamp)
>endscan
>
>select test
>locate && to set the record pointer to 1 as to compare case of sql
>delete tag t1
>
>?seconds() - m.t1
>
>it runs in 0.288 sec returning 341 rec
>
>Kindly tell me how can i still optmise the sql please ???
>
>suhashegde

Hi,
this is where VFP is not optimized.

With the tq index you do the distinct operation when you write into bhav_data,
the VFP sql engine is not able to optimize ( use a existing index )
the DISTINCT/GROUP BY/ORDER BY queries.

CLEAR 

create table 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,2),tottrdval f(20,2),timestamp c(20),date d,delqty i,delpercent f(20,2))

FOR K=1 TO 266928
	APPEND BLANK
	REPLACE timestamp WITH STR(RAND()*10000)
NEXT
*	contains 266928 records
*	size 65,951 kb

Index on timestamp tag t1 FOR NOT DELETED()
tt = seconds()
index on timestamp tag tq unique ADDITIVE  && !!!!!!!
? "distinct index build",seconds()-m.tt


tt = seconds()
* this query rebuild tq and copy timestamp into test
select distinct timestamp from bhav_data into cursor test
? seconds()-m.tt,_tally

m.t1 = seconds()

use bhav_data in 0 order tq	again ALIAS ttt && WHEN YOU OPEN THE tq INDEX, YOU DO DISTINCT USING THE PAST TIME !!!!
create cursor test(timestamp c(20))
*	index on timestamp  tag t1 THIS IS USELESS
select ttt
* this scan uses tq !!!
scan 	&& for !seek(timestamp,'test','t1') THIS IS USELESS
	insert into test VALUES (ttt.timestamp)
endscan
USE

select test
locate && to set the record pointer to 1 as to compare case of sql
* delete tag t1

?seconds() - m.t1,reccount()

*  THIS IS OPTIMIZED INTO vfp9 : vfp9 = 4ms  VFP8 = 500ms

m.t1 = seconds()
SELECT MAX(timestamp) FROM bhav_data INTO ARRAY DDDD
? SECONDS()-T1

If VFPT optimize VFP10 for DISTINCT/GROUP BY/ORDER BY,
then SQL it will win vs XBASE
( I Expected a VFP can use the t1 tag ( as for the MAX() case )
with a 3/5 times faster respect to XBASE tq unique index)