The TOP clause is so slow because it has to pull over ALL the records that meet your WHERE conditions, and then it gives you the top 100 of those.
Fred On Wed, Jul 9, 2014 at 9:02 AM, Rafael Copquin <[email protected]> wrote: > >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/cajcbksr-j-n7t3umeov+eq-1jae9xb5nfp_ziqxb_0wcye4...@mail.gmail.com ** 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.
