0

I am using Informatica Intelligent Cloud Services (IICS) Intelligent Structure model to parse the JSON file that I have.The file is located on S3 bucket,and it contains 3 groups. 2 Groups contains lots of records (~100,000) and 3rd group contains (~10,000 records). According to Intelligent structure model, largest group contains PK, which I can use to join the other group, but the issue is for Master and Detail which group should I select ? Usually, group with lower records should be selected but in my case, lower records contains foreign key ? Is there a work around for this issue ?

I am new to IICS so how to resolve the issue ? Any help will be appreciated. Thanks in advance!

biggboss2019
  • 220
  • 3
  • 8
  • 30

1 Answers1

0

Rule is, select table with samll rowcount should be master because during execution, the master source is cached into the memory for joining purpose.

Having said that, can you use 3rd group with less rows as master for both joins like below. If its normal join, logic remains same but perf will improve if you choose master with less rows and less granularity.

Sq_gr1(d)\
Sq_gr3-jnr1(m)->|jnr2----->
Sq_gr2(d)------>/

Outer join will take time equivalent to count of rows.

Koushik Roy
  • 6,868
  • 2
  • 12
  • 33
  • ok..so let me explain little bit more...grp1 contains PK, grp2 contains FK of PK, grp3 contains FK of PK. I am using Normal join that case...will join of FK = PK work ? I am doing similar mentioned here -> https://www.youtube.com/watch?v=sfyKdT2udbU – biggboss2019 Jun 12 '22 at 14:01
  • 2
    As mentioned by Koushik, choosing between Master and Detail does not affect the logic. Especially with Normal Join - the results are exactly the same. This only affects performance. – Maciejg Jun 12 '22 at 16:46
  • Thank you! So it doesn't matter if I use FK_root = PK_root for Normal JOIN in IICS ? Just want to clarify. Thanks in advance! – biggboss2019 Jun 12 '22 at 18:57
  • 1
    In case of normal join, it reads master first, caches into index,data cache, then read details based on that. so, logic remains same but if you set master with less rows, and granularity, infa has to do less comparison/less caching. So, it improves performance. – Koushik Roy Jun 13 '22 at 02:35