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.
Course Of The Month
9 days, 19 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
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.
[X]
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
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.
Active 4 days ago
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).
Active 4 days ago
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
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!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Color can increase conversions, create feelings of warmth or even incite people to get behind a cause. If you want your website to really impact site visitors, then it is vital to consider the impact color has on them.
CTAs encourage people to do something specific to show interest in your company, product or service. Keep reading to learn why CTAs should always be thought of as extremely important, albeit small, sections of websites.
The viewer will learn how to count occurrences of each item in an array.
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month9 days, 19 hours left to enroll
623 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.