MySQL ResultSet - count rows? : JDBC JAVA

Hello,

I am writing the data of a ResultSet object to an array. To declare
the array length, I need to know the number of rows before copying
them - is there a way to read the row count instantly, without having
to iterate anything?

PHP has mysql_count_rows, but I'm not sure what that does internally -
it may just iterate over the data too.

--
Chris

Christoph <christoph.burschka@gmail.com> writes:

> Hello,
>
> I am writing the data of a ResultSet object to an array. To declare
> the array length, I need to know the number of rows before copying
> them - is there a way to read the row count instantly, without having
> to iterate anything?

No. You could execute 'SELECT COUNT(*) ...' first but then you
essentially traverse all the rows twice.

(There could be non-standard MySQL extensions that lets you do this, I
don't know)

>
> PHP has mysql_count_rows, but I'm not sure what that does internally -
> it may just iterate over the data too.

--
dt

Questions about Derby/Java DB? Please visit
http://db.apache.org/derby/derby_mail.html

Christoph wrote:

> Hello,
>
> I am writing the data of a ResultSet object to an array. To declare
> the array length, I need to know the number of rows before copying
> them - is there a way to read the row count instantly, without having
> to iterate anything?
>
> PHP has mysql_count_rows, but I'm not sure what that does internally -
> it may just iterate over the data too.
>
>
There is a SQL statement to the effect: select count(*) from tablename
Look it up to see if it can be optimized.
--
Sabine Dinis Blochberger

Op3racional
www.op3racional.eu

On Dec 10, 10:34 am, Dyreatn...@sun.com wrote:
> Christoph <christoph.bursc...@gmail.com> writes:
> > Hello,
>
> > I am writing the data of a ResultSet object to an array. To declare
> > the array length, I need to know the number of rows before copying
> > them - is there a way to read the row count instantly, without having
> > to iterate anything?
>
> No. You could execute 'SELECT COUNT(*) ...' first but then you
> essentially traverse all the rows twice.
>
> (There could be non-standard MySQL extensions that lets you do this, I
> don't know)
>
>
>
> > PHP has mysql_count_rows, but I'm not sure what that does internally -
> > it may just iterate over the data too.
>
> --
> dt
>
> Questions about Derby/Java DB? Please visithttp://db.apache.org/derby/derby_mail.html


Okay, I'm working around it by using

while(result.next()) rowcount++;

At least incrementing the pointer is not a very expensive action. Even
when I get a few thousand results back (which I will) the counting
will take far less time than the copying and instance creation that
follows.

On Dec 10, 10:45 am, Sabine Dinis Blochberger <no.s...@here.invalid>
wrote:
> Christoph wrote:
> > Hello,
>
> > I am writing the data of a ResultSet object to an array. To declare
> > the array length, I need to know the number of rows before copying
> > them - is there a way to read the row count instantly, without having
> > to iterate anything?
>
> > PHP has mysql_count_rows, but I'm not sure what that does internally -
> > it may just iterate over the data too.
>
> There is a SQL statement to the effect: select count(*) from tablename
> Look it up to see if it can be optimized.
> --
> Sabine Dinis Blochberger
>
> Op3racionalwww.op3racional.eu

Thanks. Right now I'm looping over result.next, but I'll try sending a
COUNT() query to compare speeds.

Hi Christoph

as others already pointed out, there is SELECT COUNT (*). The advantage
of that is that the counting can be done in the DB instead of sending
all records from the DBto your app.
But, as you want to read all records anyway, why not store them in a
Java collection (say a List), and use .toArray() to get an array out
of the collection? That way the data has to be fetched only once, and
you need only one DB call.

HTH
Guido

Christoph schrieb:
> Hello,
>
> I am writing the data of a ResultSet object to an array. To declare
> the array length, I need to know the number of rows before copying
> them - is there a way to read the row count instantly, without having
> to iterate anything?
>
> PHP has mysql_count_rows, but I'm not sure what that does internally -
> it may just iterate over the data too.
>
> --
> Chris

Christoph <christoph.burschka@gmail.com> writes:

> On Dec 10, 10:45 am, Sabine Dinis Blochberger <no.s...@here.invalid>
> wrote:
>> Christoph wrote:
>> > Hello,
>>
>> > I am writing the data of a ResultSet object to an array. To declare
>> > the array length, I need to know the number of rows before copying
>> > them - is there a way to read the row count instantly, without having
>> > to iterate anything?
>>
>> > PHP has mysql_count_rows, but I'm not sure what that does internally -
>> > it may just iterate over the data too.
>>
>> There is a SQL statement to the effect: select count(*) from tablename
>> Look it up to see if it can be optimized.
>> --
>> Sabine Dinis Blochberger
>>
>> Op3racionalwww.op3racional.eu
>
> Thanks. Right now I'm looping over result.next, but I'll try sending a
> COUNT() query to compare speeds.

Not entirely sure, but I'm guessing that Sabine (as did I) assumed that
you were using a TYPE_FORWARD_ONLY result set. Then you _have_ to
traverse the rows twice, either by first calling count(*) and then
actually executing your query, or looping over the first rs and count
rows and then loop over a second rs (resulting from a new execute) to
fill in your array. (The count(*) approach transmits less data across
the network, but on the other hand you need to compile two
queries. If you do this often and use prepared statements, the
compilation cost may not matter. You'll have to test .

On the other hand; if you're using a scrollable result set, you can loop
over it to find the size, allocate your array, rewind and fill it. But
then you typically materialize the entire result set so you would need
enough memory for _both_ the result set and your array (unless your
driver does some tricks and only materializes parts of the scrollable
result set, but then you would get additional roundtrips to the server
"behind your back").

But why can't you use an ArrayList rather than an array? Typically that
would result in ~ log2(n) reallocation+copy operations. If you have a
heuristic which estimates how many rows will be returned, and set the
initial capacity accordingly, you can get even fewer. Just my $.02.

--
dt

Questions about Derby/Java DB? Please visit
http://db.apache.org/derby/derby_mail.html

Dyreatnews@sun.com wrote:
> On the other hand; if you're using a scrollable result set, you can loop
> over it to find the size,

You don't even need to loop.

Statement stmt =
con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT * FROM T1");
rs.last();
int n = rs.getRow();
rs.first();

But I would never use that construct.

Arne

Christoph wrote:
> I am writing the data of a ResultSet object to an array. To declare
> the array length, I need to know the number of rows before copying
> them - is there a way to read the row count instantly, without having
> to iterate anything?
>
> PHP has mysql_count_rows, but I'm not sure what that does internally -
> it may just iterate over the data too.

If you read the fine print in the PHP manual:

http://www.php.net/manual/en/functio...l-num-rows.php

#Note: If you use mysql_unbuffered_query(), mysql_num_rows() will not
#return the correct value until all the rows in the result set have been
#retrieved.

You will see that it only return the the right number if you
either read all rows to memory with mysql_query or with
mysql_unbuffered_query have read all rows.

So in that sense PHP and JDBC are identical.

BTW, I agree completely with all those that have suggested
forgetting about the number and store the data in an
ArrayList.

Arne

Arne Vajhøj wrote:
> Dyreatnews@sun.com wrote:
>> On the other hand; if you're using a scrollable result set, you can loop
>> over it to find the size,
>
> You don't even need to loop.
>
> Statement stmt =
> con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
> ResultSet.CONCUR_UPDATABLE);
> ResultSet rs = stmt.executeQuery("SELECT * FROM T1");
> rs.last();
> int n = rs.getRow();
> rs.first();
>
> But I would never use that construct.

Arne: What are the dangers of that construct?

I'm guessing that it's the overhead of going first / last / first on large
datasets.

--
Lew