At 17:56 14/06/2006, you wrote:
I'm finding that ORDER BY is surprisingly slow, and it makes me
wonder if I'm doing something wrong. Here's the situation:
I need to select a large set of records out of a table, sort them by
one column, and then get just a subset of the sorted list. (For
example, I might want records 40-60 ordered by date, which is a
completely different set than records 40-60 ordered by user ID.) I
start with the full list of record IDs I want, and a query something like this:
So you want the records from position 40 to position 60 ?
SELECT <fields> FROM <table> WHERE recID IN (<record IDs> ORDER BY dateFld
I have a unique index on recID, and an index on dateFld.
When my record IDs list is about 13000 items, the ORDER BY takes
about 10 seconds (i.e., the query takes 10 seconds longer than the
same query without the ORDER BY clause). Yet if I remove the ORDER
BY, grab all the dateFld values into my own array, and sort it
myself, the sort takes about 2 seconds.
In DB2 was faster select the 0-59 (1 to 60) records from the date
ordered table and from that selection in inverse order, select the
0-19 (1-20). It was faster because selecting records from 0 (1) to
N-1 (N) was a lot faster than using the select records M-1(M) to
N-1(N), even when used two times.
HTH
------------------------------------------------------------
Alien.org contacted... waiting for Ripley
