Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.
Solved
pagination
Posted on 2002-07-12
Hi,
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.
Thanks.
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.
Thanks.
-
5
-
3
-
3
11 Comments
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.
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.
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.
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.
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.
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.
I understand that... and you SHOULD NOT be storing these that way. There are other ways.
Start here...
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdconcachesize.asp
Note: you do NOT store anything on the SERVER.
Check MS website, there should be a lot of other info there.
Start here...
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdconcachesize.asp
Note: you do NOT store anything on the SERVER.
Check MS website, there should be a lot of other info there.
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.
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.
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?
CJ
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?
CJ
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).
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).
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.
CJ
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.
CJ
Featured Post
Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Why WooCommerce is one of the majorly favored choices when it comes to having an eCommerce store. This article will acquaint you with some reasons that I believe make it one of the best eCommerce platforms available.
These seven tips can help you create an extraordinary website, one that captivates audiences and has them wanting to return regularly for more. Keep reading to find out what your site is missing and what you need to add!
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…
Learn how to create flexible layouts using relative units in CSS. New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
Suggested Courses
Course of the Month10 days, 8 hours left to enroll
608 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.