No, there was no ORDER BY clause, since all I was interested in was determining the presence or otherwise of a record in the secondary file. I do know that the secondary file had no logical views or indexes that matched the columns in my WHERE clause. However, the exact same WHERE clause was used in the SELECT COUNT(*) with apparently much less impact on performance.
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 Birgitta Hauser
Sent: Saturday, November 03, 2012 5:44 AM
To: 'RPG programming on the IBM i / System i'
Subject: AW: Should I replace all CHAIN, SETLL, and READs to SQL
@Trevor
Was there and ORDER BY clause in your SELECT ... FETCH 1 ROW ONLY?
If so, the all data must be searched, returned and ordered before the first
row can be returned.
If the optimizer cannot find an appropriate index, a Table scan or at least
a table probe will be executed.
Also if SELECT * was performed instead of selecting only a single column or
a few columns, a lot of database operations must be performed.
In either way according to my latest tests:
Select 1 From ... FETCH FIRST ROW ONLY (without Order By) and executed as
static SQL Statement was 2,5 times slower than the comparable CHAIN.
Both Statements were executed in a loop and repeated for 100, 1000, 10000,
... 1000000 times.
In the static SQL Statement the physical file/SQL Table is specified and
there was an SQL index with the appropriate key, which was used by executing
the SQL Statement.
The same SQL Index was used for native I/O, i.e. it was specified in the
F-Specs.
For the SQL Statement only a single FULL OPEN was performed while for the
subsequent executions only PSEUDO OPENs (ODP could be reused) were
performed.
Select Count(*) From was only almost as fast as SELECT 1 FROM if an
appropriate EVI with INCLUDE for the appropriate key otherwise SELECT
Count(*) was much slower, depending on the number of rows to be added.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Briggs, Trevor (TBriggs2)
Gesendet: Friday, 02.11 2012 21:17
An: rpg400-l@xxxxxxxxxxxx
Betreff: RE: Should I replace all CHAIN, SETLL, and READs to SQL
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.
--
Regards, Chuck
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
****************************************************************************
****************************************************************************
****************************************************
This message originates from Lincare Holdings Inc. It contains information
which may be confidential or privileged and is intended only for the
individual or entity named above.
It is prohibited for anyone else to disclose, copy, distribute or use the
contents of this message.
All personal messages express views solely of the sender, which are not to
be attributed to Lincare Holdings Inc., and may not be copied or distributed
without this disclaimer.
If you received this message in error, please notify us immediately at
MailAdmin@xxxxxxxxxxx or (800) 284-2006.
****************************************************************************
****************************************************************************
****************************************************
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
midrange.com
