Re: get row count from a "cursor resultset"

Maik Wiege wrote:
> Hello!
> I'm querying my database and sometimes get a very big resultset back, so
> I needed to use a resultset with a cursor to avoid a OutOfMemoryError.
>
> Now, because I have to use a TYPE_FORWARD_ONLY resultset for that, I can
> not use the result.last() function anymore to retrieve the amount of
> rows the query returns.
>
> The query is a little bit complicated and that for can take some time,
> so I don't want to do the "SELECT COUNT(*) FROM (SELECT...)" afterward,
> what would result in doing the query twice and there for would double
> the time needed.

Have you tried using COUNT directly in your original query (not as a
separate query)? I wouldn't expect that to affect total execution time
much if at all; the main difference will be that the full query has to
complete before you get any results, while without the COUNT you might
start getting results immediately.

There's not really any other option that the current driver code will do
for you. You could DECLARE CURSOR / MOVE / FETCH yourself perhaps; I'd
expect this to have similar startup characteristics to the COUNT(*) case
though. Even if the driver supported cursor-based random-access
resultsets, it'd be using MOVE / FETCH under the covers in the same way..

> I didn't find a solution for this, but there must be, because the pgAdmin III - tool does this. After starting the
queryin the SQL-Dialog it asks wether to retrieve just the first 100 results or all results displaying the corect
amountof found rows. Or is this just not possible with jdbc. 

I'd point out that this does not require knowing exactly how many rows
there are -- only that there are more than 100. You can do the
equivalent in JDBC easily by setting fetchsize to 100 or so and calling
next() 100 times.. if you haven't hit the end of the resultset by that
point, it has more than 100 rows.

-O