Solved

pagination

Posted on 2002-07-12
11
336 Views
Last Modified: 2008-02-01
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.
0
Comment
Question by:simi
  • 5
  • 3
  • 3
11 Comments
 
LVL 19

Expert Comment

by:webwoman
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.
0
 

Author Comment

by:simi
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.

0
 
LVL 19

Expert Comment

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

Persist how?
0
 

Author Comment

by:simi
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.
0
 
LVL 19

Expert Comment

by:webwoman
ID: 7152524
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.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:simi
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.
0
 
LVL 19

Expert Comment

by:cheekycj
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?

CJ
0
 

Author Comment

by:simi
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).


0
 
LVL 19

Accepted Solution

by:
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.

CJ
0
 

Author Comment

by:simi
ID: 7169593
Thanks.
Sounds like what I had in mind too.
0
 
LVL 19

Expert Comment

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

CJ
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Uploading files to the web server has become common part of almost any kind of web application. People use different technologies to solve this, but regardless of the technology used, it is always useful to have some kind of progress indicator shown…
Read about why website design really matters in today's demanding market.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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.

762 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now