db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernt M. Johnsen" <Bernt.John...@Sun.COM>
Subject Re: Finding length of a result set
Date Tue, 31 Jan 2006 08:56:34 GMT
I would suggest you use the SQL COUNT() function (in the same transaction).


stat = con.prepareQuery("select * from myView where username='foo'")
count_stat = con.prepareQuery("select count(*) from myView where username='foo'");


ResultSet count_res = stat2.executeQuery();
count_res.next(); // count will give one and only one row
int count = count_res.getInt(1);

ResultSet res = stat.executeQuery();
.... and off you go!

>>>>>>>>>>>> John English wrote (2006-01-31 07:41:16):
> 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:je@brighton.ac.uk
>  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)
> ----------------------------------------------------------------------

Bernt Marius Johnsen, Database Technology Group, 
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway

View raw message