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

[AGurau1639681093]

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.

[Anonymous]

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

[AGurau1639681093]

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.