Performance Tuning Overview

The goal of performance tuning is to optimize session performance by eliminating performance bottlenecks. To tune session performance, first identify a performance bottleneck, eliminate it, and then identify the next performance bottleneck until you are satisfied with the session performance. You can use the test load option to run sessions when you tune session performance.

If you tune all the bottlenecks, you can further optimize session performance by increasing the number of pipeline partitions in the session. Adding partitions can improve performance by utilizing more of the system hardware while processing the session.

Because determining the best way to improve performance can be complex, change one variable at a time, and time the session both before and after the change. If session performance does not improve, you might want to return to the original configuration.

Complete the following tasks to improve session performance:

1. Optimize the target. Enables the Integration Service to write to the targets efficiently.

2. Optimize the source. Enables the Integration Service to read source data efficiently.

3. Optimize the mapping. Enables the Integration Service to transform and move data efficiently.

4. Optimize the transformation. Enables the Integration Service to process transformations in a mapping efficiently.

5. Optimize the session. Enables the Integration Service to run the session more quickly.

6. Optimize the grid deployments. Enables the Integration Service to run on a grid with optimal performance.

7. Optimize the Power Center components. Enables the Integration Service and Repository Service to function optimally.

8. Optimize the system. Enables Power Center service processes to run more quickly.

IDENTIFICATION OF BOTTLENECKS

Performance of Informatica is dependant on the performance of its several components like database, network, transformations, mappings, sessions etc. To tune the performance of Informatica, we have to identify the bottleneck first.

Bottleneck may be present in source, target, transformations, mapping, session,database or network. It is best to identify performance issue in components in the order source, target, transformations, mapping and session. After identifying the bottleneck, apply the tuning mechanisms in whichever way they are applicable to the project.

Identify bottleneck in Source

If source is a relational table, put a filter transformation in the mapping, just after source qualifier; make the condition of filter to FALSE. So all records will be filtered off and none will proceed to other parts of the mapping.In original case, without the test filter, total time taken is as follows:-

Total Time = time taken by (source + transformations + target load)

Now because of filter, Total Time = time taken by source

So if source was fine, then in the latter case, session should take less time. Still if the session takes near equal time as former case, then there is a source bottleneck.

Identify bottleneck in Target

If the target is a relational table, then substitute it with a flat file and run the session. If the time taken now is very much less than the time taken for the session to load to table, then the target table is the bottleneck.

Identify bottleneck in Transformation

Remove the transformation from the mapping and run it. Note the time taken.Then put the transformation back and run the mapping again. If the time taken now is significantly more than previous time, then the transformation is the bottleneck.

But removal of transformation for testing can be a pain for the developer since that might require further changes for the session to get into the ‘working mode’.

So we can put filter with the FALSE condition just after the transformation and run the session. If the session run takes equal time with and without this test filter,then transformation is the bottleneck.

Identify bottleneck in sessions

We can use the session log to identify whether the source, target or transformations are the performance bottleneck. Session logs contain thread summary records like the following:-

MASTER> PETL_24018 Thread [READER_1_1_1] created for the read stage of partition point [SQ_test_all_text_data] has completed: Total Run Time =[11.703201] secs, Total Idle Time = [9.560945] secs, Busy Percentage =[18.304876].

MASTER> PETL_24019 Thread [TRANSF_1_1_1_1] created for the transformation stage of partition point [SQ_test_all_text_data] has completed: Total Run Time = [11.764368] secs, Total Idle Time = [0.000000] secs, Busy Percentage = [100.000000].

If busy percentage is 100, then that part is the bottleneck.

Basically we have to rely on thread statistics to identify the cause of performance issues. Once the ‘Collect Performance Data’ option (In session ‘Properties’ tab) is enabled, all the performance related information would appear in the log created by the session.

Optimizing the Bottleneck’s

1. If the source is a flat file, ensure that the flat file is local to the Informatica server. If source is a relational table, then try not to use synonyms or aliases.

2. If the source is a flat file, reduce the number of bytes (By default it is 1024 bytes per line) the Informatica reads per line. If we do this, we can decrease the Line Sequential Buffer Length setting of the session properties.

3. If possible, give a conditional query in the source qualifier so that the records are filtered off as soon as possible in the process.

4. In the source qualifier, if the query has ORDER BY or GROUP BY, then create an index on the source table and order by the index field of the source table.

