Performance Tuning in Informatica: Complete Tutorial

By : David Carter David Carter
Hours Updated

⚡ Smart Summary

Performance tuning in Informatica removes the slowest link in a session, one layer at a time, working from the target back through the source, the mapping, the session and finally the operating system.

  • 🎯 Fixed order: Informatica recommends looking for bottlenecks as target, then source, then mapping, then session, then system.
  • 🧵 Thread statistics tell the story: The busiest of the reader, transformation and writer threads names the layer that needs work.
  • 🚿 Filter early: Rows discarded in the Source Qualifier never enter the pipeline, so they cost nothing downstream.
  • 🗄️ Push work to the database: Joins, sorts and filters usually run faster in SQL than in a transformation.
  • 🧊 Cache discipline: Fewer ports and fewer lookup columns mean smaller caches and less disk paging.
  • ⚙️ Session settings matter: DTM buffer size, buffer block size, commit interval and pushdown optimization are tuned after the mapping is clean.
Read More

Performance Tuning in Informatica

What is Performance Tuning in Informatica?

Performance tuning in Informatica is the practice of finding the one component that limits how fast a session runs, removing that limit, and then repeating the exercise on whatever becomes the slowest component next. A session is only ever as quick as its slowest layer, so tuning a transformation that was never the problem produces no measurable gain.

Informatica identifies five places where a bottleneck can sit and recommends checking them in a fixed order.

Order Layer Typical cause
1 Target Slow writes, small checkpoint intervals, small database network packet sizes
2 Source Slow query, missing indexes, unnecessary columns being read
3 Mapping Expensive or badly placed transformations, oversized caches
4 Session Buffer memory, commit interval, partitioning, load type
5 System CPU saturation, I/O waits, paging on the Integration Service machine

The order is deliberate. A target that cannot absorb rows fast enough will make every upstream layer look slow, so it is checked first. The full method is documented in the PowerCenter Performance Tuning Guide.

How to Identify Performance Bottlenecks

Guessing which layer is slow wastes more time than measuring it. Four techniques cover the five layers listed above.

  • Run a test session. Configure a copy of the session to write to a flat file target. If the session speeds up noticeably, the target is the bottleneck. The mirror image of the same trick, reading from a flat file source, isolates a source bottleneck.
  • Analyse thread statistics. The Data Transformation Manager runs a reader thread, one or more transformation threads and a writer thread. The thread with the highest busy time in the session log points straight at the layer to work on: reader for the source, transformation for the mapping, writer for the target.
  • Analyse performance details. Enable performance data collection on the session and read the counters. High error rows or a large number of rows in a lookup cache indicate a mapping problem rather than a database one.
  • Monitor the system. Operating system tools showing CPU use, I/O waits and paging, together with the Workflow Monitor resource views, reveal a machine that is simply out of capacity.

Once the responsible layer is known, the transformation-level advice in the following sections becomes worth applying. The sections are arranged in pipeline order, from the point where data enters the mapping to the point where it is aggregated.

Source Qualifier Transformation

Every row that the Source Qualifier does not read is a row no other transformation has to process, which makes it the cheapest place in the whole mapping to save time.

  • Bring only the required columns from the source. Most of the times not all the columns of the source table are required, so bring only the required fields by deleting the unnecessary columns.
  • Avoid using order by clause inside the Source Qualifier SQL override. The order by clause requires additional processing and performance can be increased by avoiding it.

RELATED ARTICLES

Filter Transformation

Filtering follows the same principle one step later in the pipeline: discard unwanted rows at the earliest point at which the mapping has enough information to identify them.

  • Use filter transformation as early as possible inside the mapping. If the unwanted data can be discarded early in the mapping, it would increase the throughput.
  • Use source qualifier to filter the data. You can also use source qualifier SQL override to filter the records, instead of using filter transformation.

Joiner Transformation

