January 21, 2015
Here are a few points which will get you started with Informatica Power center Performance Tuning .Some of these tips are very general in nature please consult your project members before implementing them in your projects.
1) Optimize the Input Query if it’s Relational (e.g. Oracle table) source –
- Reduce no.of rows queried by using where conditions instead of Filter/Router transformations later in the mapping. Since you choose fewer rows to start with, your mapping will run faster
- Maker sure appropriate Indexes are defined on necessary columns and analyzed. Indexes must be especially defined on the columns in the ‘where’ clause of your input query.
- Eliminate columns that you do not need for your transformations
- Use ‘Hints’ as necessary.
- Use sort order based on need
Note: For # i results will vary based on how the table columns are Indexed/queried…etc.
2) Use the Filter transformation as close to the SQ Transformation as possible.
3) Use sorted input data for Aggregator or Joiner Transformation as necessary.
4) Eliminate un-used columns and redundant code in all the necessary transformations.
5) Use Local variables as necessary to improve the performance.
6) Reduce the amount of data caching in Aggregator.
7) Use parameterized input query and file for flexibility.
8) Changed memory related settings at workflow/session level as necessary.
9) When use multiple condition columns in Joiner/Lookup transformation make sure to
use numeric data type column as first condition.
10) Use persistent cache if possible in Lookup transformations.
11) Go through the sessions Logs CLOSELY to find out any issues and change accordingly
12) Use overwrite queries in Lookup transformation to reduce the amount of data cached.
13) Make sure the data type and sizes are consistent throughout the mapping as much
14) For Target Loads use Bulk Load as and when possible.
15) For Target Loads use SQL * Load with DIRECT and UNRECOVERABLE option for large volume of data loads.
16) Use Partitioning options as and when possible. This is true for both Informatica and Oracle. For Oracle, a rule of thumb is to have around 10M rows per partition
17) Make sure that there are NO Indexes for all “Pre/Post DELETE SQLs” used in all
18) Use datatype conversions where ever possible
e.g: 1) Use ZIPCODE as Integer instead of Character this improves the speed of the lookup Transformation comparisons.
2) Use the port to port datatype conversions to improve the session performance.
19) Use operators instead of functions
e.g: For concatenation use “||” instead of CONCAT
20) Reduce the amount of data writing to logs for each transformation by setting
log settings to Terse as necessary only.
21) Use Re-usable transformations and Mapplets where ever possible.
22) In Joiners use less no.of rows as Master ports.
23) Perform joins in db rather than using Joiner transformation where ever possible.
January 20, 2015
Don’t you just hate it when you can’t find that one mapping out of the thousand odd mappings present in your repository ??
A best practice is a method or technique that has consistently shown results superior to those achieved with other means, and that is used as a benchmark. In addition, a “best” practice can evolve to become better as improvements are discovered.Following these Informatica Best Practices guidelines , would allow better Repository Management , which would make your Life Easier. Incorporate these practices when you create informatica objects and your life would be much easier:
- There should be a place holder transformation (expression) immediately after the source and one before the target.
- Active transformations that reduce the number of records, should be used as early as possible.
- Connect only the ports that are required in targets to subsequent transformations.
- If a join must be used in the Mapping, select the driving/master table while using joins.
- For generic logic to be used across mappings, create a mapplet and reuse across mappings.
- Replace complex filter expression with a (Y/N) flags. Filter expression will take lesser time to process the flags than the logic.
- Persistent caches should used in look ups if the look up data is not expected to change often.
Naming conventions – name the informatica transformations starting with the first 3 letters in small case indicating the transformation. E.g. : lkp_<name of the lookup> for Look Up, rtr_<name of router> for router transformation etc.
- Naming convention for session, worklet, workflow- s_<name of the session>, wlt_<name of the worklet>, wkf_<name of the workflow>.
- Sessions should be created as re usable to be used in multiple workflows.
- While loading tables for full loads, truncate target table option should be checked.
- Workflow Property “Commit interval” (Default value : 10,000) should be increased for increased for Volumes more than 1 million records.
- Pre-Session command scripts should be used for disabling constraints, building temporary tables, moving files etc. Post-Sessions scripts should be used for rebuilding indexes and dropping temporary tables.
Performance Optimization Best Practices
We often come across situations where Data Transformation Manager(DTM) takes more time to read from Source or when writing in to a Target. Following standards/guidelines can improve the overall performance.
- Use Source Qualifier if the Source tables reside in the same schema
- Make use of Source Qualifier “Filter” properties if the Source type is Relational
- Use flags as integer, as the integer comparison is faster than the string comparison
- Use tables as lesser number of records as master table for joins
- While reading from Flat files, define the appropriate data type instead of reading as String and converting
- Have all ports that are required connected to Subsequent transformations else check whether we can remove these ports
- Suppress ORDER BY using the ‘- – ’ at the end of the query in Lookup transformations
- Minimize the number of Update strategies
- Group by simple columns in transformations like Aggregate, Source qualifier
- Use Router transformation in place of multiple Filter transformations
- Turn Off the Verbose logging while moving the mappings to UAT/Production environment
- For large volume of data drop index before loading and recreate indexes after load
- For large of volume of records Use Bulk load increase the commit interval to a higher value large volume of data
- Set ‘Commit on Target’ in the sessions
These are a few things a beginner should know when he starts coding in Informatica . These Informatica Best Practices guidelines are a must for efficient Repository and overall project management and tracking.