Here's what I want to do:
In a table I have a million rows. I want to show with a JSP a listing of two columns of this table. The listing will show a configurable number of rows at a time. I am using a session EJB to fetch the rows from an Oracle DB. but without using a paging pattern, the server crashes if a million rows are fetched to the client.
Can somebody come up with a pattern/code to help me out with this?
Thanks a lot,
I notice a similar problem. I've got thousands of records (200.000 rec).
I have implemented successfully the Page by page design pattern. This pattern apply between the client side and the ejb side... So I've still one problem.... Using CMP, I load all records from DB to EJB Container with a findAll() method. I still haven't found a way to reduce this initial overhead to loading all record.
Once the findAll method has finish, all is done very quickly (using Page by Page design pattern)... But how can I improve this initial time to recovery all record from DB to EJB container?
Is the only way to use BMP and deferred the records load?
I think it is a great problem when speaking about thousands of records... HOW TO MANAGE IN EJB????
I faced some time ago the same problem yu are telling about, the difference was that i was'n developing ejb. However it is possible that my sollution can help you especially if you are using oracle because you can use the "rownum" variable. I made something like:
1: defined the maximum number of rows I want to get, something like MAXROWNUMBER = 20
3: supposing that you have certain fields used for sorting purposes, like the name (as I had, indexed in the database), and that you list the record ordered by names, create 2 variables that should store the first and the last name in the page, to use them as reference for going forward/backwards, for example theFirstNameInList and theLastNameInList.
3: I made the select querry using my initial conditions, but adding to the querry the next:
"and rownum <= " + MAXROWNUMBER
4: then, depending on the direction you want to navigate, you should add to the querry the next condition:
"and (name > \'" + theLastNameInList + "\'" +
"or (name = \'" + theLastNameInList + "\'"
<p>The equals condition is for keeping the last name in the previos page as the first name in the next page, this is sometimes useful for client's orientation.
This is the basic condition for navigating page by page, of course there are some more concerns, such as table changes during navigation and caching some data for speed puspose, the code I wrote is a little bigger, however I hope this should be helpful for a start. Actually the code I wrote for this matter is a little bit larger, if you need more explicit details pls. tell me.
Hope This Helps,
I did think of using the 'rownum' keyword (as I'm using Oracle). But I ran into trouble with the 'previous page' query. If I used the pattern u suggest, then it is always the first 10 rows which will get fetched if I click 'Previous' (assuming a page size of 10).
This is because Oracle will perform the filtering of records with rownum before using the order by clause.
hi, if you still think that the pattern using Oracle's rownum will help, please let me know, I will explain more detailed how i did. I mention that this pattern works fine with a millions rows database. The downside of it is that is not portable across databases, but however, it is a solution.
hi Dikran, I would like to see some pseudo/real code before I believe it!
"hi Dikran, I would like to see some pseudo/real code before I believe it! "
something like this:
create table t(
create view t1 as
select ROW_NUMBER as NUM, name from t;
select name from t1 where NUM between page*size and (page+1)*size;
You may do the same type of thing with ANY SQL database if your table t has a primary key.
Here's an idea which borrows from CPU cache lines and memory requests vs. locality.
First off, you'll need a reference object to persist where you are in the result set (assuming that you're using a disconnected client like a web browsers). For instance, an offset, a page size (20 records) and the original query term or phrase. Ideally, each Request contains all the information to perform the request cold.
In the EJB, each request will result in one of two actions. Reading from the database or reading from the cache. The first option, reading from the database is all too familiar, however, instead of returning all results; just return the first 100 results. Out of the EJB you return the amount determined by the page size starting at the offset.
On the next request, you're now working in cache and the offset is now changed to offset + pagesize. Instead of a RT to the database, you simply pull from the cache and return to the results to the client.
On a cache miss, another query to the database is needed. So its not optimal for truly random access, however, most page requests usually come within a very small locality. So if the client is truly erratic, you'll end up slightly erratic behavior. You could also help the user and the computer out by providing large jump actions…skip to the 100 or something along those lines. Essentially preventing erratic behavior due to poor GUI design.
Another small caveat that I ran into with the approach is the notion of total number of results. People love to see how much work they made the computer work. However, because you're moving from cache to cache, you never evaluate the entire result set and never know how many records you have. Some remedies to this are to provide statistics or metadata that are updated on some interval or give an estimate based on some other metric. Either way, that particular data point comes from another source, not the result set you're paging through.
Hi,Where can I find the "Page by Page design pattern" in detail.
I hope it isn't too late.
Here you'll see all j2ee pattern used in Java Pet Store demo