PERFORMANCE TUNING OF TARGETS

If the target is a flat file, ensure that the flat file is local to the Informatica server. If target is a relational table, then try not to use synonyms or aliases.

1. Use bulk load whenever possible.

2. Increase the commit level.

3. Drop constraints and indexes of the table before loading.

PERFORMANCE TUNING OF MAPPINGS

Mapping helps to channel the flow of data from source to target with all the transformations in between. Mapping is the skeleton of Informatica loading process.

1. Avoid executing major sql queries from mapplets or mappings.

2. Use optimized queries when we are using them.

3. Reduce the number of transformations in the mapping. Active transformations like rank, joiner, filter, aggregator etc should be used as less as possible.

4. Remove all the unnecessary links between the transformations from mapping.

5. If a single mapping contains many targets, then dividing them into separate mappings can improve performance.

6. If we need to use a single source more than once in a mapping, then keep only one source and source qualifier in the mapping. Then create different data flows as required into different targets or same target.

7. If a session joins many source tables in one source qualifier, then an optimizing query will improve performance.

8. In the sql query that Informatica generates, ORDERBY will be present. Remove the ORDER BY clause if not needed or at least reduce the number of column names in that list. For better performance it is best to order by the index field of that table.

9. Combine the mappings that use same set of source data.

10. On a mapping, field with the same information should be given the same type and length throughout the mapping. Otherwise time will be spent on field conversions.

11. Instead of doing complex calculation in query, use an expression transformer and do the calculation in the mapping.

12. If data is passing through multiple staging areas, removing the staging area will increase performance.

13. Stored procedures reduce performance. Try to keep the stored procedures simple in the mappings.

14. Unnecessary data type conversions should be avoided since the data type conversions impact performance.

15. Transformation errors result in performance degradation. Try running the mapping after removing all transformations. If it is taking significantly less time than with the transformations, then we have to fine-tune the transformation.

16. Keep database interactions as less as possible.

PERFORMANCE TUNING OF SESSIONS

A session specifies the location from where the data is to be taken, where the transformations are done and where the data is to be loaded. It has various properties that help us to schedule and run the job in the way we want.

1. Partition the session: This creates many connections to the source and target, and loads data in parallel pipelines. Each pipeline will be independent of the other. But the performance of the session will not improve if the number of records is less. Also the performance will not improve if it does updates and deletes. So session partitioning should be used only if the volume of data is huge and the job is mainly insertion of data.

2. Run the sessions in parallel rather than serial to gain time, if they are independent of each other.

3. Drop constraints and indexes before we run session. Rebuild them after the session run completes. Dropping can be done in pre session script and Rebuilding in post session script. But if data is too much, dropping indexes and then rebuilding them etc. will be not possible. In such cases, stage all data, pre-create the index, use a transportable table space and then load into database.

4. Use bulk loading, external loading etc. Bulk loading can be used only if the table does not have an index.

5. In a session we have options to ‘Treat rows as ‘Data Driven, Insert, Update and Delete’. If update strategies are used, then we have to keep it as ‘Data Driven’. But when the session does only insertion of rows into target table, it has to be kept as ‘Insert’ to improve performance.

6. Increase the database commit level (The point at which the Informatica server is set to commit data to the target table. For e.g. commit level can be set at every every 50,000 records)

7. By avoiding built in functions as much as possible, we can improve the performance. E.g. For concatenation, the operator ‘||’ is faster than the function CONCAT (). So use operators instead of functions, where possible. The functions like IS_SPACES (), IS_NUMBER (), IFF (), DECODE () etc. reduce the performance to a big extent in this order. Preference should be in the opposite order.

8. String functions like substring, ltrim, and rtrim reduce the performance. In the sources, use delimited strings in case the source flat files or use varchar data type.

9. Manipulating high precision data types will slow down Informatica server. So disable ‘high precision’.

10. Localize all source and target tables, stored procedures, views, sequences etc. Try not to connect across synonyms. Synonyms and aliases slow down the performance.

DATABASE OPTIMISATION

To gain the best Informatica performance, the database tables, stored procedures and queries used in Informatica should be tuned well.

1. If the source and target are flat files, then they should be present in the system in which the Informatica server is present.

2. Increase the network packet size.

3. The performance of the Informatica server is related to network connections.Data generally moves across a network at less than 1 MB per second, whereas a local disk moves data five to twenty times faster. Thus network connections often affect on session performance. So avoid network connections.

4. Optimize target databases.

PERFORMANCE TUNING OF LOOKUP TRANSFORMATIONS

Lookup transformations are used to lookup a set of values in another table.Lookups slows down the performance.

1. To improve performance, cache the lookup tables. Informatica can cache all the lookup and reference tables; this makes operations run very fast. (Meaning of cache is given in point 2 of this section and the procedure for determining the optimum cache size is given at the end of this document.)

2. Even after caching, the performance can be further improved by minimizing the size of the lookup cache. Reduce the number of cached rows by using a sql override with a restriction.

Cache: Cache stores data in memory so that Informatica does not have to read the table each time it is referenced. This reduces the time taken by the process to a large extent. Cache is automatically generated by Informatica depending on the marked lookup ports or by a user defined sql query.

Example for caching by a user defined query: -

Suppose we need to lookup records where employee_id=eno.

‘employee_id’ is from the lookup table, EMPLOYEE_TABLE and ‘eno’ is the

input that comes from the from the source table, SUPPORT_TABLE.

We put the following sql query override in Lookup Transform

‘select employee_id from EMPLOYEE_TABLE’

If there are 50,000 employee_id, then size of the lookup cache will be 50,000.

Instead of the above query, we put the following:-

‘select emp employee_id from EMPLOYEE_TABLE e, SUPPORT_TABLE s

where e. employee_id=s.eno’

If there are 1000 eno, then the size of the lookup cache will be only 1000.But here the performance gain will happen only if the number of records in SUPPORT_TABLE is not huge. Our concern is to make the size of the cache as less as possible.

3. In lookup tables, delete all unused columns and keep only the fields that are used in the mapping.

4. If possible, replace lookups by joiner transformation or single source qualifier.Joiner transformation takes more time than source qualifier transformation.

5. If lookup transformation specifies several conditions, then place conditions that use equality operator ‘=’ first in the conditions that appear in the conditions tab.

6. In the sql override query of the lookup table, there will be an ORDER BY clause. Remove it if not needed or put fewer column names in the ORDER BY list.

7. Do not use caching in the following cases: -

-Source is small and lookup table is large.

-If lookup is done on the primary key of the lookup table.

8. Cache the lookup table columns definitely in the following case: -

-If lookup table is small and source is large.

9. If lookup data is static, use persistent cache. Persistent caches help to save and reuse cache files. If several sessions in the same job use the same lookup table, then using persistent cache will help the sessions to reuse cache files. In case of static lookups, cache files will be built from memory cache instead of from the database, which will improve the performance.

10. If source is huge and lookup table is also huge, then also use persistent cache.

11. If target table is the lookup table, then use dynamic cache. The Informatica server updates the lookup cache as it passes rows to the target.

12. Use only the lookups you want in the mapping. Too many lookups inside a mapping will slow down the session.

13. If lookup table has a lot of data, then it will take too long to cache or fit in memory. So move those fields to source qualifier and then join with the main table.

14. If there are several lookups with the same data set, then share the caches.

15. If we are going to return only 1 row, then use unconnected lookup.

16. All data are read into cache in the order the fields are listed in lookup ports. If we have an index that is even partially in this order, the loading of these lookups can be speeded up.

17. If the table that we use for look up has an index (or if we have privilege to add index to the table in the database, do so), then the performance would increase both for cached and un cached lookups.

PUSH DOWN OPTIMISATION

You can push transformation logic to the source or target database using pushdown optimization. When you run a session configured for pushdown optimization, the Integration Service translates the transformation logic into SQL queries and sends the SQL queries to the database. The source or target database executes the SQL queries to process the transformations.

The amount of transformation logic you can push to the database depends on the database, transformation logic, and mapping and session configuration. The Integration Service processes all transformation logic that it cannot push to a database.

Use the Pushdown Optimization Viewer to preview the SQL statements and mapping logic that the Integration Service can push to the source or target database. You can also use the Pushdown Optimization Viewer to view the messages related to pushdown optimization.

The following figure shows a mapping containing transformation logic that can be pushed to the source database:

This mapping contains an Expression transformation that creates an item ID based on the store number 5419 and the item ID from the source. To push the transformation logic to the database, the Integration Service generates the following SQL statement:

