Re: Seeking a faster response to Where EXISTS or WHERE IN

Have you checked your statement with iNav (Visual Explain)? What about the
files indexes?

Regards,

Luis

On Thu, Dec 22, 2016 at 10:49 AM Paul Therrien <
paultherrien@xxxxxxxxxxxxxxxxxx> wrote:

how about:

select a.* from filea a

join (select disinct field_aa from from file_B) b

on a.field_a = b.field_aa

(syntax may not be exact)

This might be quicker.

Paul

On 2016-12-22 09:22, D Freinkel (a4g) wrote:

I am doing extracts from files based on records in file A

Select * from FILE_A where field_A in (Select FIELD_AA from FILE_B).

Or

Select * from FILE_A where field_A exists i (Select FIELD_AA from FILE_B
where FIELD_A = FIELD_B).

File_A and FILE_B both have about a million records.

Problem:

When running the program it takes a few minutes to build the sub-select.

Using CHAINS is instant, but I would prefer to use SQL.

Can anyone help with performance improvements?

TIA

Darryl Freinkel

--
This is the RPG programming on the IBM i (AS/400 and 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.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD