Search Pagination Design (Server side pagination using Hibernate)

One of the biggest problem which is commonly faced in searches is pagination; On the first day of my new job, I was asked to looked the performance issues related in the search functionality of the application. When I looked at the application, application is slow when the users gets a search results of magnitude of 6000 records( each record had 10 fields to be displayed) and all the results were displayed in a single HTML page (Even if you had designed one search implementation, you would have guessed what the problem is).

There is no pagination concept implemented for this search/ problem, its a logical thing to say this search program is using whole lots of memory and slow. Assuming the search queries are already fine-tuned, Slowness can be mainly attributed to the HTML rendering by the browser. IE browser render the complete HTML and then displays it to the user so the slowness can be easily be found compared to Firefox 2, where the browser starts displaying the data as it receives (rather completely render it and displays it).

Given this background, Now we started seriously looking at pagination as the only option. In pagination, could think three approaches as a potential solutions

• Real time pagination on the server side.
• Criteria Caching based pagination on Server side.
• Pagination on the UI front.

Lets go in details of each one of them.

Real time pagination on the server side :

This is an approach where from the UI, always pass the search criteria plus the page start record and the end record (or size of the page). So your server implementation will have these two extra arguments along with your query and returns you only the required result set which needs to be passed to front end.

On the implementation front, you could handle this scenario at two levels

• Write a stored procedure which takes your criteria and start and end record (or Size) for the page as argument and using ‘cursors’ you can return only the required result set, for more details on oracle specific result set using cursor click here
• Say you are using ORM tools like hibernate, they make our life even easier you could try something like
  

Query q = s.createQuery( “your query….” );
q.setMaxResults(PAGE_SIZE);
q.setFirstResult(PAGE_SIZE * pageNumber);
List page = q.list();

• For more details on this hibernate approach click here

Lets looks at the pros and cons of this approach

• Its a real time pagination search, say your query is based on order by name, on the first page we show say 25 records, in the mean while say another user deletes few records (say 20 to 25th record gets deleted), now when the first user click the next button, the request goes as original criteria and retrieve the records from 26th to 50th record and we return 26 to 50 th records (because of the deletion, ideally we should have displayed from 20th to 45th records), essentially now we are not showing 5 records this is a disadvantage. For the same example, addition of records ‘could’ be an advantage for this approach.

Criteria Caching based pagination on Server side

This is an approach where all records matching to the search criteria will be fetched from the database to the the server and cached. Caching is based the search criteria and a timeout period.

From the UI point of view, request will contain the search criteria plus the start record and the size( r the end record) to be fetched.

Lets looks at the pros and cons of this approach

• This solution should fairly work for most of the search cases, unless you have a requirement for real time pagination.
• When Caching is considered, Caching algorithm plays an important role and this also has to take care of the timeout period and the memory allocated for the caching mechanism.
• Arriving on the timeout period is quite crucial as this directly impacts your performance of the search. Too low timeout (is very near to real time pagination case) will cause too many hits to your database and can cause slowness also has the drawbacks of real time pagination. Too long timeout period will have the drawback of having out-dated data on the server cache.

Pagination on the UI front

This is an approach where all the records corresponding the search criteria will be retrieved from the database and held in the controller of the UI Framework and using custom tag libraries the records could be displayed in the Jsp/ Servlet.

Here are some of the recommendations for writing your own custom pagination tab lib

• The bean will have attributes for collection of all the records, total number of pages, records per page and start record number for the current page.
• On the first display of the page, read all the corresponding records for this page and store the bean in HTTP Session.
• On the selection of the next page, previous page, etc.,; retrieve the bean from the HTTP Session and then calculate which data needs to be displayed.

Lets looks at the pros and cons of this approach

• This would be dream design approach for small volume search results (where memory is not a constraint).
• Most the cases, you do have a memory constraint, hence this may not be a feasible approach.

Written by Ravi Nallakukkala on March 30th, 2007 with 2 comments.
Read more articles on Design and Hibernate.