INSERT INTO T_ITEMS(ITEM_ID, ITEM_NAME, ITEM_DESC) SELECT CAST((CASE WHEN 5419 IS NULL THEN '' ELSE 5419 END) + '_' + (CASE WHEN ITEMS.ITEM_ID IS NULL THEN '' ELSE ITEMS.ITEM_ID END) AS INTEGER), ITEMS.ITEM_NAME, ITEMS.ITEM_DESC FROM ITEMS2 ITEMS

The Integration Service generates an INSERT SELECT statement to retrieve the ID, name, and description values from the source table, create new item IDs, and insert the values into the ITEM_ID, ITEM_NAME, and ITEM_DESC columns in the target table. It concatenates the store number 5419, an underscore, and the original ITEM ID to get the new item ID.

Pushdown Optimization Types

You can configure the following types of pushdown optimization:

• Source-side pushdown optimization. The Integration Service pushes as much transformation logic as possible to the source database.
• Target-side pushdown optimization. The Integration Service pushes as much transformation logic as possible to the target database.
• Full pushdown optimization. The Integration Service attempts to push all transformation logic to the target database. If the Integration Service cannot push all transformation logic to the database, it performs both source-side and target-side pushdown optimization.

Running Source-Side Pushdown Optimization Sessions

When you run a session configured for source-side pushdown optimization, the Integration Service analyzes the mapping from the source to the target or until it reaches a downstream transformation it cannot push to the source database.

The Integration Service generates and executes a SELECT statement based on the transformation logic for each transformation it can push to the database. Then, it reads the results of this SQL query and processes the remaining transformations.

Running Target-Side Pushdown Optimization Sessions

When you run a session configured for target-side pushdown optimization, the Integration Service analyzes the mapping from the target to the source or until it reaches an upstream transformation it cannot push to the target database. It generates an INSERT, DELETE, or UPDATE statement based on the transformation logic for each transformation it can push to the target database. The Integration Service processes the transformation logic up to the point that it can push the transformation logic to the database. Then, it executes the generated SQL on the Target database.

Running Full Pushdown Optimization Sessions

To use full pushdown optimization, the source and target databases must be in the same relational database management system. When you run a session configured for full pushdown optimization, the Integration Service analyzes the mapping from the source to the target or until it reaches a downstream transformation it cannot push to the target database. It generates and executes SQL statements against the source or target based on the transformation logic it can push to the database.

When you run a session with large quantities of data and full pushdown optimization, the database server must run a long transaction. Consider the following database performance issues when you generate a long transaction:

• A long transaction uses more database resources.
• A long transaction locks the database for longer periods of time. This reduces database concurrency and increases the likelihood of deadlock.
• A long transaction increases the likelihood of an unexpected event. To minimize database performance issues for long transactions, consider using source-side or target-side pushdown optimization.

Rules and Guidelines for Functions in Pushdown Optimization

Use the following rules and guidelines when pushing functions to a database:

• If you use ADD_TO_DATE in transformation logic to change days, hours, minutes, or seconds, you cannot push the function to a Teradata database.
• When you push LAST_DAY () to Oracle, Oracle returns the date up to the second. If the input date contains sub seconds, Oracle trims the date to the second.
• When you push LTRIM, RTRIM, or SOUNDEX to a database, the database treats the argument (' ') as NULL, but the Integration Service treats the argument (' ') as spaces.
• An IBM DB2 database and the Integration Service produce different results for STDDEV and VARIANCE. IBM DB2 uses a different algorithm than other databases to calculate STDDEV and VARIANCE.
• When you push SYSDATE or SYSTIMESTAMP to the database, the database server returns the timestamp in the time zone of the database server, not the Integration Service.
• If you push SYSTIMESTAMP to an IBM DB2 or a Sybase database, and you specify the format for SYSTIMESTAMP, the database ignores the format and returns the complete time stamp.
• You can push SYSTIMESTAMP (‘SS’) to a Netezza database, but not SYSTIMESTAMP (‘MS’) or SYSTIMESTAMP (‘US’).
• When you push TO_CHAR (DATE) or TO_DATE () to Netezza, dates with sub second precision must be in the YYYY-MM-DD HH24: MI: SS.US format. If the format is different, the Integration Service does not push the function to Netezza.

TUNING Look-Up Transformation

To me, look-up is the single most important (and difficult) transformation that we need to consider while tuning performance of Informatica jobs. The choice and use of correct type of Look-Up can drastically vary the session performance in Informatica. So let’s delve deeper into this.

