what is the best practice for presenting large datasets to users in a web application, say a 'product search'? Do you obtain the entire dataset at once and then show 'x' at time? Do you perform queries that only obtain 'x-sized' chunks of the dataset? ??
Yeah agree with Bear Bibeault ! Second approach is the only correct approach !
If you have let's say 100,000 rows or 1 million rows and you are only showing lets say 25 rows at a time, you probably will end up overkilling your application by fetching all the data. Just caching all rows on middle tier wont make sense if your data is real time one. Once the data is send back to the client client may want to see next 25 records or provide another filter criteria to reduce the rows retrieved or whatever. Plus with web apps you will never know, how long it will take client to decide to click next. He may he may not. How long will you keep cached data on middle tier ? What happens when some other users modifies the data in the DB ? You will have stale copy of data. Now add 100 users simulteneously quering same data... # of copies on middle tier will increase linearly.
Bear in his message told the way you can do it in POSTgreSQL. In MS Sql server I think there's a TOP N clause. In oracle you can try using Analytical functions rank () etc. or.. other way is to use inline views..
SELECT * FROM ( SELECT rownum r, a.* r FROM ( <YOUR SQL Statement Here Including WHERE GROUP ORDER BY> A WHERE rownum < :toRow) WHERE r > :fromRow [ November 08, 2004: Message edited by: nilesh Katakkar ]