When Good Lookups Go Bad

[RDBMS] While Pervasive Data Integrator Lookup Functions provide functions that can be re-used throughout your transformation, they can suffer from performance problems.  To avoid running a query (the implementation of a Lookup Function), use a query rather than a table as the source of a transformation.

In some cases, it may not be performant to execute a transformation record-by-record, running a Lookup Function with each iteration.  If this is the case, consider building the source off of a query rather than a table where the query will do the work of finding the extra data.  Take the following source of a map "ConvertSalesAssocRegions".

 

This source is based on an Access query that follows.


SELECT FirstName, LastName, Prefix, Suffix,
Email, Phone, StreetAddr1, StreetAddr2, City,
sa.State,
Country,
nr.Region
FROM SalesAssociates sa, NewRegions nr
WHERE sa.State = nr.State

For some deployments, the relational database server will do the extra work rather than requiring the processing machine (where the Data Integrator Map is running) to shuffle data in and out of the database. This will result in a very scalable implementation that can take full advantage of database features like clustering and avoids bottlenecks like the network.

The downside of embedding the lookup in a query is that it can lead to SQL statements hidden in Maps  that are difficult to maintain. But if you're dealing with a lot of data, it may be your best implementation.