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.
Trevor Briggs
Analyst/Programmer
Lincare, Inc.
(727) 431-1246
TBriggs2@xxxxxxxxxxx
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Friday, November 02, 2012 3:55 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: Should I replace all CHAIN, SETLL, and READs to SQL
On 02 Nov 2012 12:46, Briggs, Trevor (TBriggs2) wrote:
Did you benchmark SELECT COUNT(*) FROM XXXX WHERE..... ?
I've found this much faster than SELECTing other ways for just
checking a record's existence.
When using predicates across all key columns that definitively select
just one row via an established UNIQUE KEY or PRIMARY KEY constraint?
Otherwise, or more generally for more generic query row-exists SELECT,
...
Why ask for the count of all rows matching some selection, unless the
actual count was required? If the implementation of the query requires
a full table scan, then instead of stopping after finding existence of
just one matching row, the database must complete scanning every row to
obtain that count. While the Storage Management can be very efficient
to implement that, the potential for contention on disk access across
the jobs on the system could negatively impact the overall throughput of
the system.
Both an EXISTS predicate and the FETCH FIRST 1 ROW ONLY clause asks
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.
midrange.com
