topic Re: Lookup table no primary keys in Talend Studio

Lookup table no primary keys

TharunJoshi — Sat, 16 Nov 2024 05:04:03 GMT

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

Re: Lookup table no primary keys

Anonymous — Tue, 06 Aug 2019 10:21:25 GMT

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?

Re: Lookup table no primary keys

TharunJoshi — Tue, 06 Aug 2019 11:42:32 GMT

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

Capture2.PNG
Capture3.PNG
Capture4.PNG

Re: Lookup table no primary keys

Anonymous — Tue, 06 Aug 2019 12:58:03 GMT

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

Re: Lookup table no primary keys

TharunJoshi — Tue, 06 Aug 2019 13:11:47 GMT

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

Re: Lookup table no primary keys

Anonymous — Tue, 06 Aug 2019 14:46:16 GMT

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.

Re: Lookup table no primary keys

TharunJoshi — Mon, 12 Aug 2019 17:11:20 GMT

Hi,

I used tcrc component it works fine

Thanks