Optimize mappings by offloading database operations

A common Informatica CDI use case might involve connecting to a database, pulling out a set of data, and writing the data to a different location. Informatica provides a number of transformations that can be used to alter the data after pulling it from the source database but before writing it to the target. Some of these transformations aren't very efficient for large volumes of data and can cause your integration to run slowly and/or use large amounts of shared memory.

When possible, it's best to perform transformations in the SQL query to the original database, rather than using Informatica's transformations.

Select only the fields you need

Bad: 

SELECT * FROM mydatabase.users;

Better:

SELECT first_name, last_name, netid FROM mydatabase.users;

Select only the records you need

SELECT first_name, last_name, netid FROM mydatabase.users
WHERE netid IS NOT NULL;

Remove duplicates

SELECT first_name, last_name, DISTINCT netid FROM mydatabase.users;

Order

SELECT first_name, last_name, netid FROM mydatabase.users ORDER BY last_name DESC;

Joined tables

SELECT users.first_name, users.last_name, users.netid, publications.title 
FROM mydatabase.users AS users
INNER JOIN mydatabase.publications AS publications
ON publications.netid = users.netid