PERFORMANCE TUNING

64. What are the different levels at which performance improvement can be performed in Informatica?

 65.What Can We Do To Improve The Performance Of Informatica Aggregator Transformation?

• records are sorted before passing to the aggregator and “sorted input” option under aggregator properties is checked.
• The record set should be sorted on those columns that are used in Group By operation.
• It is often a good idea to sort the record set in database level e.g. inside a source qualifier transformation, unless there is a chance that already sorted records from source qualifier can again become unsorted before reaching aggregator.

66.How The Informatica Server Increases The Session Performance Through Partitioning The Source?

• For a relational sources informatica server creates multiple connections for each partition of a single source and extracts separate range of data for each connection.
• Informatica server reads multiple partitions of a single source concurrently. Similarly for loading also informatica server creates multiple connections to the target and loads partitions of data concurrently.
• For XML and file sources, informatica server reads multiple files concurrently. For loading the data informatica server creates a separate file for each partition (of a source file). You can choose to merge the targets.

67. How can we increase Session Performance

•  Minimum log (Terse)
•  Partitioning source data
•  Performing ETL for each partition, in parallel. (For this, multiple CPUs are needed)
•  Adding indexes
•  Changing commit Level
•  Using Filter transformation to remove unwanted data movement
•  Increasing buffer memory, when large volume of data
•  Multiple lookups can reduce the performance. Verify the largest lookup table and tune the expressions.
•  In session level, the causes are small cache size, low buffer memory and small commit interval
•  Run concurrent sessions
•  Partition session (Power center)
•  Tune Parameter – DTM buffer pool, Buffer block size, Index cache size, data cache size, Commit Interval, Tracing level (Normal, Terse, Verbose Initialization, Verbose Data)
•  The session has memory to hold 83 sources and targets. If it is more, then DTM can be increased.
•  The Informatica server uses the index and data caches for Aggregate, Rank, Lookup and Joiner transformation.
•  Remove Staging area
•  Tune off Session recovery
•  Reduce error tracing

The server stores the transformed data from the above transformation in the data cache
before returning it to the data flow. It stores group information for those transformations in index cache. If the allocated data or index cache is not large enough to store the date, the server stores the data in a temporary disk file as it processes the session data. Each time the server pages to the disk the performance slows. This can be seen from the counters. Since generally data cache is larger  than the index cache, it has to be more than the index.

At system level,

•  WIN NT/2000-Use the task manager
•  UNIX: VMSTART, IOSTART

68.Hierarchy of optimization

•  Target
•  Source
•  Mapping
•  Session
•  System

Optimizing Target Databases:

•  Drop indexes /constraints
•  Increase checkpoint intervals
•  Use bulk loading /external loading
•  Turn off recovery
•  Increase database network packet size

Source level

•  Optimize the query (using group by, group by)
•  Use conditional filters
•  Connect to RDBMS using IPC protocol

Mapping

•  Optimize data type conversions
•  Eliminate transformation errors
•  Optimize transformations/ expressions

Session

•  Concurrent batches
•  Partition sessions
•  Reduce error tracing
• Tune session parameters

System

•  Improve network speed
•  Use multiple preservers on separate systems
•  Reduce paging

69. How do you handle performance issues in Informatica? Where can you monitor the performance?

There are several aspects to the performance handling .Some of them are:-

•  Source tuning
•  Target tuning
•  Repository tuning
• Session performance tuning
•  Incremental Change identification in source side.
•  Software, hardware (Use multiple servers) and network tuning.
•  Bulk Loading
•  Use the appropriate transformation.

To monitor this

•  Set performance detail criteria
•  Enable performance monitoring
•  Monitor session at runtime &/ or Check the performance monitor file .

70. What are performance counters?

• The performance details provide that help you understand the session and mapping efficiency. Each Source Qualifier, target definition, and individual transformation appears in the performance details, along with that display performance information about each transformation
• All transformations have some basic that indicates the number of input rows, output rows, and error rows.Source Qualifiers, Normalizes, and targets have additional that indicates the efficiency of data moving into and out of buffers.
• You can use these to locate performance  bottlenecks.
• Some transformations have specific to their functionality. For example, each Lookup transformation has an indicator that indicates the number of rows stored in the lookup cache. When you read performance details, the first column displays the transformation name as it appears in the mapping, the second column contains the name, and the third column holds the resulting number or efficiency percentage.
• When you partition a source, the Informatica Server generates one set of for each partition.
• The following performance illustrate two partitions for an Expression
  transformation:
  Transformation Counter Value
   EXPTRANS [1]
  o Expression_input rows 8
  o Expression_output rows 8
   EXPTRANS [2]
  o Expression_input rows 16
  o Expression_output rows 16

