Jerry,
I think if you have an index on "months", then
your FETCH FIRST should work - and the testing
should be a snap.
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jerry C. Adams
Sent: Monday, August 13, 2012 12:16 PM
To: 'Midrange Systems Technical Discussion'
Subject: 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.
midrange.com
