Slowly Changing Dimensions - SQL Server Integration Services Design Patterns - page 357

The components output from the Slowly Changing Dimension Wizard are not con-
figured for optimal performance. By changing some settings and moving to a set-based
pattern, you can drastically improve the performance of your SCD processing.
The Slowly Changing Dimension Transform
The main transform does not cache any row results from the reference dimension, so
every incoming row results in a query against the database. By default, the wizard will
open a new connection to the database on each query. For a gain in performance (as
well as lower resource usage), you can set the RetainSameConnection property
of the connection manager used by the wizard to True so that the same connection is
reused on each query.
OLE DB Command Transforms
The wizard will output two (or three, if you're processing inferred members) OLE DB
Command transforms. These transforms perform row-by-row updates, which greatly
degrade performance. You will get a big performance boost by placing these rows in
staging tables and performing the updates in a single batch once the data flow com-
pletes.
OLE DB Destination
Since the main Slowly Changing Dimension transform and the destination use the
same connection manager, the destination component will have the Fast Load option
disabled by default to avoid deadlocking your data flow. If you are processing a small
number of rows (for example, a single data flow buffer's worth of data), you can en-
able Fast Load on the destination component for an immediate performance gain. To
avoid deadlocking issues when processing a larger number of rows, consider using a
staging table. Bulk load the data into a temporary staging table and update the final
destination once the data flow is complete using an INSERT INTO ... SELECT
statement.
Third-Party SCD Components