To cache or not to cache?

When you cache a look-up, Informatica fires the lookup query to database and brings in the entire set of data from the database to the Cache file directory under Informatica server. Informatica then uses this data whenever this particular lookup is called. By doing this Informatica saves the time (and effort) to go back to database every time the lookup is called in the mapping.

In case of an un-cached or non-cached lookup, the opposite thing happens. Here Informatica goes to database to fetch data every time the lookup is called inside the mapping. How often the lookup is called depends on number of records coming from source (exactly equalling to number of records in case of a connected lookup)

There are couple of things that you need to consider while opting for any particular type of lookup. I tend to follow these general guidelines:

I. 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.

II. If the lookup table is small (less than 2 million), it’s generally a good idea to cache the lookup table

Above guidelines are based on the following mathematical approach that suggests when to use cache lookup and when to use uncached lookup.

N = Number of records coming from source (For the purpose of simplicity, let’s assume N also equals to the number of times the lookup will be called)
M = Number of records retrieved from the Lookup query
t = time required to retrieve a single record from database based on the Lookup query
T = time required to retrieve all the records from database based on the Lookup query = M * t
L = Time required to build the lookup index cache for cached lookup = f(N)
C = Network Time required to do one round trip for data fetch from database

In case of an uncached lookup, total time required will be:
T _uncached = N * t + N * C
In case of a cached lookup, total time required to build the cache will be:
T_cached = T + L + C

In the above equation we can put L = f(N) and since C is generally very small number, we can ignore C and rewrite the above equation as –
T_cached = T + f(N)

Now if we assume that the database table has INDEX defined upon it and the index is being used while retrieving records from database then, we can further assume –
T = M * t

Replacing the above value in our earlier equation for T_cached we get –
T_cached= M * t + f(N)

We should use uncached lookup if –
T_cached > T_uncached
=> M * t + f(N) > N * t + N * C
Dividing all sides by N, we get – C + t < (M/N)*t + f(N)/N

For a given Informatica and database setup, C, t & f(N) are fixed.
So the above equation is more likely to be true if (M/N) ratio is on higher side, i.e. N << M – that is – number of records coming from source is considerably less than number of records present in the lookup table (Refer back to point no. 1 in the discussion above).

Similarly, if N >> M – that is number of records coming from source is considerably high than the number of records present in lookup table, then we should use cached lookup.

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 –

I. ORDER BY clause may create additional processing overhead on database. So ensure that in the SELECT list of the lookup query, you do not include any column that are redundant because otherwise those columns will also be included in ORDER BY also

II. Do not completely suppress the ORDER BY.

I recommend you always perform ORDER BY at least on the columns which are being used in the joining condition. This is because 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.

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

WARNING! Found unsorted input when building Lookup cache of [XXX_XXX_XXX] (Current number of entries in the index cache = 1). Continue building the cache by insertion. This will cause it to take longer and the index cache may require more space.

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. Following article describes the detail process on how to implement persistent cache in Informatica session:

How to implement 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.

Also note that there are some disadvantages of using persistent cache lookup. For details on that, please check the below article:

Benefit and disadvantage of informatica persistent cache lookup

Without going much deeper in to the disadvantage part, I would like to mention that there is one particular disadvantage that you must take into account while opting for persistent cache option for big lookup tables. If the cache file size of your lookup table is more than 2GB, most likely Informatica will create multiple cache files for one lookup wherein maximum file size for each file will be 2GB. If those are data cache files, Informatica will name them as .dat1, .dat2, .dat3 … etc. and corresponding index cache files will be named as .idx1, .idx2, .idx3 … etc.

Also note that in many flavors of UNIX (e.g. HP-UX 11i), NOLARGEFILES is a default option for the file system. This option prevents applications or users to create file larger than 2GB. You can check whether LARGEFILE option is enabled in your server by issuing the following command:

getconf FILESIZEBITS /mountpoint_name

However, please note that irrespective of the fact whether LARGEFILE option is enabled or disabled, Informatica will not create cache sized above 2GB (This is true for both 32-bit and 64-bit versions of Informatica)

My personal opinion is breaking one single data or index cache file into multiple files may slow down the lookup performance. Hence if your lookup cache size is more than 2GB then if possible consider the option of joining the lookup source table in the database level itself instead of building lookup cache.

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 Northa 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

Tuning JOINER Transformation

