I have a method in a J2EE application which displays the result of a query
in a standardised tabular format. The output is presented in pages of 20
results at a time, so I need to know how many rows there are to find the
number of pages, and to seek to the start of the correct page.
This all works fine, but I have a table with about 13000 rows in it which
takes about 40 seconds to complete. I have encapsulated the query in a
view, and I then select from this according to various user-supplied
criteria (sort order and result filtering). I use the following code to
get the number of rows:
ResultSet res = stat.executeQuery();
res.last();
int n = res.getRow(); // no. of rows in result
res.beforeFirst();
and I then use res.absolute(p) to get to the first row of the current page.
The problem is that this seems to take as long as it would take to display
all rows of the result. I've also tried using "select count(*) from myView"
but this also takes as long as it would take to process the entire set of
rows. That is, the view produces a set of 13000 rows and "count(*)" then
seems to need to read the entire set of rows to find out how big it is.
The time it takes drops if the query is filtered, e.g. "select * from myView
where username='foo'". A query like this might only result in only 100 rows
being selected, in which case the time it takes drops accordingly.
All I need is 20 rows from the result, and the total number of rows, but
it seems that I have to process all 13000 rows just to find out how many
there are. Can anyone suggest a better way to find the number of rows in
the result other that the ways I've tried? MySQL has a function to tell
you the size of a result set directly, but I can't find any JDBC equivalent.
TIA,
----------------------------------------------------------------------
John English | mailto:[EMAIL PROTECTED]
Senior Lecturer | http://www.it.bton.ac.uk/staff/je
School of Computing & MIS | "Those who don't know their history
University of Brighton | are condemned to relive it" (Santayana)
----------------------------------------------------------------------
