CR Linefeed

Monday, December 06, 2004

Google-style stateless cursors

In the good old days when I spent my time cranking out PL/SQL, I spent lots of time creating cursors, since these are the best way for PL/SQL to interact with SQL. So, you define a cursor, open it and step through the rows that get returned, usually doing stuff with each row. End of story.

Fast forward to the present day and I am implementing search functionality in a stateless architecture. So you can't open a cursor and step through all of the rows. You have to come up with something like Google, where you get displayed results a page at a time. So, you can open a cursor and pull back a page of results, but when you want the next page obviously the cursor you had has gone. So you have to open a new one and move through it until you get to page 2.

This seems like something the world and his dog must have done, but I can't find much discussion of the topic and any techniques for doing it efficiently. Maybe I'm just not looking hard enough?

3 Comments:

  • forgive me if this is too trivial (just stumbled on the post from Artima), but in MySQL land the traditional solution is to use a limit with an offset.

    offset = page * 50
    select * from MyTable where #{offset}, 50

    will return you results 151-200 where page = 3

    one of those doesn't work in theory, but works great in practice solutions

    (I believe the equivalent syntax in Postgres is: ... limit 50 offset 150)

    By Blogger kellan(at)protest.net, at 4:37 am  

  • No, that's not trivial. But I suspect that under the covers it is just doing what I said (opening a cursor and stepping through to the relevant rows). So I suspect that if you have 10000 results it'll be rather slow to display the final page if you've got 10 results per page.

    This all seems rather inefficient and inelegant. Maybe I should just get over it and move on?

    By Blogger Chris, at 12:11 pm  

  • There's a good discussion of this (in the Oracle environment) here

    By Blogger Chris, at 12:55 pm  

Post a Comment

<< Home