Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17



Posted on 2002-07-12
Medium Priority
Last Modified: 2008-02-01

I am interested in finding out what would be the best way to deal with pagination in a web application conecting to a data base and performin a query against the db.
Once the query is performed, I have a ResultSet.
I need to show the rows of the result set to the user, using pagination.
I need to show the user how many pages I found, present the first page and allow the user to chose which page to see.
I know how to do this, and allready did it, creating my own CachedResultSet, that I persist in the session, and connecting it to a table object that knows how to paginate.
What I need to know is what are the proved best ways to deal with this type of problem from the design point of view.
If I have this CachedResultSet, I can run into problems if a lot of users are hitting my application server at the same time, and I have too many such objects stored in memory. I could reduce the size of each CachedResultSet, by setting a max for each of them, and presenting to the user a message that the result set was too big and say only 100 rows are presented in 10 pages, each with 10 rows, and that the user should refine the search to get less results.
This way I minimize the size of each CachedResultSet, but stil can't controll how many such objects will be at once in memory. On the other hand, I did not see this type of design on the major sites, like Google, and others, so it might not be the best ideea.
I can use one of MySQL features, that limits the number of rows, and allowd what range to find. However this seems to have not been optimized, so it might be slow.
I can have a primary key that I use saying "Select * from whatever where id <= something and id >=something.

However, I run into problems when I delete rows.
The deleted row will dissapear from the table, and I lose the sequence of the id. I could have all the remaining rows renumbered each and every time I delete a row, but this also seems a bit weird to me to do.

Anyway, maibe there are allready better ways of doing it out there.
I am looking for an answer.

However, I am looking for an answer that will be at the design level, and not some implementations, that I have allready seen on the net, none of them really responding to the design questoin.
Question by:simi
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
LVL 19

Expert Comment

ID: 7151749
You don't put ANY recordset in the cache, nor in a session variable.

As for pages -- decide how many records per page, divide the total records found by that number, and that gives you the pages.

There are ways to set pointers/cache records. I'm sure somebody else will be able to tell you how to do that, since I don't do that enough to have the code at my fingertips.

Author Comment

ID: 7151779
I dit not say I put the record set, or result set, in the session.
I read all the data from the ResultSet into my own object, that I called a CachedResultSet. Then I close the ResultSet and the connection to the db.
Now I have my own object, containing data.
This was not the question.
I do realize I have to divice the number of rows to have the pages too. Again, if you read my question, I did not ask that.

LVL 19

Expert Comment

ID: 7152227
>>creating my own CachedResultSet, that I persist in the session, >>

Persist how?
Certified OpenStack Administrator Course

We just refreshed our COA course based on the Newton exam.  With 14 labs, this course goes over the different OpenStack services that are part of the certification: Dashboard, Identity Service, Image Service, Networking, Compute, Object Storage, Block Storage, and Orchestration.


Author Comment

ID: 7152243
As a session variable.

Webwoman, I think the question is pretty clear.
You seem to have a lot of problems understanding what I mean. Read the question, it states clearly that the so called CachedResultSet is an object, that I store in the memory and that I am concerned about too many of them loading up the memory.
LVL 19

Expert Comment

ID: 7152524
I understand that... and you SHOULD NOT be storing these that way. There are other ways.

Start here...
Note: you do NOT store anything on the SERVER.

Check MS website, there should be a lot of other info there.

Author Comment

ID: 7153279
I really don't get you.
First of all my question is a generic question, you keep pointing me to technologies that I don't even use.
I write my program in java using servlets.
Now you point me to read about the CacheSize of an Active X object that I do not intend to use.
If I ask a question I espect to find an answer to it, not some sentences that are hardly related to my question or something pointing to somewhere, like you did not, that is too generic to be considered an answer.
Do you really espect me to assign you the points for your comments ?
Please disregard my questions from now. Thank you.
LVL 19

Expert Comment

ID: 7154899
simi: Please try to stay civil.

Now regarding your question: The query or queries you are trying to cache are they standard (I mean all users see the same results or are they custom for each user)

Also, How big of a resultset are you talking about?  What kind of Memory limitations do you have on your server?


Author Comment

ID: 7155360
HI Ch,

Don't understand the meaning of "civil".
However, I belive I guess what you mean. I am on this site for years, and I got intouch with several people. From thowe, two, one of them being webwoman, proved to be not helpfull, arogant, and I thing "not civil".
I have responded them accordingly, however without being "not civil", meaning I was ferm and polite, but I told them what I think.

Regarding the problem.
The queries are allways different. It is something that allows users to search something in a db.
Each user can search for something. As a result I get a ResultSet.
I save this ResultSet in an object of my own named a CachedResultSet. This CachedResultSet is saved in as a Session variable, to allow the user to find it the next time it hits the server, and that is happening when the user wants to see the next(or any), page in the set of data saved in the CachedResultSet.
This is how I solve the pagination.
I was asking, if anyone knows of something that is allready an established procedure, that culd be alike, or different from what I am doing.
I guess it all deppends on how many users are hitting the server at a time, how big the db is and so on, resulting in how much memory is going to be ocupied on the server by a number of such CaachedResultSet instances, each of them for a user.
I do not know that, now. At the beginning there will be less users and less data in the db. Hopefully later it is going to be more.
My own judgement is that at the beginning this kind of approach can be OK, but then I have to find a different solution, a trade off Speed(Memory) versus serving a lot of users.
I was hoping to get confirmation of my judgement, or some new ideeas, as well as some information that will allow me to decide at what point do I have to apply a different stragegy(Eg. if you have so many users and the data ocupies so much, from this kind of available memory, the you should consider a different approach).

LVL 19

Accepted Solution

cheekycj earned 400 total points
ID: 7169502
Well, there is NO defacto standard that I know of.  Everyone implements whatever works for them.  In Memory Cache is usually the fastest and best way to go about it (like using session).

We cache queries in memory (not using session)  some application servers provide you the ability to cache queries otherwise you have to write your own (as you did)

I think your approach is fine.  Remember Memory is cheap now. and Memory does give you faster response times than caching on the file system will.  I would stick with your approach until it becomes un-manageable.  Then you can implement an expiration technique like LRU approach to help manage the memory better.


Author Comment

ID: 7169593
Sounds like what I had in mind too.
LVL 19

Expert Comment

ID: 7169596
Glad I could help and Thanx for the "A"


Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article was originally published on Monitis Blog, you can check it here . Today it’s fairly well known that high-performing websites and applications bring in more visitors, higher SEO, and ultimately more sales. By the same token, downtime…
When the s#!t hits the fan, you don’t have time to look up who’s on call, draft emails, call collaborators, or send text messages. An instant chat window is definitely the way to go, especially one like HipChat. HipChat is a true business app. An…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Suggested Courses

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question