General information
Category:
Coding, syntax & commands
Title:
Performance suggestions for a large table
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 PriKey2) 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 the result set size 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?
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only