Maximum Number of Lookups in single transformer

[jpr196]

Hi All,

This topic isn't really regarding an error, but best practice. Is there any magical number of lookups you can do in one transformer before you should use a second tranformer? Is there a performance difference between doing 20 lookups in one transformer and 10 lookups each in 2 transformers for example? Is it all relative?

Thanks

[DSguru2B]

Really depends, as you said, its relative. If having 20 lookups does not slow down the job, then go for it. Else I would recomend doing it in two seperate jobs.
Like, keeping 5 heavy lookups in one job and 15 small lookups in another job.
This will also help you in restratability.

[ray.wurlod]

Using multiple Transformer stages and inter-process row buffering can give you some performance gains, particularly if you have more than one processor.

[reddy.vinod]

Hi All,

This topic isn't really regarding an error, but best practice. Is there any magical number of lookups you can do in one transformer before you should use a second tranformer? Is there a performance difference between doing 20 lookups in one transformer and 10 lookups each in 2 transformers for example? Is it all relative?

Thanks

Hi,
U try to use less than or equal to 7 lkp ups on a single transformer,else u r performance will go down..if u need to take more than 7 tkae them in another transformer.

[ArndW]

There is no particular reason for '7' references. What I will do with a particularly complex transform stage on a multi-cpu system is monitor the %CPU of that tranform stage. If it is close to 100% then the job can be made somewhat faster by splitting into 2 transforms stages (assuming interprocess buffering being enabled). This splitting can be continued until CPU use on a single transform is no longer the bottleneck. Note that the procedure applies in similar fashion to both server and PX jobs.