Hi Elvis,

The problem is that 16 tables are being loaded into a single tMap.  You would have the same problem if you wrote a query in a database joining 16 tables. 

Think of it this way.  If you have 2 tables, one with 10 records, and one with 100 records, when you join the two tables together you will potentially loop through 1000 records total depending on your settings (find all matches, find unique matches, find first match).  If you add another table with 10 records, you are now at 10,000 records in the hashmap.  Imagine this same paradigm with 16 tables, some of which are much larger.

The will perform much better and consumer less overall memory if you use multiple tmaps and bring fewer tables into each (rule of thumb: no more than 5 per tMap, preferably less).  This is similar to using subqueries in a database.

Thanks and good luck,
Ben

badproxy, Your problem is slightly different.   Elvis had many small tables, you have 2 very big tables.  You may each be experiencing memory problems, but the solution to each problem differs. 

Perhaps this should be a new thread....but anyway

Those are pretty big tables and that is a lot of records to load into memory.  How many rows are expected on the output?  You could write the joins and filter conditions into the SQL queries and extract fewer rows.

Can you please post pics of your tMap where these tables are being joined?

Thanks,
Ben

Last edited by phobucket (2012-04-24 22:28:34)

Hi guys, problem solved... The job ran without problem, by the way the TOS is a excellent tool!

Solution? Buffering in HD!

Thanks for all, again!
See ya!

UPDATE:
I've re-engineered one of my jobs splitting the lookups between 2 tmaps, I must say however that the memory requirements have stayed exactly the same; before re-engineering it would only just run with  Xmx5G and afterwards it would only just run with Xmx5G.

Phobucket's post above suggests that the hasmap grows as if a cross join (each new lookup increases the memory requirments X number of rows) however my experience here suggest that th hashmap increases in a linear way (each new lookup increases the memory requirements +  number of rows).

Or perhaps the hashmap size is not the limiting factor?

As you can see from the screenshot the main "flow" is still quite slow too.

Last edited by elvis (2012-04-27 11:31:17)

elvis,

It looks like the reads from the database are fast (up to 65k rows per second), so the problem is either in the map or the output.

brandon makes a good point that the bottleneck might not be in the tmap, so editing the MySQLOutput settings could help. 

You could also try and pinpoint the problem by sending the output from the first tmap to a tFileOutputDelim and see how it performs.  If performance at the stage is ok, you could move on to the second tMap.

Going into the second tmap, for the 3 mapicscosts inputs... are you joining on 3 different fields?  If not, it could be possible to design these as a single input with a code field to differentiate the type.