Re: SQL/CLI Count of rows returned in result set

On 29-May-2015 20:47 -0500, Scott Klement wrote:

I'm very interested in this discussion, as we tried to find a way to
do this a few years ago, and the best we could find was to do
something like:

SELECT COUNT(*) FROM (the-statement)

Correct. Getting the actual row COUNT is the consistently accurate means. Another option is to output the result set and get the record count from the member of the file that was created for that purpose. If that /output/ is in the form of a derived-table [implemented as a true temporary] there is a mildly reduced impact to performance more noticeable for small result sets. The following Common Table Expression (CTE) for example, used to obtain the count in the result-set for the SELECT:

with
ts as (the-select-statement)
select
(select count(*) from ts) as sc
, ts.*
from ts

We tried the various diagnostic fields, and all either gave an
"estimated" count, or didn't return anything at all until you had
already read the result set.

The Open indeed may be able to give only an /estimated/ row count; until a[n actual or effective] FETCH of every selected row is complete, the actual number is unknown:


<http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzfielddescsqlca.htm>
IBM i 7.2->Database->Reference->SQL reference->SQLCA (SQL communication area)->
_Field descriptions_
SQLERRD(2) ...
...
For an OPEN statement, if the cursor is insensitive to changes, SQLERRD(2) contains the actual number of rows in the result set. If the cursor is sensitive to changes, SQLERRD(2) contains an estimated number of rows in the result set.
..."

Our goal was to create a scrollbar for a grid that we were loading
one page at a time from SQL. The count is needed to determine the
total size so that we can make the scrollbar show the position
accurately. But nothing we've tried has worked aside from doing a
COUNT() of the statement as a subselect, or reading the whole result
set twice (which isn't performant enough)

Using insensitive cursors, mostly from temporary\sorted results, is the best to avoid the estimates.