Re: Should I replace all CHAIN, SETLL, and READs to SQL

Hmmm, I have never known of the DB2 for i SQL to allow that syntax [FETCH integer ROW(S) ONLY] as a clause on a SELECT; only a FETCH-FIRST-clause.
http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzfetchfirst.htm

Perhaps the reference to that syntax implies FETCH FOR n ROWS? That is, perhaps the FETCH statement with a FOR-integer-ROWS clause was being used? That could justify the disparate results for a variety of reasons, mostly because that would mean a result set was produced and even the possibility that the statement was performed implicitly FOR UPDATE. Specifying the FETCH FIRST integer ROW(S) ONLY clause on the SELECT makes the statement implicitly FOR READ ONLY and should have allowed the query to be optimized quite similar to how a COUNT(*) aggregate read-only query would using the same predicates.

If not from having coded a FETCH statement versus the FETCH-FIRST clause which should give a /hint/ for optimization of the SELECT, then the described effect [15 minutes vs sub-second] seems most likely to have been a defect; hopefully an anomalous scenario for which a fix has since been provided. While an aggregate query can work just as fast [or perhaps even faster than] the EXISTS predicate or FETCH-FIRST-clause in some optimizations, as I had noted, *if* the query implements such that the database must actually process rows, then the aggregate count query will spend as much time as is necessary to effect that counting of every matching row. No matter how the query implements with the FETCH-FIRST-clause or an EXISTS predicate, the database will [have been told to] stop after finding just one match.

Regards, Chuck

On 02 Nov 2012 15:17, Briggs, Trevor (TBriggs2) wrote:

I had a program that was running in less than a second, and I had to
add a check to it to see if a record existed in another file. I used
a SELECT with the FETCH 1 ROW ONLY clause (I wasn't aware of the
"FIRST" option, although that shouldn't make a difference in this
case). The program took 15 minutes. I replaced the SELECT with SELECT
COUNT(*) and the program was back to sub-second completion. Now I
know there are a myriad factors in play, a couple of which being in
my favor were that the majority of records would NOT have a record in
the second file and those that did would most likely only have one
and occasionally a few. In my particular experience, I've never found
a SELECT COUNT(*) to be a performance hog, which I've always
attributed, in my admitted ignorance of the actual mechanics, to be
due to the fact that it doesn't care anything about having to
manipulate any data.

CRPence on Friday, November 02, 2012 3:55 PM wrote:

<<SNIP>>

Both an EXISTS predicate and the FETCH FIRST 1 ROW ONLY clause
ask via the SQL that the database should stop searching after
encountering the first matching row. If one could be positive
that the query implementation will be with a known compatible
INDEX which can be /probed/ for the existence of the row(s) and
the database easily be able to know [all key values] or quickly
count the rows [partial key] under a particular node [which as
/random access/ that the data is effectively
clustered\already-paged would be desirable]. But if one can
presume so much, then selecting only [any of the key columns or]
a literal value enables an index-only implementation [possibly
same as the noted /fast/ COUNT(*) queries] for which no actual
row data is accessed to conclude the query. With either of these
non-aggregate queries, the implication is that the [sub]query
will be performed as though the /hint/ OPTIMIZE FOR 1 ROW has
been coded on the SQL request.