Lookup Performance

[sreedhards]

Hi,
I'm using Lookup stage in my job,there are 10 lookup tables and one primary table.Please suggest any tips for better performance on higher volumes.

Thanks,
Sreedhar S.

[ArndW]

The number of lookups you perform isn't as important as the type of data source behind the lookups (is it a dataset? A database table?) and, more importantly, how big the data volumes are in the lookups themselves. Without knowing the size of the reference data (in MB, average record length times number of records) no suggestions or recommendation can be made.

[rkashyap]

What is the issue that you are facing ... What is the volume of incoming records vs lookup table size? How long is the execution time?

[ray.wurlod]

The answer will depend on many factors, including what other load there is on the server(s).

For example you could use 10 Lookup stages and disable operator combination, so that the individual lookups are being performed in separate processes. If not much else is happening on the machine, this would tend to be quite quick, particularly if you have a large volume of data and relatively small reference data sets.

[kduke]

If your lookups have small number of rows then it works fine. Your inbound data stream can be huge without issue. If your lookup table size grows then it becomes necessary to use other stage types no matter how large the inbound stream. You can use sparse lookups with small inbound stream and large lookup tables. Should have indexes on the keys in a sparse lookup.