THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Investigation: Can different combinations of components affect Dataflow performance?

Introduction

The Dataflow task is one of the core components (if not the core component) of SQL Server Integration Services (SSIS) and often the most misunderstood. This is not surprising, its an incredibly complicated beast and we’re abstracted away from that complexity via some boxes that go yellow red or green and that have some lines drawn between them.

Example Dataflow

Example dataflow

In this blog post I intend to look under that facade and get into some of the nuts and bolts of the Dataflow Task by investigating how the decisions we make when building our packages can affect performance. I will do this by comparing the performance of three dataflows that all have the same input, all produce the same output, but which all operate slightly differently by way of having different transformation components.

I also want to use this blog post to challenge a common held opinion that I see perpetuated over and over again on the SSIS forum. That is, that people assume adding components to a dataflow will be detrimental to overall performance. Its not surprising that people think this –it is intuitive to think that more components means more work- however this is not a view that I share. I have always been of the opinion that there are many factors affecting dataflow duration and the number of components is actually one of the less important ones; having said that I have never proven that assertion and that is one reason for this investigation.

I have actually seen evidence that some people think dataflow duration is simply a function of number of rows and number of components. I’ll happily call that one out as a myth even without any investigation! 

The Setup

I have a 2GB datafile which is a list of 4731904 (~4.7million) customer records with various attributes against them and it contains 2 columns that I am going to use for categorisation:

  • [YearlyIncome]
  • [BirthDate]

The data file is a SSIS raw format file which I chose to use because it is the quickest way of getting data into a dataflow and given that I am testing the transformations, not the source or destination adapters, I want to minimise external influences as much as possible.

In the test I will split the customers according to month of birth (12 of those) and whether or not their yearly income is above or below 50000 (2 of those); in other words I will be splitting them into 24 discrete categories and in order to do it I shall be using different combinations of SSIS’ Conditional Split and Derived Column transformation components. The 24 datapaths that occur will each input to a rowcount component, again because this is the least resource intensive means of terminating a datapath.

The test is being carried out on a Dell XPS Studio laptop with a quad core (8 logical Procs) Intel Core i7 at 1.73GHz and Samsung SSD hard drive. Its running SQL Server 2008 R2 on Windows 7.

The Variables

Here are the three combinations of components that I am going to test:

   

  1. One Conditional Split - A single Conditional Split component CSPL Split by Month of Birth and income category that will use expressions on [YearlyIncome] & [BirthDate] to send each row to one of 24 outputs.

    Dataflow 1 - Raw Source and Conditional Split

    This next screenshot displays the expression logic in use:

    image

  2. Derived Column & Conditional Split - A Derived Column component DER Income Category that adds a new column [IncomeCategory] which will contain one of two possible text values {“LessThan50000”,”GreaterThan50000”} and uses [YearlyIncome] to determine which value each row should get. A Conditional Split component CSPL Split by Month of Birth and Income Category then uses that new column in conjunction with [BirthDate] to determine which of the same 24 outputs to send each row to. Put more simply, I am separating the Conditional Split of #1 into a Derived Column and a Conditional Split.

    Dataflow 2 - Raw Source, Derived Column and Conditional Split

    The next screenshots display the expression logic in use:

    DER Income Category  
    image  
       
    CSPL Split by Month of Birth and Income Category  
    image  

     

  3. Three Conditional Splits - A Conditional Split component that produces two outputs based on [YearlyIncome], one for each Income Category. Each of those outputs will go to a further Conditional Split that splits the input into 12 outputs, one for each month of birth (identical logic in each). In this case then I am separating the single Conditional Split of #1 into three Conditional Split components.

    Dataflow 3 - 3 Conditional Splits

    The next screenshots display the expression logic in use:

    CSPL Split by Income Category  

    image

     
       
    CSPL Split by Month of Birth 1& 2  
    image  

     

Each of these combinations will provide an input to one of the 24 rowcount components, just the same as before. For illustration here is a screenshot of the dataflow containing three Conditional Split components:

image

As you can these dataflows have a fair bit of work to do and remember that they’re doing that work for 4.7million rows.

