I am currently using JDBC 1.0. My project involved a large capacity of database. I want to retrieve my record in a large table and show it on the screen. Since my monitor is small, it is good only to retrieve certain amount of result once on the screen. Let's say 20 records per screen. But then I faced another problem. Does this mean I have to retrieve the whole table for 20 records every time? I think it is better to retrieve 20 records every time. Can it be done using JDBC 1.0, or maybe JDBC 2.0 ResultSet?
I think it is possible to partition the ADO record set in to several chunks depending on the number of records to appear in one page. But as far as JDBC is concerned, the best optimization which is possible is to retrieve an incremental number of records as the page number increases.
For example if 20 records are to be displayed in 5 pages @ 4 records per page, then only 4 records are to be retrieved for the first page which can be achieved by set rowcount 4. Generalizing it, the row count should be set to 4 * pageno.
(set rowcount 4 * pageno) before executing the select sql statement and again the row count set to zero immediately to disable row count for further db access. I have seen that this gives a significant increase in performance as the probability of the users seeing the first page is much more than that of other pages in that order.
Here on TheServerSide we don't get the whole table to show you lists of threads in forums, we only get a few at a time. In particular, click on one of the forums, and look at your browsers status bar, you should see the following:
These parameters propagate all the way down to the EJBFinder level, where we dynamically modify the sql statement to return different rows from the database.
What if you want to display the total result set count on the first page? For example, it might need to say "Page 1 of 10". In the solution provided in this thread, the presentation layer is never given this information. To do this, do we need to make an extra query to get the total count? It's a shame to have to do essentially the same query twice. Is there a faster way?
Well if you are working from an EJB perspective, then you could just maintain the current count of whatever ejb you are interested in as a member variable of that EJB, then when you return its details object you just query that member variable. That is how we acheived it on TheServerSide.com
The easiest / fastest way that I've found to do "data windowing" is to use a correlated sub-query in SQL. This way your ejbFind.. methods just need two extra pieces of data: the current sorted field, and the last data value for that field.
With this information, and the size of your window, you can write a query like:
select * from my_table t where
t.theColumn >= lastDataValue
and (select count(*) from my_table tt where
tt.theColumn >= lastDataValue
and tt.theColumn >= t.theColumn) < windowSize
This allows your query optimizer to do the work for you.. no fuss. There's a paper by Joe Weinstein on BEA's developer website "Tips from the Experts" on this and other JDBC performance tips.