10th November 2006

Technology- Paged SQL Queries

The last two times I asked a question on here the result was so succesful I figure I may as well try it again.

One of the most common scenarios for querying a database is a paged result, either in a
rich-client listbox scenario, or in a web-page where it shows you page x of Y. MySql has this cool syntax
where you can do something like:

SELECT * from xxx LIMIT 500,10

Which means give me lines 500-509 of the resultset I’m asking for. So if I’m displaying a page
with 10 entries on each page and want to show page #51, I just do the above.

As far as I can tell Microsoft SQL Server has no equivalent syntax. You can do something similar to this
with a cursor, but as far as I can tell its complicated to do (at least I haven’t seen any easy boilerplate
that I can use everywhere). Plus there are all these different kinds of cursors and its hard to understand the
performance trade-offs of each.

Now to be fair, I don’t know the real performance of the MySql implementation. Presumably when you say LIMIT 500,10
the database is calculating the first 510 lines of the result set and only returning 10. But the easy way with SQL
server involves returning all 510 lines and then throwing away the first 500, which can’t be better. Plus in
theory given the knowledge you want that 51st page, MySql could be doing some cool optimizations to improve
performance.

So- anyone with advice on the best way to do this with Microsoft SQL Server? Write please… Thanks!

]]>

posted in Technology | 0 Comments