Re: MySQL ResultSet - count rows?

Christoph <christoph.burschka@xxxxxxxxx> writes:

On Dec 10, 10:45 am, Sabine Dinis Blochberger <no.s...@xxxxxxxxxxxx>
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
.

Relevant Pages

  • RE: Error 3021
    ... Create proto-file names using the selected job names and storre to an array ... Save and close the document and repeat the loop ... Dim strJobsAs String, strDocsAs String, varValsAs _ ...
    (microsoft.public.access.modulesdaovba)
  • RE: Error 3021
    ... Kevin Backmann ... Create proto-file names using the selected job names and storre to an array ... Save and close the document and repeat the loop ... Dim strJobsAs String, strDocsAs String, varValsAs _ ...
    (microsoft.public.access.modulesdaovba)
  • RE: Error 3021
    ... Create proto-file names using the selected job names and storre to an array ... Save and close the document and repeat the loop ... Dim strJobsAs String, strDocsAs String, varValsAs _ ...
    (microsoft.public.access.modulesdaovba)
  • Re: Displaying a large amount of data quickly (VB6)
    ... >>> involving a loop of VB code would be too slow. ... but I'd sure be interested to know if that StringBuilder ... Array elements: 25000 ... Array construction: 17 ...
    (microsoft.public.vb.controls)
  • RE: Error 3021
    ... Create proto-file names using the selected job names and storre to an array ... Save and close the document and repeat the loop ... Dim strJobsAs String, strDocsAs String, varValsAs _ ...
    (microsoft.public.access.modulesdaovba)