I will execute each dataflow 10 times and use the average for comparison. I foresee three possible outcomes:

  • The dataflow containing just one Conditional Split (i.e. #1) will be quicker
  • There is no significant difference between any of them
  • One of the two dataflows containing multiple transformation components will be quicker

Regardless of which of those outcomes come to pass we will have learnt something and that makes this an interesting test to carry out. Note that I will be executing the dataflows using dtexec.exe rather than hitting F5 within BIDS.

The Results and Analysis

The table below shows all of the executions, 10 for each dataflow. It also shows the average for each along with a standard deviation.

image

All durations are in seconds.
I’m pasting a screenshot because I frankly can’t be bothered with the faffing about needed to make a presentable HTML table.

It is plain to see from the average that the dataflow containing three conditional splits is significantly faster, the other two taking 43% and 52% longer respectively. This seems strange though, right? Why does the dataflow containing the most components outperform the other two by such a big margin? The answer is actually quite logical when you put some thought into it and I’ll explain that below.

Before progressing, a side note. The standard deviation for the “Three Conditional Splits” dataflow is orders of magnitude smaller – indicating that performance for this dataflow can be predicted with much greater confidence too.

The Explanation

I refer you to the screenshot above that shows how CSPL Split by Month of Birth and salary category in the first dataflow is setup. Observe that there is a case for each combination of Month Of Date and Income Category – 24 in total. These expressions get evaluated in the order that they appear and hence if we assume that Month of Date and Income Category are uniformly distributed in the dataset we can deduce that the expected number of expression evaluations for each row is 12.5 i.e. 1 (the minimum) + 24 (the maximum) divided by 2 = 12.5.

Now take a look at the screenshots for the second dataflow. We are doing one expression evaluation in DER Income Category and we have the same 24 cases in CSPL Split by Month of Birth and Income Category as we had before, only the expression differs slightly. In this case then we have 1 + 12.5 = 13.5 expected evaluations for each row – that would account for the slightly longer average execution time for this dataflow.

Now onto the third dataflow, the quick one. CSPL Split by Income Category does a maximum of 2 expression evaluations thus the expected number of evaluations per row is 1.5. CSPL Split by Month of Birth 1 & CSPL Split by Month of Birth 2 both have less work to do than the previous Conditional Split components because they only have 12 cases to test for thus the expected number of expression evaluations is 6.5 There are two of them so total expected number of expression evaluations for this dataflow is 6.5 + 6.5 + 1.5 = 14.5.

14.5 is still more than 12.5 & 13.5 though so why is the third dataflow so much quicker? Simple, the conditional expressions in the first two dataflows have two boolean predicates to evaluate – one for Income Category and one for Month of Birth; the expressions in the Conditional Split in the third dataflow however only have one predicate thus they are doing a lot less work. To sum up, the difference in execution times can be attributed to the difference between:

MONTH(BirthDate) == 1 && YearlyIncome <= 50000
and
MONTH(BirthDate) == 1

In the first two dataflows YearlyIncome <= 50000 gets evaluated an average of 12.5 times for every row whereas in the third dataflow it is evaluated once and once only. Multiply those 11.5 extra operations by 4.7million rows and you get a significant amount of extra CPU cycles – that’s where our duration difference comes from.

The Wrap-up

The obvious point here is that adding new components to a dataflow isn’t necessarily going to make it go any slower, moreover you may be able to achieve significant improvements by splitting logic over multiple components rather than one. Performance tuning is all about reducing the amount of work that needs to be done and that doesn’t necessarily mean use less components, indeed sometimes you may be able to reduce workload in ways that aren’t immediately obvious as I think I have proven here.

Of course there are many variables in play here and your mileage will most definitely vary. I encourage you to download the package and see if you get similar results – let me know in the comments. The package contains all three dataflows plus a fourth dataflow that will create the 2GB raw file for you (you will also need the [AdventureWorksDW2008] sample database from which to source the data); simply disable all dataflows except the one you want to test before executing the package and remember, execute using dtexec, not within BIDS.

If you want to explore dataflow performance tuning in more detail then here are some links you might want to check out:

Any comments? Let me know!

@Jamiet

Published Wednesday, January 12, 2011 9:28 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

SSIS Junkie said:

Earlier this evening I posted a blog post entitled Investigation: Can different combinations of components

January 12, 2011 4:51 PM
 

John Welch said:

Great post, Jamie. Another factor that may factor into this (for 2008 and above) is the ability for SSIS to use multiple threads even in synchronous execution trees in the data flow. The # of threads is still limited by the number of components though, so splitting a workload into more components doing less processing per component can improve throughput. See this post from SQLCAT for an example: http://sqlcat.com/technicalnotes/archive/2010/08/18/increasing-throughput-of-pipelines-by-splitting-synchronous-transformations-into-multiple-tasks.aspx

January 12, 2011 4:52 PM
 

Fritz said:

but isn't the performance of the second option is also affected by the string comparison? perhaps if instead of storing a string in the column "IncomeCategory" a smallint is stored the performance of the second option would be slightly better than the first option. I don't have my laptop at the moment but I'll test that. I also agree with John Welch but the number of execution threads can be locked for testing purposes, right?

January 12, 2011 5:11 PM
 

jamiet said:

Fritz,

Doing a string comparison may be slightly more resource intensive than an integer comparison but I doubt this would account for the big disparity that we are seeing here. Given Income Category only has two possible values you might even consider using a boolean rather than an integer.

I look forward to your results.

Jamie

January 13, 2011 5:14 AM
 

Simon said:

I'm with John here.

The fact that the two bottom conditional splits can work in parallel means that you are doing allowing for more parallelism.

Buffer 1 will be processed by split one and passwed to split 2, at which point split 1 will and split 2 will both be processing a buffer.

Would be interesting to see what would happen if you just used a multicast and had 4 splits.

Also the use of MONTH(..) in each expression will be very expensive moving that to a derived component will have a big change.

January 13, 2011 4:33 PM
 

Mike Davis said:

I wrote a similar blog last year. I agree that the number of components is not biggest factor in perfromance.

http://bidn.com/blogs/MikeDavis/ssis/173/ssis-parallel-vs-series-data-flows

January 18, 2011 10:09 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement