Lookup Performance Tuning

Lookup Performance Tuning

1. If the number of records coming from source is comparatively much lesser than the number of records present in the lookup table then you should consider using uncached lookup. This is because less number of records from source ensures less number of database calls.
2. If the lookup table is small (less than 2 million), it’s generally a good idea to cache the lookup table.

Effect of suppressing default ORDER BY clause in Lookup
When Informatica fires the lookup query to database, it appends a ORDER BY clause at the end of the query. However you can suppress this default behaviour by appending a comment “--“at the end of the override query. You should consider following factors regarding default Informatica Lookup ORDER BY –

1. ORDER BY clause may create additional processing overhead on database. When Informatica lookup fires the SELECT query to the database, it includes all the lookup ports in the SELECT query and in the ORDER BY clause. Hence ensure that there is no unnecessary port in the lookup. Because otherwise those ports will be included in the Informatica generated SELECT statement and also in ORDER BY clause, thus creating additional overhead in database

2. Also, do not completely suppress the ORDER BY with '--' (see the note below to know why)

3. If you completely suppress the ORDER BY from the Lookup query then Informatica will generate a Warning like below in the session log –

The above warning requires little explanation. If you suppress the ORDER BY, then data fetched from database will not be sorted. Hence Informatica will require to again sort this data in its own cache space in order to generate the Index cache (yes! index cache can not be built on unsorted data). This means in any case, data has to be sorted - either from database or inside the Informatica. From our experience we have noticed that it's better to get data sorted in database level than in informatica level.

However, what you can probably do is: you can suppress the default ORDER BY with '--' and write your own ORDER BY only on those columns (lookup ports) that are being used in the joining. Let's say, you are performing a lookup in the "Employee" table based on emp_id and returning name, salary, department and date_of_joining columns. If you don't do anything, the default Lookup query that Informatica sends to database will be:

SELECT emp_id, name, salary, department, date_of_joining
FROM Employee
ORDER BY emp_id, name, salary, department, date_of_joining

But you know that you do not need to perform the ORDER BY on all those columns since joining column is emp_id and index cache will be built on only that column. Hence you can put your own order by in the SQL Override and suppress the default ORDER BY, like below:

SELECT emp_id, name, salary, department, date_of_joining

FROM Employee

ORDER BY emp_id --

I recommend you always perform ORDER BY at least on the columns which are being used in the joining conditions in the lookup (i.e. lookup ports used in joining)

Remember Informatica creates its own index on the joining columns and therefore if the joining columns are ordered, Informatica would require less space (and time) to create the indexes.

Use of Persistent Lookup Cache

Persistent Cache lookup can be Holy Grail for the cached lookups – if you know how to use them. Generally speaking, if the source data in the underlying lookup tables are not changing between consecutive sessions runs then one may use persistent lookup cache.

In short, to use a persistent cache, you have to check the following options in Lookup Transformation properties tab:

§ Lookup caching enabled
§ Lookup cache persistent

Once you do that, cache file created by Informatica session will *NOT* be deleted from the Cache directory and the same cache file will be used in all the consecutive runs. Advantage of doing this is you need not spend time building the same cache every time the session executes. However if the source data for the lookup changes meanwhile then you must refresh the cache by either of the following two options:

§ Delete the cache file manually from the Cache directory Or,
§ Check the “Re-cache from lookup source” option in the Properties tab of the lookup

If the persistent cache lookup is reusable, then you can share the same lookup in multiple mapping without rebuilding the cache in each one of them. And you can have one additional mapping with re-cache option enabled for this lookup, which you can run whenever you need to refresh the cache file.

Disadvantage of Persistent cache Lookup

Although persistent cache can give you considerable performance and other advantages, it comes with some hazards.

§ Recovering sessions after failure in midway may not be possible. Consider this – you have a typical update-else-insert logic in a target table over which you also have persistent cache lookup. This PC lookup on target is used to determine if a certain record coming from source is already present in target (Update) or not (Insert). Suppose this session got failed after inserting a few record in target. If this was a normal lookup, you could simply restart the session after fixing the cause of the error. But if this is a persistent cache lookup, you can not restart this job directly as because the lookup cache will not be holding the records that got inserted in the first time and as a result the lookup will fail to determine that these records are already existing in target and your mapping will try to insert them once again.

§ Persistent cache gives you performance boost by saving time in building the lookup while session run but it still takes the same time like a normal lookup for using the lookup data during session runtime. It is often observed that persistent cache shared across many sessions creates huge disk level I/O contention when the lookup is actually being used in the sessions. You need to monitor the disk IO performance using “iostat” or “vmstat” (UNIX) if you see huge transformation bottleneck without any apparent reason in sessions using persistent cache lookup.

Effect of choosing connected OR Unconnected Lookup

If all the other factors remain same, choice of connected and unconnected lookup can impact lookup performance in one particular case. In case of a connected lookup, since the lookup is connected in the data flow pipeline so the lookup will be called for each record coming from source, irrespective of the fact whether the data returned from lookup is at all used later in the mapping or not. In case of unconnected lookup, this can be controlled by calling the lookup only when it is really needed.

To illustrate the difference, let’s consider the following example. Suppose you are loading sales data and if the sales is done in one specific geography location (say North America), then you want to populate one additional column in your target table with GST amount (which is based on which American State the sales was made). Suppose one record in the source is like this:

SalesID = 100067345

SalesLocation = CA

Geography = North America

SalesAmount = 100.54

ProductPartNo = PX937

In case of connected lookup, the lookup will be called
for all the records irrespective of the fact whether the sales Geography is in North America or not. In case of unconnected lookup, you can write
something like this inside an Expression Transformation, which will ensure the
lookup is only called when Sales Geography is North America: 

IIF (SALES_GEOGRAPHY = ‘North America’,

:LKP.LKP_GET_GST_PCT_PER_STATE(SALES_LOCATION), 0)

Suppose out of 1,000,000 records, the above condition matches only for 100,000 records (10%). Then you save calling the lookup unnecessarily for 90% of cases.

WHEN TO set Pre-build Lookup Cache OPTION (AND WHEN NOT TO)

Informatica may build the lookup cache even before the first row reaches the Lookup transformation (Pre-build scenario) or it may build the lookup cache only when the first row reaches the lookup transformation (On demand scenario). You can let Informatica decide what it wants to do (pre-build or on demand) or you may explicitly tell Informatica what you want to do. All you need to do is set the “Pre-Build Lookup Cache” option to “Always allowed” under the lookup transformation Properties tab to force Informatica build all the lookup caches beforehand.

So what’s the difference? How does it impact the performance? Generally speaking pre-building all the lookup cache beforehand may give you occasional performance boost as once the source qualifier starts pulling data, the processing of that data need not wait any more for the completion of building of lookup caches.

Performance boost of pre-building lookup caches is more apparent in cases where the source query itself is taking considerable amount of time to return rows so the same time can be simultaneously used to pre-build all the lookup caches instead of waiting for the source to return results and then starting to build other lookup caches on demand.

I said the performance benefit is occasional because there are number of other factors that need to be considered before setting Pre-Build Lookup Cache to Always allowed.

· When Informatica pre-builds the lookup cache, how many caches it can build concurrently depends on various factors including “Additional Concurrent Pipelines for Lookup Cache Creation” property present in session level under “Config Object” tab. I will discuss this in more detail later below.

· One should also check if the database where Informatica is firing the Lookup queries has enough capacity to process the Lookup queries simultaneously without hampering the individual query performance. This depends on various database level properties especially on database TEMP space availability, session level connection restrictions and degree-of-parallelism (DOP)

· There can be scenario where not all the lookups are even required to be called (especially if the lookup is unconnected and called based on a condition – see above in the section “Effect of Choosing Connected or unconnected lookup). There is really no point in pre-building those lookups as those lookups should be built on demand only. This is also the reason why “Pre-Build Lookup Cache” option is available in individual lookup level and not as a generic option in session level

USE OF Additional Concurrent Pipeline for Lookup Cache Creation OPTIONS

To me, this option which is available in session level under “Config Object” tab, is more of a limiting option rather than a performance augmenting option. This option determines how many additional concurrent pipelines Integration service can use while trying to build lookup caches simultaneously. I prefer setting it to “Auto”.

If you set it to Auto, Informatica will decide the value at run time while building the caches concurrently. Below I describe how Informatica will typically behave when you set this to “Auto”. Suppose one mapping has 4 lookup transformations that Informatica decided to build concurrently. It will give a message like below first in the session log:

Enabled using [2 (auto)] additional concurrent pipelines to build lookup caches. 

(Session likely will build or refresh [4] lookup caches; [1] on-demand only)

The above message means that Informatica has started 2 pipelines to handle 2 different lookups already. So this means 2 more lookups are yet to be built. When Informatica starts building those 2 more lookups, based on the situation whether the already allocated pipelines for the first 2 lookups are free by that time or not, it will generate any of the below two messages respectively:

Starting additional concurrent pipeline to build the lookup cache 

needed by Lookup transformation [LKP_XXX_XXX_XXX]

Or

Lookup cache of [LKP_XXX_XXX_XXX], previously started by an additional 

concurrent pipeline, is now needed on demand.

The first message will appear if –

§ Informatica decides that it can “afford” one more additional pipeline and
§ No additional pipeline allocated previously for other lookups is yet free at the time of building this lookup

Now that we understand how additional pipeline behaves, let’s see how we can use this property to augment session performance. As I described before, the best thing to do with this is to set it to “Auto” which will anyway use the maximum possible pipelines when lookups are required to be built concurrently. However if you want to limit the concurrency then you can specify a numeric number there which will mean that no more additional pipelines will be created after it reaches the specified number. If any more additional pipeline is required, then it will wait for any current pipeline to free up first so that Informatica can reuse this. If you set it to 0 (zero), all lookups will be built serially.