If you write SQL with many UNION in OLEDB source transformation then you will find the SSIS package is running slow.
For example, you may require many SQL unions to populate the data set as like below:
SELECT x,y,z,z1,z2
FROM tblA
UNION
SELECT x,y,z,z1,z2
FROM tblB
UNION
SELECT x,y,z,z1,z2
FROM tblC
UNION
SELECT x,y,z,z1,z2
FROM tblD
UNION
SELECT x,y,z,z1,z2
FROM tblN
Instead of using the above SQL in OLEDB source, you can use Union All transformation which is much faster in compare with using the SQL code in OLEDB source.
For example, you may require many SQL unions to populate the data set as like below:
SELECT x,y,z,z1,z2
FROM tblA
UNION
SELECT x,y,z,z1,z2
FROM tblB
UNION
SELECT x,y,z,z1,z2
FROM tblC
UNION
SELECT x,y,z,z1,z2
FROM tblD
UNION
SELECT x,y,z,z1,z2
FROM tblN
 |
| Fig 1: OLEDB source |
Instead of using the above SQL in OLEDB source, you can use Union All transformation which is much faster in compare with using the SQL code in OLEDB source.
 |
| Fig 2: Union All Transformation |
1 comment:
Unions (at least in sql server) remove duplicate rows, so the database is going to have to sort and discard duplicates.
a sql "Union ALL" would simply add the sets together just like a SSIS Union All.
That may be the reason for the performance hit
Post a Comment