RE: NOT EXISTS and NOT IN (was RE: SQL Help finding missing records) -- RPG400-L

Perhaps the distinction lies in the "style" of IN clause defined... IN can parse a list of values -or- single column rows of a subquery. Row selection is clearly the primary use of an access path (meaning that an index could benefit this use), whereas list value selection is a not necessarily related to row selection. FWIW, I have gotten myself out of the habit of using IN when there are other approaches, because of these recommendations. I tend to use join logic to constrain my results....

Then again, its all just a WAG on my part.

Eric

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of vhamberg@xxxxxxxxxxx
Sent: Thursday, July 17, 2008 12:16 PM
To: RPG programming on the AS400 / iSeries
Subject: RE: NOT EXISTS and NOT IN (was RE: SQL Help finding missing
records)

Eric

True enough.

The matter of supporting indexes still holds, I believe. The caution against using NOT IN was given within the last year by a knowledgeable IBM person. So it was given fully in the context of running in SQE.

Again, if there is no index, the choices are not good for the optimizer.

Vern

-------------- Original message --------------
From: "DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>

Vern,

I suspect that this is yet another "legacy" issue, that has been corrected on
newer releases. I'm certain that CQE had serious issues with NOT IN, but I'm
not so sure about SQE... Perhaps some of these performance related statements
need to be re-reviewed so that we can un-learn these notions that no longer
apply....

Eric DeLong

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of vhamberg@xxxxxxxxxxx
Sent: Thursday, July 17, 2008 10:38 AM
To: RPG programming on the AS400 / iSeries
Subject: Re: NOT EXISTS and NOT IN (was RE: SQL Help finding missing
records)

Charles

I was probably mistaken on the NOT EXISTS, which would take advantage of an
index, if it exists.

And it's good that NOT IN can use and index. I will still stand by what was said
by the IBMer, however, that NOT IN can be a performance drag; better, if
possible, to have a predicate without the NOT. Without an index to use, the
optimizer would have either to create one (table scan PLUS writing the index
out) or to do the table scan.

Lots of ad hoc queries do not have indexes to support them. Of course, that's
another issue.

Vern

-------------- Original message --------------
From: "Wilt, Charles"

Sorry guys, but OS/400 is smart enough to use an index even with a NOT IN.

A test on v5r2....

SELECT SPLVL, count(*)
FROM cstspnp
group by splvl

Salesperson Level COUNT ( * )
413,797
Y 1
2 4
1 299,078
N 3

CREATE INDEX cmwtest2 ON CSTSPNP (SPLVL)

select * from cstspnp
where splvl not in ('1',' ')

Access path of file CMWTEST2 was used by query.
for reason code 1.
1 - Record selection.

I think the issue is that usually a NOT IN selects enough records that a table
scan is the best
choice.

HTH,

Charles Wilt
--
Software Engineer
CINTAS Corporation - IT 92B
513.701.1307
wiltc@xxxxxxxxxx

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of DeLong, Eric
Sent: Wednesday, July 16, 2008 12:36 PM
To: RPG programming on the AS400 / iSeries
Subject: RE: SQL Help finding missing records

I don't think so..... NOT EXISTS will use an index scan *IF* the fields
referenced in the WHERE of the subquery exist within an index. I would
agree with that statement in the case of the IN predicate....

Eric DeLong

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of vhamberg@xxxxxxxxxxx
Sent: Wednesday, July 16, 2008 10:32 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: SQL Help finding missing records

Caveat emptor!!

Remember that NOT EXISTS, as well as NOT IN, forces a table scan - you
can't tell if something is not there unless you look at everything.
Learned this at the last COMMON, I think.

Now probably and EXCEPTION JOIN can do better, IF you have supporting
indexes.

Regards
Vern

This e-mail transmission contains information that is intended to be
confidential and privileged. If you receive this e-mail and you are not a

named

addressee you are hereby notified that you are not authorized to read, print,
retain, copy or disseminate this communication without the consent of the

sender

and that doing so is prohibited and may be unlawful. Please reply to the
message immediately by informing the sender that the message was misdirected.
After replying, please delete and otherwise erase it and any attachments from
your computer system. Your assistance in correcting this error is appreciated.
--
This is the RPG programming on the AS400 / iSeries (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 is the RPG programming on the AS400 / iSeries (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 is the RPG programming on the AS400 / iSeries (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.