Re: COUNT(*) or FETCH Faster?

[Charles Wilt <charles.wilt@xxxxxxxxx>]

The right way to do this in SQL is with the EXISTS clause....

select 1 into :hasRecords
from sysibm/sysdummy1
where exists (select * from mytable where mykey = :somekey)

It's a little tricky as if no records exists, you get no records
returned, and so need to check SQLSTATE or SQLCODE... (google for
complete examples)  A short cut is to make sure hasRecords is
initalized to 0, as the system won't change it if there are no
records.

However, assuming a recent version of the OS, i think you'll find
COUNT(*) to not be too bad assuming you have an index over the column
in question.   In particular, an EVI index over MYKEY gives the SQE
easy access to the number of records with that key.

Use of COUNT(*) with an EVI index is preferred if you actually need to
use the count.

HTH,
Charles


On Mon, Aug 13, 2012 at 2:21 PM, Gary Thompson <gthompson@xxxxxxxxxxx> wrote:
> 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.
>
>
>
> --
> 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.