Use a cursor. MySQL does it with:
SELECT name FROM customers LIMIT 5, 10
Yes, it's optimized.
http://www.mysql.com/doc/L/I/LIMIT_optimisation.html
All databases can do that, maybe differently.
--Renaud
----- Original Message -----
From: "John M. Corro" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, November 30, 2001 8:43 AM
Subject: Iterate Design Question
Consider a situation where you want to display a straght listing of items
from a database. Something like where you would display a listing of 1-20
products on page 1, 21-40 on page 2, etc. There seem to be two ways this
gets implemented.
1. Pull the entire table contents, store them into a container of sorts,
refer to the container's listing from page to page (instead of hitting the
database again.
2. Query the DB for only the contents you need on each request. So user
request page 1, backend queries the the DB for products 1 - 20, returns it.
If the user requests for page 2, the backend does the same process over
again for products 21-40 and so on.
My specific situation prevents me from doing the first approach because the
result set is so large (takes too long to make that initial DB call) and
the listing changes so often that it's possible a user may be view an
inaccurate listing.
In going forward w/ approach 2, I've been having some issues I'd like to
bounce off anyone w/ experience in the matter.
- Thought about using the PreparedStatement.setMaxRows() method, but if I do
something like "SELECT ... FROM Products WHERE...", I don't know if the
statement is smart enough to optimize itself to only retrieve 20 products.
The docs say that "the excess rows are silently dropped", but I don't know
if "silently" means it pulls all the records and only returns 20. If that
is the case that would seem like I wouldn't get any performance gains (the
DB still has to go through the process of retrieving all the records). I
imagine that what "silently" means would be dictated by the driver.
- I also thought about using database specific SQL statements to cap the
size of the result set (ie the 'Limit' keyword in MySQL). This would be my
least desired step as I don't want to get tied to a specific database. I'm
not aware of any ANSI SQL commands that will do this.
Anyone have any thoughts?
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
