Re: Ultimate Question: Oracle, MSSQL, Others vs MYSQL LIMIT Statement

I have used this query extensively via dynamically created queries in MSSQL. It appears to operate about 3 times faster than populating temp tables (unless you are doing joins across linked servers, where temp tables/views appear to be better).

If you use sp_executesql to execute the dynamic queries, it also saves the execution plan, so if someone happens to execute the same (exact) query, then it will be able to reuse the plan. We have modified the query so that we can add a where clause (filter) and dynamically choose fields to sort by.

The where clause only needs to be applied to the inner most query, and you will need to have a unique row identifier to do the sort by - this was easy for us, because we use guid's for IDs which are fast enough for our purposes, but a timestamp should also be good enough.

It appears that this issue only effects people with large result sets otherwise I would suggest that this would already be in the standard! The interesting thing about returning a particular page of a table where a page has a certain pagesize is that I wouldn't have expected such a query to alter the query execution plan.

consider this:
SELECT PAGE(2,25) Col1, Col2, Col3
FROM Table
WHERE....
ORDER BY 2 how would the query plan be any different to SELECT PAGE(7,25) Col1, Col2, Col3
FROM Table
WHERE...
ORDER BY 2 as you would still have to sort the entire table (unless the order the records were returned was guaranteed without sorting, of course), and apply the where clause.

But we also need to be able to dynamically change the page and pagesize. So in MSSQL we might need:
SELECT PAGE(@PageNumber,@PageSize) Col1, Col2, Col3 FROM Table
WHERE...
ORDER BY 2 This code doesn't work; it's just a suggestion...

Surely the standards committee have considered paging?

radpin_at_hotmail.com (Jesus Christ's Evil Twin) wrote in message news:<2a0f8137.0209181144.480aa14b_at_posting.google.com>...
> In regards to SQL Server's ability to do this (ver 2000), there seems
> to be two major hurdles.
>
> This statement works just ducky:
>
> SELECT * FROM
> (SELECT TOP 10 * FROM
> (SELECT TOP 150 * FROM TEMP_ERIC ORDER BY COL1 ASC)
> AS T1 ORDER BY COL1 DESC)
> AS T2 ORDER BY COL1 ASC
>
>
> But the problem is fairly obvious. You cannot use input paramaters for
> top values, and as such either have to:
>
> a) hardwire in the top value in a procedure, which is absurd
>
> b) write an if statement allowing for every possible combination
> possible, the ability to do so being equiv to item (a)
>
> or
>
> c) pass dynamic sql after a string function tweaks things the way you
> need them.
>
> All three are poor, with c being the only way I can imagine things to
> work, and that is a horrible alternative.
>
> Anyone have any ideas for item (d)?
>
> And please refrain from making item (d) get mysql! or anything of the
> sort.
>
> :-)
Received on Mon Sep 30 2002 - 02:40:32 CDT