Lookup table no primary keys

Highlighted
Six Stars

Lookup table no primary keys

Hi,
I have a table which gets data from an Excel file every week with 1M and above records. I don't HAVE ANY PRIMARY KEYS in the table,now when I put a lookup where inner join on lookup and output table has reject (true), it is checking each and every record which is taking a lots and lots of time. How to improve performance for this any idea is appreciated.
Thanks in advance

Accepted Solutions
Six Stars

Re: Lookup table no primary keys

Hi,

I used tcrc component it works fine 

Thanks


All Replies
Community Manager

Re: Lookup table no primary keys

How many records are in your main recordset?
Can you show us a screenshot of your job and your tMap configuration?

Is it possible for you to load the Excel data into a database first?

Six Stars

Re: Lookup table no primary keys

Hi 

Here are screenshots below

I will give you an overview of my task I have an excel file with four excel sheets in it.Each excel sheet will be moved into four different tables in same database,which has at least Fifty thousand to 1 million this updates every week.Now the first load of excel file is done now if the second excel file comes after one week i should also find duplicates so i used lookup to catch it but checking 1 million records is a very big issue

Check the screenshots

 

Community Manager

Re: Lookup table no primary keys

You don't appear to have any joins in your tMap. This will essentially create a cartesian join.

Six Stars

Re: Lookup table no primary keys

Can you tell me what can I do as I have explained my scenario

Community Manager

Re: Lookup table no primary keys

Well first of all I would decide whether you need to have all of the processing in one job? Can the processing be split into multiple jobs? Does it all need to be done at the same time? The next thing you need to do is decide how you are joining. As I said in my last post, it doesn't look like you have any joins in your tMap. This will not work as what you are doing is essentially multiplying the rows of the main input by the number of rows in the lookup. You need to decide how you can join the data (what columns you need to match) and then move on from there.

Six Stars

Re: Lookup table no primary keys

Hi,

I used tcrc component it works fine 

Thanks

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Why Companies Move to the Cloud: 7 Success Stories

Learn how and why companies are moving to the Cloud

Read Now