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 Wed, 01 Feb 2006 08:25:59 GMT
I apologize for this mail. At last I have learned (once again) to read
the whole mail before I answr. At least Craig sent a decent
replay. Thanks Craig. Sorry John.

>>>>>>>>>>>> Bernt M. Johnsen wrote (2006-01-31 09:56:34):
> I would suggest you use the SQL COUNT() function (in the same transaction).
> 
> E.g.
> 
> 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);
> count_res.close();
> 
> 
> 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



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

Mime
View raw message