Joining is the first genuinely expensive operation in a typical mapping, because the master source has to be cached before detail rows can be matched against it.

  • Always prefer to perform joins in the database if possible, as database joins are faster than joins created in Informatica joiner transformation.
  • Sort the data before joining if possible, as it decreases the disk I/O performed during joining.
  • Make the table with less number of rows as master table.

The third point is the one most often missed. The Integration Service caches the master source, so nominating the smaller table as master keeps that cache small.

Lookup Transformation

A lookup either queries the database once per row or builds a cache in memory, and both routes reward a smaller, better indexed lookup source.

  • Create an index for the column in a lookup table which is used in lookup condition. Since the lookup table will be queried for looking up the matching data, adding an index would increase the performance.
  • If possible, instead of using lookup transformation use join in the database. As database joins are faster, performance will be increased.
  • Delete unnecessary columns from the lookup table and keep only the required columns. This will bring down the overhead of fetching the extra columns from the database.

Aggregator Transformation

An Aggregator holds data in a cache while it groups rows, so anything that reduces the volume reaching it reduces the cache it needs.

  • Filter the data before aggregating it. If you are using filter transformation in the mapping, then filter the data before using aggregator as it will reduce the unnecessary aggregation operation.
  • Limit the number of ports used in the aggregator transformation. This will reduce the volume of data that aggregator transformation stores inside the cache.

Session-Level Tuning in Informatica

When the mapping itself is clean, the remaining gains come from the session properties. These settings are worth changing one at a time, with a timed run after each change, because several of them trade memory for speed.

Setting What it controls When to change it
DTM buffer size Total memory the Integration Service allocates for source and target data blocks Increase when the session handles many partitions, sources or targets
Buffer block size Size of an individual memory block Increase for unusually large rows; decrease when physical memory is limited
Commit interval How many rows are written before a commit is issued Raise it when the writer thread spends its time waiting on database checkpoints
Pushdown optimization How much of the mapping logic is converted into SQL and executed by the database Use when source and target sit on the same powerful database

Buffer memory follows a documented calculation rather than guesswork. The Integration Service allocates at least two blocks for each source and target partition, so the number of session buffer blocks is (total sources + total targets) multiplied by two, and the DTM buffer size is that block count multiplied by the buffer block size and divided by 0.9.

Pushdown optimization deserves a caution. It only helps when the database is genuinely faster than the Integration Service machine and the transformation logic can be expressed in SQL; logic that cannot be translated stays in the session, so the gain is often smaller than expected. Measure before and after rather than enabling it as a default.

FAQs

Partitioning helps when the Integration Service machine has spare CPU and the source and target can serve parallel connections. On a saturated machine, or against a single-threaded target, extra partitions add overhead without shortening the run.

Set index and data cache large enough to hold the whole lookup source, otherwise the Integration Service pages to disk. Session logs report the cache size actually required, so run once with automatic sizing and read the value back.

Each insert also maintains every index and constraint on the target, and that cost rises with table size. Stale database statistics make matters worse. The writer thread then becomes the busiest thread, which is the classic target bottleneck signature.

Yes, because the transformation can release each group as soon as it ends instead of caching everything. The incoming rows must already be sorted by the group-by ports, and the session fails if they are not.

For a large insert-only load, dropping indexes and constraints first and rebuilding them afterwards is usually faster. Pre-session and post-session SQL commands on the session properties are the normal place to script both steps.

Bulk mode bypasses much of the database logging and loads faster, but it prevents recovery and does not work with all targets or with update strategies. Normal mode writes through the usual logged path and stays recoverable.

Models trained on historical run times flag a session that has drifted from its normal duration long before anyone notices, and cluster related failures. They point at the run that changed — the layer responsible still has to be confirmed from thread statistics.

It can rewrite a query, suggest index candidates and explain an execution plan pasted into the editor. It cannot see the repository or the session log, so any rewrite has to be validated against the real plan and row counts.

Summarize this post with: