Moises

I have seen 2 possible explanations using tables approaching 6.7 million records


1) experiment with deleted
if you do not have an index on deleted(), try adding one and test
if you don't want to do that, set deleted off in your form (scoped to your datasession) then run your test.

trace your code after executing these lines to check your optimization level

LOCAL jrush
=SYS(3054,11,"jrush")
SELECT * FROM system INTO CURSOR tsystem
? jrush

2) physical sorting of your table and rushmore caching when possible each request (only reboot will clear)
I am not sure if it is possible for you to do this or not...

if you are searching on the most commonly used index in your file, sort your table according to this index and test

Example, retrieve balance for 10,000 utility customer from transaction file of 6.5 million records.
Before sort:

scan
open a view
endscan

this would run for about 700 to 800 customers depending on memory then slow down mysteriously...
I would halt the scan, then run the test again.
It would run fast through any executed queries in first run, then slow on new queries.
A reboot would cause the caching to disappear and back to 700 or 800 on the first scan

After sort:

scan
open a view
endscan

runs like a fox!
very quick times all the way through

Reference, see page 5 on this document:
http://my.advisor.com/doc/01663?open&p=1&pid=ztdbms

the graph displayed is not a smoking mirror.

Hope this helps,

Stacy

>I have a very large table over 300,000 records
>
>I am using locate for command
>I can not use seek because I have conditions seek does not fulfill
>
>The first time a use the command locate,it is very slow
>
>The second time and then, locate is very fast
>
>The command is in a button of a form and table in date environment
>If I close the form, the first time I use locate it is slow again
>
>Is there any explanation for it ?