>I have a 1 million row table with a primary key of RECNO(). I need to pull random sets of 100 contiguous rows from the table. I have tried 3 things:
>
>1) SELECT * FROM table WHERE PriKey>=N AND PriKey>2) SEEK followed by COPY TO ARRAY
>3) SEEK followed by SCAN
>
>All three get a throughput of about 10 requests per second. This seems slow to me. Would anyone disagree? Can you suggest a better way to do the requests?
>
>Especially in the SCAN case I am puzzled. It would seem that all FoxPro has to do is read the records sequentially (I tried using SET ORDER TO 0 and NOOPTIMIZE), yet my disk's average transfer rate is 5 times what I am seeing and my CPU is at 15%.
>
>Also, in the SELECT case, I am collecting statistics and I see some requests taking 10 milliseconds and others taking 450 milliseconds. Based on the ranges requested, it does not look like buffering is causing the speedy ones so could disk fragmentation be causing the slow ones?

A couple of thoughts.

I don't think it's a good idea to use RECNO() as a key value since these values could change. For Rushmore to optimize, the WHERE cluse expression has to match the index expression exactly. I don't think this would be possible with an index expression on RECNO(). You might want to change this key to something more amenable.

If the desired records are contiguous, I think a SEEK followed by a DO WHILE would be fast.

Bill

William A. Caton III
Software Engineer
MAXIMUS
Atlanta, Ga.