Union and Union All in Informatica

[ravinuthala]

Hi,

Can anyone please tell which one will improve session performance if we have 'Union' and 'Union All' in the same mapping.

For example, if we have two tables A and B then

(1) 'A UNION B' gives all DISTINCT recs from both the tables. Here to get the disticnt recs also will take time.

(2) 'A UNION ALL B' gives ALL(not distinct) recs from both tables and again it would take time to load into target table which has key constraints.Here also it wil time to do this operation.

This leads to a confusion which technique will improve session performace?

Thanks,
Ravinuthala

[jamesravid]

Obviously the second query will have better performance than the first one. Reason for this is the UNION query sort the output records in order to filter the duplicate records. But the UNION ALL query does not rsort the output records as it is going to display all the records...

[sanghala]

Hi,

Can anyone please tell which one will improve session performance if we have 'Union' and 'Union All' in the same mapping.

For example, if we have two tables A and B then

(1) 'A UNION B' gives all DISTINCT recs from both the tables. Here to get the disticnt recs also will take time.

(2) 'A UNION ALL B' gives ALL(not distinct) recs from both tables and again it would take time to load into target table which has key constraints.Here also it wil time to do this operation.

This leads to a confusion which technique will improve session performace?

The second one i.e., UNION ALL will take less time to retrieve the records which in turn improves the performance as it retrieval time for records will be less.

By using UNION ALL, we get the distinct records from both A and B tables and also not matched records from both tables.

In case of UNION, this will allow to retrieve only distinct records from the both A and B tables, which is a time consuming query.

So finally, for better performance, its better to go for UNION ALL rather than going for UNION.