Re: Help - Simple SQL query doing full table scans

> The idea of returning a fistfull of records, when Users are only
interested
> in the first x-dozen of them, sounds like distinctly dodgy application
> design to me. In any case, if you want the majority of the rows returned
in
> a sorted order, that's what an order by clause is for. It's a misuse of
an
> index otherwise, involving excess I/O that's totally not required.

It doesn't matter if the I/O is excess as long as it doesn't cause a problem.

The point about using an index to fetch all the required records is that the work is done as the records are fetched ie a few milliseconds delay every time the page down key is pressed, compared with a wait of (potentially) seconds before even the first record is shown if the data is sorted - all records are fetched before the sort can occur. A total execution time of 10 seconds where there are 100 0.1 second pauses as the user pages down is better than a total execution time of 3 seconds where there is a 3 second delay before any data is shown.

The point is that using an index to do a sort means you get the first page of data back immediately e.g. show me all outstanding work requests in order of date requested to select items to work on. The odds are that the user won't actually fetch all the available records. Fetching them to sort is "excess I/O that's totally not required." Received on Thu Jan 10 2002 - 15:47:42 CST