>>>>>>>>>>
>>>>>>>>>>An Index caching overload effect ?
>>>>>>>>>>
>>>>>>>>>>try, and post this time:
>>>>>>>>>>
>>>>>>>>>>use bhav_data
>>>>>>>>>>SET EXACT ON
>>>>>>>>>>=lOOKUP(symbol, m.THISVALUE,symbol)
>>>>>>>>>>SELECT date,open,high,low,close,tottrdqty,sma9,sma12,sma26 FROM bhav_data;
>>>>>>>>>>where symbol = m.THISVALUE INTO CURSOR tempDETAIL nofilter
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>Minimum time taken is 9 secs
>>>>>>>>>
>>>>>>>>>but if i run the second time with the same value in thisvalue it runs under .5 secs
>>>>>>>>>
>>>>>>>>>like
>>>>>>>>>
>>>>>>>>>First run with thisvalue = 'ABCD' -- 10 secs
>>>>>>>>>second run with thisvalue = 'dfsd' -- 10 secs
>>>>>>>>>third run with thisvalue = 'ABCD' -- .5 secs
>>>>>>>>>
>>>>>>>>>that is wilhout cloing the table
>>>>>>>>
>>>>>>>>this confirm the caching effect.
>>>>>>>>
>>>>>>>>try
>>>>>>>>
>>>>>>>>SELECT date,open,high,low,close,tottrdqty,sma9,sma12,sma26 FROM bhav_data;
>>>>>>>>where symbol = m.THISVALUE AND symbol = symbol INTO CURSOR tempDETAIL nofilter
>>>>>>>>
>>>>>>>
>>>>>>>No the result is the same. the above doesnt make any difference.
>>>>>>>Can u hilight what u mean by cashing effect ? what is going on ?
>>>>>>
>>>>>>VFP load data into an internal cache ( a buffer memory).
>>>>>>
>>>>>>try this ( it minimize and clear the buffer, then the execution time is more stable ?):
>>>>>>
>>>>>>
>>>>>>SYS(3050, 1, 1) minimize the buffer size
>>>>>>
>>>>>>sys(1104)
>>>>>>SELECT .... WHERE .. = m.thisvalue INTO ...
>>>>>>
>>>>>
>>>>>
>>>>>No help
>>>>>same effect
>>>>
>>>>
>>>>>I put the SYS(3050, 1, 1) in init of the form or should i put it before the query ?
>>>>
>>>>One time is sufficient
>>>>>sys(1104)
>>>>>SELECT .... WHERE .. = m.thisvalue INTO
>>>>
>>>>Rerun VFP, and post the output of this:
>>>>
>>>>for k=1 to 5
>>>> sys(1104)
>>>> t1=seconds()
>>>> SELECT .... WHERE .. = m.thisvalue INTO
>>>> ? seconds()-m.t1
>>>>next
>>>>
>>>
>>>
>>>returned
>>>
>>>9.838
>>>0.0339
>>>0.0149
>>>0.0149
>>>0.017
>>
>>
>>9.838 - VFP reads data from hard drive into RAM.
>>
>>.0339 - VFP uses data in RAM - CACHE - not read from hard drive.
>
>
>what should i do to speed it up?
You will never get first query speed same as second one. CACHE means what was read from disk is now in RAM. RAM is much faster than disk.
Ignore second query speed. Only improve speed of first query.
SELECT date,open,high,low,close,tottrdqty,sma9,sma12,sma26 FROM bhav_data;
where symbol = m.THISVALUE INTO CURSOR tempDETAIL nofilter
slower than
SELECT date,open,high,low,close,tottrdqty,sma9,sma12,sma26 FROM bhav_data;
where symbol = m.THISVALUE and date =date() INTO CURSOR tempDETAIL
300,000 records takes time.
