Informatica Performance Tuning Guide, Performance Enhancements - Part 4
In our performance turning article series, so far we covered about the performance turning basics, identification of bottlenecks and resolving different bottlenecks. In this article we will cover different performance enhancement features available in Informatica PowerCener. In addition to the features provided by PowerCenter, we will go over the designs tips and tricks for ETL load performance improvement.

Performance Enhancements Features

The main PowerCenter features for Performance Enhancements are.
      1. Pushdown Optimization.
      2. Pipeline Partitions.
      3. Dynamic Partitions.
      4. Concurrent Workflows.
      5. Grid Deployments.
      6. Workflow Load Balancing.
      7. Other Performance Tips and Tricks.

1. Pushdown Optimization

Informatica Performance Tuning Guide, Performance Enhancements - Part 4
Pushdown Optimization Option enables data transformation processing, to be pushed down into any relational database to make the best use of database processing power. It converts the transformation logic into SQL statements, which can directly execute on database. This minimizes the need of moving data between servers and utilizes the power of database engine.

2. Session Partitioning

Informatica Performance Tuning Guide, Performance Enhancements - Part 4
The Informatica PowerCenter Partitioning Option increases the performance of PowerCenter through parallel data processing. Partitioning option will let you split the large data set into smaller subsets which can be processed in parallel to get a better session performance.

3. Dynamic Session Partitioning

Informatica Performance Tuning Guide, Performance Enhancements - Part 4
Informatica PowerCenter session partition can be used to process data in parallel and achieve faster data delivery. Using Dynamic Session Partitioning capability, PowerCenter can dynamically decide the degree of parallelism. The Integration Service scales the number of session partitions at run time based on factors such as source database partitions or the number of CPUs on the node resulting significant performance improvement.

4. Concurrent Workflows

Informatica Performance Tuning Guide, Performance Enhancements - Part 4
A concurrent workflow is a workflow that can run as multiple instances concurrently. A workflow instance is a representation of a workflow. We can configure two types of concurrent workflows. It can be concurrent workflows with the same instance name or unique workflow instances to run concurrently.

5. Grid Deployments

Informatica Performance Tuning Guide, Performance Enhancements - Part 4
When a PowerCenter domain contains multiple nodes, you can configure workflows and sessions to run on a grid. When you run a workflow on a grid, the Integration Service runs a service process on each available node of the grid to increase performance and scalability. When you run a session on a grid, the Integration Service distributes session threads to multiple DTM processes on nodes in the grid to increase performance and scalability.

6. Workflow Load Balancing

Informatica Performance Tuning Guide, Performance Enhancements - Part 4
Informatica Load Balancing is a mechanism which distributes the workloads across the nodes in the grid. When you run a workflow, the Load Balancer dispatches different tasks in the workflow such as Session, Command, and predefined Event-Wait tasks to different nodes running the Integration Service. Load Balancer matches task requirements with resource availability to identify the best node to run a task. It may dispatch tasks to a single node or across nodes on the grid.

7. Other Performance Tips and Tricks

Informatica Performance Tuning Guide, Performance Enhancements - Part 4
Through out this blog we have been discussing different tips and tricks to improve your ETL load performance.  We would like to reference those tips and tricks in this article for your reference.
Hope you guys enjoyed these tips and tricks and it is helpful for your project needs. Leave us your questions and commends. We would like to hear any other performance tips you might have used in your projects.

Share with your friends

Readers Comments