Joiner transformation allows you to join two heterogeneous sources in the Informatica mapping. You can use this transformation to perform INNER and OUTER joins between two input streams.

For performance reasons, I recommend you ONLY use JOINER transformation if any of the following condition is true –

I. Data sources are not relational in nature (joining two flat files)

II. Data sources are heterogeneous in nature (e.g. Flat File and Database)

III. Data sources are coming from different relational connections

IV. One or more column(s) used in the joining condition(s) of the JOINER is derived in the mapping

Except the above mentioned conditions, it is better to use database side joins. To know why, please read –

Database performs join faster than Informatica

Although the article in the above link is particular about Oracle database but the conclusion will hold true in case of most of the other databases.

However, if you have to use the joiner transformation, following are the additional points/actions that you must consider:

· When joining between two data sources, treat the data source containing less number of records as Master. This is because the Cache size of the Joiner transformation depends on master data (unless sorted input with the same source is used).

· Ensure that both the master and detail input sources are sorted and both “Sorted Input” and “Master Sort Order” ports are checked and set

· Consider using cache partitioning for Joiner transformation if you have partition option available under your Informatica license. More details on this topic later

· Check if the Data and Index cache sizes can be configured. More details on this next

Understanding Joiner Cache

Joiner Transformation needs a space to store the cache for the data and index. This cache can be either memory cache (stored in RAM) or disk cache (stored in hard drive disks) or both depending on various factors which I won’t discuss now. Obviously the memory cache is much faster than the disk cache. So enough system memory should be available to Informatica for faster Join operation. One can actually configure the amount of memory for Joiner data cache and index cache by the following two options under Joiner Transformation Properties:

I. Joiner Data Cache Size

II. Joiner Index Cache Size

If you click on these properties under the “Mapping” tab of the session, you can access “Joiner-Cache Calculator” which is a small tool inbuilt into the PowerCenter Manager for calculating the required amount of cache sizes for the joining operation. You can use the values suggested by this calculator for joiner data and index cache or you can leave them as AUTO. If you do not leave them as Auto and input some values in those boxes, you must ensure that the allocated memory is available while the session executes. Otherwise the session will fail.

I prefer to leave Joiner Data and Index Cache sizes parameters to Auto and set the maximum values for the auto memory attributes in the session level. To know why, please read on.

Partitioning the Joiner cache

If the session containing the joiner is already partitioned, then one can take the advantage of cache partitioning for the Joiner. As the name suggests, the joiner cache itself gets divided in different partitions in this case. The benefit of this is Informatica accesses the cache in parallel for each partition which is faster than accessing the full cache in serial.

In order to achieve this cache partition, you need to create a partition point on the Joiner Transformation by choosing the “Add Partition Point” option in Session level under the Mapping::Partitions tab. Then you can edit that partition point to add more than one Hash Auto-key partitions to it. Suppose if you add 4 hash auto key partitions to the Joiner transformation then, by default, Informatica will automatically add 4 Pass-Through partitions in the source qualifier transformations of *both* the master and detail pipelines.

The benefit of choosing Hash Auto Keys partition in the Joiner transformation is – you need NOT explicitly tell Informatica how to divide the incoming data to individual partitions in Joiner level. You set it to Hash Auto Keys and you forget it, Informatica will take care for the rest.

However, as soon as you add number of Hash Auto Keys partition to the Joiner level, your source qualifiers also get Pass-Through partitioned. Here you may override the Source Qualifier query and specify individual SQL queries for each partition in the source qualifier transformation level. Supposing your original source side SQL query is like below:

SELECT AccountID, TransactionType, TransactionAmount 

FROM Transactions

You can override the above query for each partition level like below:

Partition #1:

SELECT AccountID, TransactionType, TransactionAmount 

FROM Transactions  WHERE AccountType = ‘SAVINGS’

Partition #2:

SELECT AccountID, TransactionType, TransactionAmount 

FROM Transactions WHERE AccountType = ‘CURRENT’

Partition #3:

SELECT AccountID, TransactionType, TransactionAmount 

FROM Transactions  WHERE AccountType = ‘CHECK-IN’

Partition #4:

SELECT AccountID, TransactionType, TransactionAmount 

FROM Transactions  WHERE AccountType = ‘DEMAT’

The above method ensures that each of your source qualifier partition is populated with different set of data.

