Hi, Rightnow we are facing one problems fetching rows from resultset. In our application, we have search page which comes up with more than 2000 of records but on the screen we are displaying first 10 records and then we put the paging.. we are caching resultset so for each page we are executing sql to get the result and skip the rows based on the page user selects.. Now question, is it possible that we can say fetch first 20 rows or fetch rows from 21 to 30.. we are using DB2 6.0. Thanks.
We had this problem for DB2 as well. There is a way to get certain rows, but it involves doing a nested query. It would be more efficient to just get the whole thing and skip the beginning data. Alternatively, if possible, you can store the primary key for the 2000 records and query based on that the next time.
I've tried a hybrid approach for this situation. There are now about 2500 rows in a table, displayed 25 at a time with FWD/BACK buttons. On the server side I am caching 100 rows of data. So the first time they hit the database I get the rows and pass back 1-25. When the user does a FWD I simply return 26-50. Once they go forward to page 5 I query the database again and get rows 101-200 (by ignoring the first 100 rows). It take a little extra work to keep track of the "virtual" page to "real" page mapping, but it's very straightforward. This is a good compromise for our application, and it cuts down on my database calls. There is frequent enough refresh from the database so nothing gets too stale. I allow users to sort ascending/descending on any of the 8-10 columns returned, so every time that happens I also have to refresh the data. This requirement precludes me from storing the keys, since rarely is the data returned based on the primary key.
Originally posted by Wayne L Johnson: On the server side I am caching 100 rows of data.
Do you use any logic in your query to get the first 100 rows then 100 and so on. I read many discussions in this forum and still I could not come to conclusion on this. As you said at first attempt, my query may return a resultset of 100 rows which matches the codition in the query. Lets say, out of first 200 rows in the actual table we are getting 100 rows which match the condition. So at second attempt we have to run our query from 201th row of the table. Do you use ROW NUMBER to achieve this? How you are doing this? Can you please explain? -Sainudheen
It's actually very simple. The first time I hit the database I get rows 1-100, which allows me to handle pages 1-4 w/out hitting the database again. When the user goes to page 5, then I query the database again. I throw away the first 100 rows, and then save rows 101-200, which allows me to handle pages 5-8. I don't do anything with ROW NUMBER. For our application it's a good trade-off, saving me from hitting the database as often. However when I do query the database, I use the brute-force method of reading past the rows I don't want and only saving those I do.
What if new rows are added or rows pointed by cached primary keys are removed?
It depends on the business requirement. If you get rows 1-20 using true dynamic paging and row 5 disappears, what do you get the next time? Rows 21-40 or rows 20-39? Logic would say rows 21-40, but then you mis the original row 21 because it is now row 20. Wayne's solutions sounds like a good compromise although it doesn't resolve this problem.