I would be very surprised if it were not backed up by a cursor on the
server side. It does, however, probably do next, next, next on the
server side. I bet if you wrote a simple stored proc that creates a
cursor and then increments it to the end you would see similar performance.

My guess is there is still data movement out of the table as the cursor
gets incremented but there isn't (as much) data movement when count() is
used.

This is, of course, all conjecture (are there enough disclaimers in
here? :-) )

IMHO reasons for that are:

1. All resultset data travel from servet to client, client initialize memory
for that data, copy that data into that memory locations.

2. Speed of "standard" trick vary of implementation of resultset (array or
list ?)

So, it is not strange that count(*) is much faster.

That should be expected since it is a bad "trick". Scrollable/updateable
resultsets are bad anyway if performance is required. The point is that
there typically is no such thing as a scrollable/updateable resultset on the
backend so the driver emulates this in a generic way. This is almost always
less efficient than running the right combinations of select, insert and
update yourself.

In this case the query is executed, all rows are fetched and probably
discarded and then the query is probably executed again. Selecting a count
is obviously much faster.

Silvio Bierman

Thanks for your support.
HW