Note: When you partition a session, the number of aggregate or rank input rows may be different from the number of output rows from the previous transformation.

71.Explain how the performance of joiner transformationcan be increased.

• Perform joins in a database when possible.
  In some cases, this is not possible, such as joining tables from two different databases or flat file systems. To perform a join in a database, we can use the following options:
  Create and Use a pre-session stored procedure to join the tables in a database.
  Use the Source Qualifier transformation to perform the join.

• Join sorted data when possible
• For an unsorted Joiner transformation, designate the source with fewer rows as the master source.
• For a sorted Joiner transformation, designate the source with fewer duplicate key values as the master source

73. How are indexes created after completing the load process?

• For the purpose of creating indexes after the load process, command tasks at session level can be used.
• Index creating scripts can be brought in line with the session’s workflow or the post session implementation sequence.
• Moreover this type of index creation cannot be controlled after the load process at transformation level.

74. Which one is faster Connected or Unconnected Lookup?

• There can be some very specific situation where unconnected lookup may add some performance benefit on total execution.
• If you are calling the “Unconnected lookup” based on some condition(e.g. calling it from an expression transformation only when some specific condition is met – as opposed to a connected lookup which will be called anyway) then you might save some “calls” to the unconnected lookup, thereby marginally improving the performance.
• The improvement will be more apparent if your data volume is really huge. Keep the “Pre-build Lookup Cache” option set to “Always disallowed” for the lookup, so that you can ensure that the lookup is not even cached if it is not being called, although this technique has other disadvantages, check
  http://www.dwbiconcepts.com/etl/14-etl-informatica/46-tuning-informatica-lookup.html , especially the  points under following subheadings:
  – Effect of choosing connected OR Unconnected Lookup, and
  – WHEN TO set Pre-build Lookup Cache OPTION (AND WHEN NOT TO)

75. Scenario Implementation 1
What would be the best approach to update a huge table (more than 200 million records) using Informatica.The table does not contain any primary key. However there are a few indexes defined on it. The target table is partitioned. On the other hand the source table contains only a few records (less than a thousand) that will go to the target and update the same. Is there any better approach than just doing it by an update strategy transformation?

• Since the target busy percentage is 99.99% it is very clear that the bottleneck is on the target. So we need tweak the target.
•  Since the target tale is partitioned on time_id, you need to include in the WHERE clause of the SQL fired by Informatica. For that you can define the time_id column as primary key in the target definition. With this your update query will have the time_id in the where clause.
•  With Informatica update strategy, it fires update sql for every row which is marked for update by update strategy. To avoid multiple update statements you can INSERT all the records which is meant to be UPDATE into a temporary table. Then use a correlated sql to update the records in the actual table (200M table). This query can be fires as a post session SQL.
• Please see the sample SQL UPDATE TGT_TABLE U SET (U.COLUMNS_LIST /*Column List to be updated*/) = (SELECT I.COLUMNS_LIST /*Column List to be updated*/ FROM UPD_TABLE I WHERE I.KEYS = U.KEYS AND I.TIME_ID = U.TIME_ID)
  WHERE EXISTS (SELECT 1 FROM UPD_TABLE I WHERE I.KEYS = U.KEYS AND I.TIME_ID = U.TIME_ID) TGT_TABLE –
• Actual table with 200M records UPD_TABLE – Table with records meant for UPDATE (1K record) We need to make sure that your indexes are up to date and stats are collected. Since this is more to be done with DB performance, you may need the help of DBA as well to check the DB throughput, SQL cost etc Hope this will help you.

76. How do you identify the bottlenecks in Mappings?

Identifying Targets Bottlenecks:

The most common performance bottleneck occurs when the informatica server writes to a target database.You can identify target bottleneck by configuring the session to write to a flat file target. If the session performance increases significantly when you write to a flat file, you have a target bottleneck.

Tasks to be performed to increase performance:

• •  Drop or Disable index or constraints
  •  Perform bulk load (Ignores Database log)
  •  Increase commit interval (Recovery is compromised)
  •  Tune the database for RBS, Dynamic Extension etc.,
  •  Increase checkpoint intervals.
  •  Use external loading.
  • Increase database network packet size.
  • Optimize target databases.

Identifying Sources Bottlenecks:

• • Sources – Set a filter transformation after each SQ and see the records are not through. If the time taken is same then there is a problem.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 sourceSo 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.
  • You can also identify the Source problem by Read Test Session – where we copy the mapping with sources, SQ and remove all transformations and connect to file target. If the performance is same then there is a Source bottleneck.
  • Using database query – Copy the read query directly from the log. Execute the query against the source database with a query tool. If the time it takes to execute the query and the time to fetch the first row are significantly different, then the query can be modified using optimizer hints.

