Top is slow because the whole of the index (either temporary or permanent) has to be either downloaded or created on the client machine. In many cases the whole of the table. Hence normalising it and creating proper incxes to filter records using Rusmore is quicker.
Dave -----Original Message----- From: ProFox [mailto:[email protected]] On Behalf Of Rafael Copquin Sent: 09 July 2014 17:02 To: [email protected] Subject: Re: issue with dbf's >I wonder what would happen if instead of selecting * I select all fields, but >naming them one by one, like select >field1,field2...fieldN from table.... I just tested this and found the following: set order to 0 (to let Rushmore operate better) select field1,field2,field3...fieldN from table where reccno() between 200000 and 200100 works very fast In other words, with only one index key (the pk), order set to 0 and selecting a range of records (100) with the between clause insted of the top 100 I was using before, the speed is very acceptable. I am now going to develop a pagination routine that uses the BETWEEN instead of the TOP clause. That should do it But still, I wonder why the TOP clause is so slow Rafael Copquin [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[email protected] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
