topic Inner join with tMap and Oracle extremely slow and then errored out with java out of heap space in Talend Studio

Inner join with tMap and Oracle extremely slow and then errored out with java out of heap space

AGurau1639681093 — Fri, 15 Nov 2024 22:19:09 GMT

Hi, newbie here.

The table on the left id sql server and has a few hundred records in real life. In my example it has 13.

The lookup table is Oracle and has a million and half records. I need to join them on a key, via tMap only that it's extremely slow and then it bombs out.

I only need to join theses table on those 13 records whose fields values are to be updated in the Oracle table. How do I speed this up? Is there any other way to do this?

How do I fix this? Thanks so much.

Re: Inner join with tMap and Oracle extremely slow and then errored out with java out of heap space

Anonymous — Wed, 30 Nov 2022 02:11:09 GMT

Hello @Adriana Gurau ,

When the main flow has few data, to speed this up, you can setup the Lookup Model to 'Reload at each row (cache)' for tMap

and setup a globalMap key, then setup the condition in the Query of tOracleInput component like:

"select id, date1 from test where id='"+ globalMap.get("id")+"'"

Also, to fix the java heap space error, you can increase the jvm memory by adding the below jvm parameter in the job Run/Advanced settings like

-Xmx4096m

Re: Inner join with tMap and Oracle extremely slow and then errored out with java out of heap space

AGurau1639681093 — Wed, 30 Nov 2022 16:52:28 GMT

Thank you so much. I am not familiar with GlobalMap Key but will look into it. Hopefully I could find a video on youtube. I was thinking if there was such a thing where I could keep all the keys of the records I need to inner join on for example (1, 100, 103, 55) and then the query to be more like select * from table_name where id in globalMap Key. Is this how it works?

I'll do the other things, lookup model and increase the jvm parameter, thanks SO much.