× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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" <WiltC@xxxxxxxxxx>

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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.