db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Craig L Russell <Craig.Russ...@Sun.COM>
Subject Re: Finding length of a result set
Date Tue, 31 Jan 2006 20:58:03 GMT
Hi John,

This is a common "problem" for which there is no good, satisfying  
solution.

If you want an exact answer, you have to run the query to count the  
results. So it's no surprise to me that running COUNT() with the  
query takes as long as running the query that returns the results you  
are interested in.

For a complex query, you do all that work on the back end and all you  
get is a number. And depending on your isolation level, the result is  
not exact anyway. That is, in read committed mode, if another  
transaction changes some data that is used by your query, running the  
same query twice might return different answers, even in the same  
transaction. And since in your application there is user think time  
involved, you are probably not even running the queries for  
subsequent pages in the same transaction. So there really is no  
rationale for an exact number.

So most of the applications I've seen that present the user with a  
screen full of data at a time forego the N in "Now displaying page 1  
of N". If you have some idea at the application level about the  
cardinality of the result you might consider giving the user a hint  
about how many results are expected. But an exact number isn't  
technically feasible as far as I know. (Even Google returns "Results  
1 - 10 of about 7,010,000 (0.46 seconds)"; this is not based on the  
back end actually counting anything but using some application  
heuristic).

The two standard object-persistence APIs, JDO and EJB3, both offer  
direct support for this kind of paging application. They allow you to  
say "skip the first 40 results and give me the next 20 results". And  
neither of these APIs allows you to get the complete count of  
results. There's no standard SQL to do what you want. Might be an  
indication that it's not a straightforward exercise.

Craig

On Jan 30, 2006, at 11:41 PM, John English wrote:

> 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)
> ----------------------------------------------------------------------

Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!


Mime
View raw message