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

[CRPence <CRPbottle@xxxxxxxxx>]

  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.