Alternatively you may also change the partition type in source qualifier level from Pass-Through to “Key range” and specify start and end range of values for each partition. You can also change the partition type to “Database Partitioning” if your source database is Oracle or DB2.

Another important point to note here is – if you add SORTER transformation before Joiner (which you should always do if your data is not already sorted from source) – then you should also consider creating partition points and adding same number of partitions to the sorter transformation. If the partition type at the sorter level is Hash Auto Key, then you need not add any partition point in the Joiner Transformation level

Based on whether your joiner data is sorted or not and the cache is partitioned or not, different number of cache(s) will be created by Informatica as shown below:

	Number of Cache(s) in Disk	Number of Cache(s) in Memory
Data Sorted	Only one	Equal to number of partitions
Data Un-sorted/ Not Partitioned	Only one	Only one
Data Un-sorted/ Partitioned	Equal to number of partitions	Equal to number of partitions

So, this is all about tuning a joiner transformation.

Tuning Aggregator Transformation

Like Joiner, the basic rule for tuning aggregator is to avoid aggregator transformation altogether unless –

I. You really can not do the aggregation in the source qualifier SQL query (e.g. Flat File source)

II. Fields used for aggregation are derived inside the mapping

If you have to do the aggregation using Informatica aggregator, then ensure that all the columns used in the group by are sorted in the same order of group by and “Sorted Input” option is checked in the aggregator properties. Ensuring the input data is sorted is absolutely must in order to achieve better performance and we will soon know why.

Other things that need to be checked to increase aggregator performance are –

I. Check if “Case-Sensitive String Comparison” option is really required. Keeping this option checked (default) slows down the aggregator performance

II. Enough memory (RAM) is available to do the in memory aggregation. See below section for details.

III. Aggregator cache is partitioned

How to (and when to) set aggregator Data and Index cache size

As I mentioned before also, my advice is to leave the “Aggregator Data Cache Size” and “Aggregator Index Cache Size” options as Auto (default) in the transformation level and if required, set either of the followings in the session level (under “Config Object” tab) to allow Informatica allocate enough memory automatically for the transformation:

I. Maximum Memory Allowed For Auto Memory Attributes

II. Maximum Percentage of Total Memory Allowed For Auto Memory Attributes

However if you do have to set Data Cache/ Index Cache size yourself, please note that the value you set here is actually RAM memory requirement (and not disk space requirement) and hence, your mapping will fail if Informatica can not allocate the entire memory in RAM at the session initiation. And yes, this can happen often because you never know what other jobs are running in the server and what amount of RAM other jobs are really occupying while you run this job.

Having understood the risk, let’s now see the benefit of manually configuring the Index and Data Cache sizes. If you leave the index and data cache sizes to auto then if Informatica does not get enough memory during session run time, your job will not fail, instead Informatica will page-out the data to hard disk level. Since I/O performance of hard disk drive is 1000~ times slower than RAM, paging out to hard disk drive will have performance penalty. So by setting data and index cache size manually you can ensure that Informatica block this memory in the beginning of session run so that the cache is not paged-out to disk and the entire aggregation actually take place in RAM. Do this at your own risk.

Manually configuring index and data cache sizes can be beneficial if ensuring consistent session performance is your highest priority compared to session stability and operational steadiness. Basically you risk your operations (since it creates high chance of session failure) to obtain optimized performance.

The best way to determine the data and index cache size(s) is to check the session log of already executed session. Session log clearly shows these sizes in bytes. But this size depends on the row count. So keep some buffer (around 20% in most cases) on top of these sizes and use those values for the configuration.

Other way to determine Index and Data Cache sizes are, of course, to use the inbuilt Cache-size calculator accessible in session level.

Fig. Aggregator - Cache Calculator

Using the Informatica Aggregator cache size calculator is a bit difficult (and lot inaccurate). The reason is to calculate cache size properly you will need to know the number of groups that the aggregator is going to process. The definition of number of groups is as below:

No. of Groups = Multiplication of cardinality values of each group by column

This means, suppose you group by store and product, and there are total 150 distinct stores and 10 distinct products, then no. of groups will be 150 X 10 = 1500.

This is inaccurate because, in most cases you can not ascertain how many distinct stores and product data will come on each load. You might have 150 stores and 10 products, but there is no guarantee that all the product will come on all the load. Hence the cache size you determine in this method is quite approximate.

You can, however, calculate the cache size in both the two methods discussed here and take the max of the values to be in safer side.