Tasks to be performed to increase performance:

• •  Optimize Queries using hints.
  •  Use indexes wherever possible.

Identifying Mapping Bottlenecks:

If both Source and target are OK then problem could be in mapping. Add a filter transformation before target and if the time is the same then there is a problem. (OR) Look for the performance monitor in the Sessions property sheet and view the counters.

Tasks to be performed to increase performance:

•  If High error rows and rows in lookup cache indicate a mapping bottleneck.
•  Optimize Single Pass Reading:

•  Optimize Lookup transformation :

o Caching the lookup table: When caching is enabled the Informatica server caches the lookup table and queries the cache during the session. When this option is not enabled the server queries the lookup table on a row-by row basis. Static, Dynamic, Shared, Un-shared and Persistent cache

o Optimizing the lookup condition: Whenever multiple conditions are placed, the condition with equality sign should take precedence.

o Indexing the lookup table: The cached lookup table should be indexed on order by columns. The session log contains the ORDER BY statement The un-cached lookup since the server issues a SELECT statement for each row passing into lookup transformation, it is better to index the lookup table on the columns in the condition

• Optimize Filter transformation:
  • You can improve the efficiency by filtering early in the data flow. Instead of using a filter transformation halfway through the mapping to remove a sizable amount of data.
  • Use a source qualifier filter to remove those same rows at the source, If not possible to move the filter  into SQ, move the filter transformation as close to the source qualifier as possible to remove unnecessary data early in the data flow.

• Optimize Aggregate transformation:
  •  Group by simpler columns. Preferably numeric columns.
  •  Use Sorted input. The sorted input decreases the use of aggregate caches. The server assumes all input data are sorted and as it reads it performs aggregate calculations.
  •  Use incremental aggregation in session property sheet.
• Optimize Seq. Generator transformation:
  • Try creating a reusable Seq. Generator transformation and use it in multiple mappings
  •  The number of cached value property determines the number of values the  nformatica server caches at one time.
• Optimize Expression transformation:
  •  Factoring out common logic o Minimize aggregate function calls.
  •  Replace common sub-expressions with local variables.
  •  Use operators instead of functions.

The best time in the development cycle is after system testing. Focus on mapping-level optimization only after optimizing the target and source databases.
Use Session Log to identify if the source, target or transformations are the performance bottleneck

• • Avoid executing major sql queries from mapplets or mappings.
  • Use optimized queries when we are using them.
  • Reduce the number of transformations in the mapping. Active transformations like rank, joiner, filter, aggregator etc should be used as less as possible.
  • Remove all the unnecessary links between the transformations from mapping.
  • If a single mapping contains many targets, then dividing them into separate mappings can improve performance.
  • 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.
  • If a session joins many source tables in one source qualifier, then an optimizing query will improve performance.
  • 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.
  • Combine the mappings that use same set of source data.
  • 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.
  • Instead of doing complex calculation in query, use an expression transformer and do the calculation in the mapping.
  • If data is passing through multiple staging areas, removing the staging area will increase performance.
  • Stored procedures reduce performance. Try to keep the stored procedures simple in the mappings.
  • Unnecessary data type conversions should be avoided since the data type conversions impact performance.
  • 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.
  • Keep database interactions as less as possible.

Identifying Sessions Bottlenecks:

Sessions: If you do not have a source, target, or mapping bottleneck, you may have a session bottleneck.You can identify a session bottleneck by using the performance details. The informatica server creates performance details when you enable Collect Performance Data on the General Tab of the session properties.
Performance details display information about each Source Qualifier, target definitions, and individual transformation. All transformations have some basic counters that indicate the Number of input rows, output rows, and error rows. Any value other than zero in the readfromdisk and writetodisk counters for Aggregate, Joiner, or Rank transformations indicate a session bottleneck.

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.

DATABASE OPTIMISATION

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

• If the source and target are flat files, then they should be present in the system in which the Informatica server is present.
• Increase the network packet size.
• 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.
• Optimize target databases.

Optimizing the Bottleneck’s

System (Networks)

77.PUSH DOWN OPTIMISATION

Attribute	Value
Pushdown optimization	Full

3. Select the mapping tab set reader, writer connection with target load type normal.

4. Click apply  click ok  save the session.

5. Create & start workflow.

Pushdown Optimization Viewer:-

Double click the session  Select the mapping tab from left window  select pushdown optimization.