Database Paging
Im trying to page data that comes back from a cursor on the db (AS400) - but its a generic problem on all databases I think. The reason for needing the database to handle the paging is that the complete resultant dataset is too large to cache, multiple by users and youd crash server (because each one has individual results). Ive come up with two working models, but both leave that bad taste in your mouth that let you know its not right. If you have ideas keep in mind that the user can set the rows per page, jump into the application at page X (it remembers where they were last at), and sort (acs and desc) on any column in the cursor returned (too large of a number to think indexing each one would be efficient).
The first method I have is the equivelant of the SQL Sever of (this is psuedo, not actual SQL):
--For clarification
--@RowsPerPage = 2 (number or rows per page)
--@Total = @RowsPerPage * 5 (5 is the requested page number)
--@SortDirection = ASC
--@OppositeSortDirection = DESC
--@ColumnX is whatever column the user is sorting by.
--End clarification
SELECT TOP @RowsPerPage * FROM (
SELECT TOP @RowsPerPage * FROM (
SELECT TOP @Total * FROM SomeTable SORT BY @ColumnX @SortDirection) AS TEMP1
) AS TEMP2 SORT BY @ColumnX @OppositeSortDirection)
SORT BY ColumnX @SortDirection
On the following test set of data:
ID-Name
1 - Bob
2 - Joe
3 - Tom
4 - Earl
5 - Jack
6 - John
7 - Mark
8 - Matthew
9 - Luke
10 - David
11 - Mike
12 - Dan
If we want rows 9 and 10 we pass in page 5 as the page we want to navigate to and 2 as the rows per page, this causes the inner most select top to get the first 10 (2*5 = 10). Now we reverse sort that so that we now have:
10
9
8
7
6
5
4
3
2
1
We select the top 2 to get:
10
9
And then reverse sort that to get:
9
10
Which is the equivelant of page 5.
It works but WHAT A MESS!!!!!
Not to mention if you run this in query analyzer your using 48% of your processing in the two outer sorts...not good. Also the further down the page - the more data that has to be sifted through - for example in this we immediately threw out 8 of the 10 rows returned (80%).
Now it was suggested that I sort by the input column with a secondary sort on the primary key (numeric). Then whenever I need the next page I just do the save sort but include in my where clause where id > @lastID - where last id equals the primary key on the last row of the previous returned cursor. Then I would do the opposite for the getting the previous page. Problem is that the user doesnt do next and previous only. As stated to earlier they can jump from page 3 to page 18. This means I would initially have to return all the records, get the end points of each page (the first row primary key and the last row primary key), and carry those around me through the application. This will cause a lot of extra logic in the stored procedure, plus I carring around a bunch if information in Session in order to navigate more than one page at a time - which Im trying to avoid overloading session with a bunch of information, and while this may be significantly less than carrying around the whole dataset, its still a lot of information, and some coding that leaves me with the same pit in my stomach as the contrived crap I had above.
Im not an ANSI SQL expert to begin with, and the particulars of AS400 with its FETCH statement and working directly with cursor only makes it more difficult for me to come up with an elegant solution...
If any one has a good solution, please pass it on. Again, storing the entire dataset in session or cache isnt an option because of size.
Im trying to page data that comes back from a cursor on the db (AS400) - but its a generic problem on all databases I think. The reason for needing the database to handle the paging is that the complete resultant dataset is too large to cache, multiple by users and youd crash server (because each one has individual results). Ive come up with two working models, but both leave that bad taste in your mouth that let you know its not right. If you have ideas keep in mind that the user can set the rows per page, jump into the application at page X (it remembers where they were last at), and sort (acs and desc) on any column in the cursor returned (too large of a number to think indexing each one would be efficient).
The first method I have is the equivelant of the SQL Sever of (this is psuedo, not actual SQL):
--For clarification
--@RowsPerPage = 2 (number or rows per page)
--@Total = @RowsPerPage * 5 (5 is the requested page number)
--@SortDirection = ASC
--@OppositeSortDirection = DESC
--@ColumnX is whatever column the user is sorting by.
--End clarification
SELECT TOP @RowsPerPage * FROM (
SELECT TOP @RowsPerPage * FROM (
SELECT TOP @Total * FROM SomeTable SORT BY @ColumnX @SortDirection) AS TEMP1
) AS TEMP2 SORT BY @ColumnX @OppositeSortDirection)
SORT BY ColumnX @SortDirection
On the following test set of data:
ID-Name
1 - Bob
2 - Joe
3 - Tom
4 - Earl
5 - Jack
6 - John
7 - Mark
8 - Matthew
9 - Luke
10 - David
11 - Mike
12 - Dan
If we want rows 9 and 10 we pass in page 5 as the page we want to navigate to and 2 as the rows per page, this causes the inner most select top to get the first 10 (2*5 = 10). Now we reverse sort that so that we now have:
10
9
8
7
6
5
4
3
2
1
We select the top 2 to get:
10
9
And then reverse sort that to get:
9
10
Which is the equivelant of page 5.
It works but WHAT A MESS!!!!!
Not to mention if you run this in query analyzer your using 48% of your processing in the two outer sorts...not good. Also the further down the page - the more data that has to be sifted through - for example in this we immediately threw out 8 of the 10 rows returned (80%).Now it was suggested that I sort by the input column with a secondary sort on the primary key (numeric). Then whenever I need the next page I just do the save sort but include in my where clause where id > @lastID - where last id equals the primary key on the last row of the previous returned cursor. Then I would do the opposite for the getting the previous page. Problem is that the user doesnt do next and previous only. As stated to earlier they can jump from page 3 to page 18. This means I would initially have to return all the records, get the end points of each page (the first row primary key and the last row primary key), and carry those around me through the application. This will cause a lot of extra logic in the stored procedure, plus I carring around a bunch if information in Session in order to navigate more than one page at a time - which Im trying to avoid overloading session with a bunch of information, and while this may be significantly less than carrying around the whole dataset, its still a lot of information, and some coding that leaves me with the same pit in my stomach as the contrived crap I had above.
Im not an ANSI SQL expert to begin with, and the particulars of AS400 with its FETCH statement and working directly with cursor only makes it more difficult for me to come up with an elegant solution...
If any one has a good solution, please pass it on. Again, storing the entire dataset in session or cache isnt an option because of size.
Last edited by a moderator: