At 02:42 PM 29/05/2006, you wrote:

>Hi Helen,
> > Here's what the SELECT FIRST n SKIP m syntax does under the hood:
> >
> > 1. Gets the entire set specified by the WHERE clause and creates a
> > temporary set (in memory on disk, depending on size and resources)
> >
> > 2. Physically orders the temporary set according to the ORDER BY clause.
> >
> > 3. Starts outputting rows from the temporary set, skipping the first
> > m rows if SKIP is specified, until n rows have been sent
> >
> > 4. Ignores the rest of the temporary set
>Interesting, I didn't know that. I think for the tables I normally used
>FIRST/SKIP on (of course always with some ORDER BY clause) it didn't
>make a huge difference, because I never have more than a few 1000 rows
>to scroll through.

True, with such a tiny table, none of the underlying steps would be
noticeably expensive. Relatively, the cost is the same; but you're
spending less. :-)

>I was thinking about using a cursor, but in a web application that would
>mean I need to keep the cursor (and the database connection) in the
>respective user's session. I'm talking about Java here, but should be
>not much different in PHP. This is a big problem, because you can never
>know when or if a user would do his next click, it could be in 10
>seconds, it could be in 20 minutes or he/she could just decide to close
>his browser. In the case the browser was closed I would hold an open
>transaction on the database for maybe a very long time, until the
>session gets garbage collected (if ever) and I'm even not sure if that
>would really close the connection.

An abandoned connection will time out eventually (two hours is the
default socket keepalive period on Windows and Linux). But, from
what I've seen, you've got to be careful about abandoned connections
in Java, because the JDBC default is to keep a limited number of
connections open permanently and "vacate" them when they go idle,
allowing re-use. Other interfaces have techniques for managing pools
of connections also.

>So to make my story short: What would you suggest in such a situation?
>Is FIRST/SKIP appropriate or is there some magical way to keep a cursor
>without keeping the connection to the database?

No, of course not. A cursor is a contextual pointer inside a
specific output set. If the set dies, the cursor dies.

>Since web applications
>with MySQL usually make extensive use of the ROWS clause I thought it
>would be the "right tool for the job (tm)" to use FIRST/SKIP.

It's the right tool for the job in MySQL and other data storage
systems (one's hesitant to call them "databases") that depend on
physical order to do anything. It was pressure from ex-MySQL users
that got the FIRST/SKIP syntax into Firebird at all. It's not the
only such silliness that Firebird has acquired by this route.

Running a cursor inside a SP was always the way we did it and it's
still the only sensible way. It's fast and clean; you can
parameterise it at both ends, so that your application can hold and
pass previous and next key values; and so on. MySQL doesn't have
these capabilities.

I wouldn't bother with SELECT FIRST for anything. It's a waste of
space -- one of those things that (IMNSHO) people insist they need
because they don't know any better.

./heLen (because you asked!)