Paging Table Enhancements
Yesterday I wrote about using a paging table as a technique to persist the ordering of the results of a query for the purpose of returning a few rows at a time out of the entire result set. I came across this technique while working on an online phone bill system for companies having a monthly bill exceeding 5,000 8 ½” x 11” pages. For some reason they wanted the ability to page through the entire bill, even though nobody would ever really do so.
There was too much data to store it in some sort of cache on the web server. It took too long to sort 5,000 pages of data each time they wanted to return the next page. So, it was easier to perform and persist the sorting in the database. From that need the paging table emerged.
The page table had a batchID column, rowID and foreignkeyId pointing to the data where the actual data for the query was stored. By placing the foreignkeyid of the lowest level detail element in the paging table, the results could be re-constituted by joining to the necessary parent tables fulfilling the request of the original query.
Sometimes the report was a little more complicated, and required storing more than one foreign key in order to reconstitute the original data. In that scenario, I would still use the paging table. I would create a batch for all the necessary keys, placing the appropriate page/foreign key in each batch instance. Another table, or external construct such as an object in the web server cache would be used to maintain pointers to the different batches in the Paging table. Then a more sophisticated paging stored procedure could be written utilizing multiple batches that would join the necessary tables for final results.
I didn’t have to use this method often. It was useful when I might find myself using a CROSS JOIN in a query, where there was no join criteria between two tables producing final results.
This technique works well with detail type reports. It doesn’t work as well with summary type reports. Maybe you have a solution you can share with us? Get into the conversation by leaving a comment below, or sending an Email to btaylor@sswug.org to share in a future editorial.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Keeping a job offer alive
It’s usually bad news when your job offer is put on hold. Sometimes the offer disappears. So what strategies can you employ to make sure you keep that offer alive even if the employer has to suspend plans for bringing you on board?
Featured White Paper(s)
Ten Tips for Optimizing SQL Server Performance
read more)