Read to lookup table with millions of row

Five Stars

Read to lookup table with millions of row

Hi everyone, 

 

I've encountered a very simple issue for you guys. 

I need to read data from a lookup table which contains for the moment 2milions + rows.

My SQL Workbench takes 77 secs to display it all but Talend is not working, it does the select query but takes forever to end.

So, how can I resolved this issue ? talendissue.png

PS : I'd prefer a solution entirely based on Talend components and a simple one, doesn't matter if it's not the most performant solution.

Thanks for reading. 

 

Forteen Stars TRF
Forteen Stars

Re: Read to lookup table with millions of row

Hi,

Millions rows for the lookup, but how many for the main row?

Which lookup model are you using (load once or reload at each row)?

As explain in the documentation, "when the main flow has much less rows than the lookup flow (for example, with a ratio of 1000 or more) and the lookup input is a database component" you should consider the option to reload the lookup table at each row giving the opportunity to "select only the lookup data that is relevant for each record in the main flow".

You should have a look at this page.


TRF
Forteen Stars TRF
Forteen Stars

Re: Read to lookup table with millions of row

Did this help you?
If so, thank's to mark your case as solved (Kudo also accepted).

TRF
Five Stars

Re: Read to lookup table with millions of row

Yes thank you, it did help me because I don't know much things about Talend. However, I don't think this is what I want because it increase the time for the job to end. And to answer your previous question my job as much more rows at the end of all tMaps combination.

Forteen Stars TRF
Forteen Stars

Re: Read to lookup table with millions of row

 @Goubi of course this design is not the way to improve performance but it did not seem like your first concern as you said "doesn't matter if it's not the most performant solution".

Note: if you want better response time, run the query on the DB Server.


TRF
Five Stars

Re: Read to lookup table with millions of row

Yes you right.
We can't run on DB Server because the guy working on Talend isn't the same who work on the servers. Our goal now is to create the most performant job in Talend which will be deployed on servers every night.
Forteen Stars TRF
Forteen Stars

Re: Read to lookup table with millions of row

How long it take to run the query associated to the lookup table?

How many rows in the main table?

Which lookup model are you using?

Did you try the solution proposed here?

And finally, it is a common situation to have different guys working with the DB solution and with Talend.

Usually, for complex or particular situations, DB experts write ad-hoc queries exposed as views or SQL functions which are used in Talend jobs. 

This way, you have the opportunity to tune the queries without any change on jobs side (as soon as the selected columns do not change).


TRF
Five Stars

Re: Read to lookup table with millions of row

the query associated to the lookup table take sometimes like 20 minutes i'd say. There are approximately 7 thousand rows in the main table but the output is about 3 million. I use load once as a lookup model and I did not try the solution proposed yet. I'll try it when I'll be able to.
Thanks for your responses.
Forteen Stars TRF
Forteen Stars

Re: Read to lookup table with millions of row

If your lookup table have primary key or indices you are able to use in your query (based on the solution you have to test), it could be the solution but you have to know that you'll run the query on the lookup table for each row in the main table.

Beware of reuse an existing connection for the lookup table (so you maybe have to open 2 connections).


TRF