Birgitta, et al,
Thanks. There is no appropriate index over the file; Date within Item#
would only be used by the function I'm trying to write (at least, I can't
think of anything else here that might benefit).
I admit that I've always liked the COUNT(*) method simply because it only
takes one statement. .-)
Jerry C. Adams
IBM i Programmer/Analyst
Never forget that your weapon was made by the lowest bidder.
--
A&K Wholesale
Murfreesboro, TN
615-867-5070
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Birgitta Hauser
Sent: Monday, August 13, 2012 11:59 PM
To: 'Midrange Systems Technical Discussion'
Subject: AW: COUNT(*) or FETCH Faster?
Fetching a single value with the right index is faster than using a
count(*), even though this information could be pulled from the statics.
According to my last tests, the fastest way to check for record existence
with SQL is:
Exec SQL Select 1 into :isFound
From MyTable
Where ....
Fetch First Row only;
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: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Jerry C. Adams
Gesendet: Monday, 13.8 2012 20:16
An: 'Midrange Systems Technical Discussion'
Betreff: COUNT(*) or FETCH Faster?
No referential integrity constraints here. Usually, when I want to check
for records in a secondary file before I allow a delete of, say, a customer
master record, I just used SQL SELECT with COUNT(*) and return that to the
maintenance program.
Works okay, but now I need to check an invoice history table which has about
2,000,000 records, but I only want to check for the last twelve [12] months.
I was wondering if a SELECT into a cursor and then a FETCH FIRST 1 ROWS ONLY
might be faster.
I'm pretty sure the COUNT(*) option has to process all of the records for
the period I pass to it. I figured the SELECT into a cursor did, too, but
don't know for sure.
Thanks.
Jerry C. Adams
IBM i Programmer/Analyst
Try to look unimportant; the enemy may be low on ammo and not want to waste
a bullet on you.
--
A&K Wholesale
Murfreesboro, TN
615-867-5070
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
