Posted on 2002-07-12
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?
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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 100 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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Learn by example how to specify CSS selectors for Selenium WebDriver test automation software.
"In order to have an organized way for empathy mapping, we rely on a psychological model and trying to model it in a simple way, so we will split the board to three section for each persona and a scenario and try to see what those personas would Do,…
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

763 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