Speeding up Derived Column Transforms

Published Mar 25 2019 02:41 PM 75 Views
Not applicable
First published on MSDN on Aug 20, 2010

A new technical note about splitting up transforms to increase performance has been posted on the SQLCAT site . It’s something you want to consider when you’re using synchronous transforms (like Derived Column, or Data Convert) to perform actions on a large number of columns. It seemed like black magic at first, but if you consider the data flow threading changes that were made in 2008 , it starts to make sense. Also note that because the performance increase is a result of these threading changes, you won’t see this same performance increase if you’re using SSIS 2005. I’m going to have to start adding this one to my list of performance patterns…

Increasing Throughput of Pipelines by Splitting Synchronous Transformations into Multiple Tasks...
by Sedat Yogurtcuoglu, Henk van der Valk, and Thomas Kejser

Here are some screen caps from the white paper.

Before:


After:


%3CLINGO-SUB%20id%3D%22lingo-sub-387706%22%20slang%3D%22en-US%22%3ESpeeding%20up%20Derived%20Column%20Transforms%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-387706%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Aug%2020%2C%202010%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3EA%20new%20technical%20note%20about%20%3CA%20href%3D%22http%3A%2F%2Fsqlcat.com%2Ftechnicalnotes%2Farchive%2F2010%2F08%2F18%2Fincreasing-throughput-of-pipelines-by-splitting-synchronous-transformations-into-multiple-tasks.aspx%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%20splitting%20up%20transforms%20to%20increase%20performance%20%3C%2FA%3E%20has%20been%20posted%20on%20the%20%3CA%20href%3D%22http%3A%2F%2Fsqlcat.com%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%20SQLCAT%20site%20%3C%2FA%3E%20.%20It%E2%80%99s%20something%20you%20want%20to%20consider%20when%20you%E2%80%99re%20using%20synchronous%20transforms%20(like%20Derived%20Column%2C%20or%20Data%20Convert)%20to%20perform%20actions%20on%20a%20large%20number%20of%20columns.%20It%20seemed%20like%20black%20magic%20at%20first%2C%20but%20if%20you%20consider%20the%20%3CA%20href%3D%22http%3A%2F%2Fblogs.msdn.com%2Fb%2Fmichen%2Farchive%2F2007%2F06%2F11%2Fkatmai-ssis-data-flow-improvements.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20data%20flow%20threading%20changes%20that%20were%20made%20in%202008%20%3C%2FA%3E%20%2C%20it%20starts%20to%20make%20sense.%20Also%20note%20that%20because%20the%20performance%20increase%20is%20a%20result%20of%20these%20threading%20changes%2C%20you%20won%E2%80%99t%20see%20this%20same%20performance%20increase%20if%20you%E2%80%99re%20using%20SSIS%202005.%20I%E2%80%99m%20going%20to%20have%20to%20start%20adding%20this%20one%20to%20my%20list%20of%20performance%20patterns%E2%80%A6%3C%2FP%3E%0A%20%20%3CP%3E%3CA%20href%3D%22http%3A%2F%2Fsqlcat.com%2Ftechnicalnotes%2Farchive%2F2010%2F08%2F18%2Fincreasing-throughput-of-pipelines-by-splitting-synchronous-transformations-into-multiple-tasks.aspx%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%20Increasing%20Throughput%20of%20Pipelines%20by%20Splitting%20Synchronous%20Transformations%20into%20Multiple%20Tasks%20%3C%2FA%3E%20%3CBR%20%2F%3E%20%3CSTRONG%3E%20by%20%3C%2FSTRONG%3E%20Sedat%20Yogurtcuoglu%2C%20Henk%20van%20der%20Valk%2C%20and%20Thomas%20Kejser%3C%2FP%3E%0A%20%20%3CP%3EHere%20are%20some%20screen%20caps%20from%20the%20white%20paper.%3C%2FP%3E%0A%20%20%3CP%3EBefore%3A%3C%2FP%3E%0A%20%20%3CP%3E%3CA%20href%3D%22http%3A%2F%2Fsqlcat.com%2Fblogs%2Ftechnicalnotes%2Fimage_053616FF.png%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%20%3C%2FA%3E%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%20%20%20%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EAfter%3A%3C%2FP%3E%0A%20%20%3CP%3E%3CA%20href%3D%22http%3A%2F%2Fsqlcat.com%2Fblogs%2Ftechnicalnotes%2Fimage_5412C85F.png%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%20%3C%2FA%3E%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%20%20%20%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-387706%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Aug%2020%2C%202010%20A%20new%20technical%20note%20about%20splitting%20up%20transforms%20to%20increase%20performance%20has%20been%20posted%20on%20the%20SQLCAT%20site.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-387706%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EPerformance%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Mar 25 2019 02:41 PM
Updated by: