RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Paging in J2EE: Manage Large Result Sets Efficiently : Page 4

Learn how to manage paging for unbounded queries over large result sets—without blowing up your server. Strike the right balance between resource utilization and response-time requirements to satisfy your user demands.

Current Page Number and Total Page Count
You may want to keep track of the current page number and the total page count. You can display this information to the user, as well as prevent paging past the first or last page. For query-based paging, the simplest approach is to use SQL to count up the total number of rows in the result using the SQL function COUNT(*) or COUNT(column name). You can derive the total number of pages by dividing the number of rows by the page size. You can issue the count query every time you request a page, but this may be too much. Underneath the covers, the DBMS will have to assemble your entire result set to count the results. As a compromise, issue the count query when the first page is requested. This way, the cost is incurred once per search/read request for the first page. In most databases, you will be able to append the COUNT(*) to your column list in your query for the first page. For the databases that don't allow this (like MySQL), issue a separate count query.

The first page will be slightly slower to fetch than other pages. However, your UI will be able to accurately show the total number of pages returned, as well as the current page number. If page count isn't important or the data is so volatile that the total page count isn't reliable, avoid the overhead altogether by skipping the count query.

To add page number and total page count support to your paging mechanism, extend the PageContext object to store and maintain page counts and extend the PagingDAO to issue an additional count query on receipt of a FIRST page request.

Paging in Perspective
Searching and querying over data is an integral part of most Web sites. Paging is an ideal way to manage limited server resources, large result sets, and congested UI real estate. The approach you chose, whether cache-based, query-based, or hybrid, depends on your use cases and data characteristics. Regardless, you should limit your memory and connection usage so that your paging architecture can scale efficiently to meet your performance targets.

Lara D'Abreo is an independent consultant with over 10 years experience in commercial product development in the US, Japan, and the UK. She's currently based out of Sydney, Australia and spends her time trying to make J2EE systems run faster.
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date