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

[Darryl Freinkel <dhfreinkel@xxxxxxxxx>]

The join did the trick.

Thank you. Darryl.

On Thu, Dec 22, 2016 at 10:17 AM, D Freinkel (a4g) <
DFreinkel@xxxxxxxxxxxxxxxxx> wrote:

> I have created indexes and iNav has zero suggestions.
>
>
>
> I have not tried the join. Duh. I’ll try that next.
>
>
>
> Thanks
>
>
>
> Darryl
>
>
>
> From: Paul Therrien [mailto:paultherrien@xxxxxxxxxxxxxxxxxx]
> Sent: Thursday, December 22, 2016 9:49 AM
> To: RPG programming on the IBM i (AS/400 and iSeries)
> Cc: D Freinkel (a4g)
> Subject: Re: Seeking a faster response to Where EXISTS or WHERE IN
>
>
>
> 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