Home > SSIS > SSIS Balanced Data Distributor – Comparison

 

SSIS Balanced Data Distributor – Comparison

May 21st, 2011

Microsoft just released a new Data Flow transformation for SSIS – Balanced Data Distributor. Reading the installation pages, we can see that there are a few interesting things about it, among which – it is a multithreaded transform, which uniformly splits a data stream in multiple outputs. I decided to give it a quick test and see how it performs in comparison to a straight insert and a Script Component which splits the input stream in two, as well.

First the tests show performance with input of a SQL Server table (OLE DB Input) and output to a raw file. The input is a TOP 20000000 * select from a large table. The results are as follow:

1. Straight insert: 32 seconds

2. Balanced Data Distributor: 36 seconds

3. Script Component: 57 seconds

4. Conditional Split: 42 seconds



Note that the Conditional Split divides the stream based on the remainder of a division of an integer field, while the Script Component does it based on the row number. The Conditional Split may or may not be useful in a number of cases – when we have non-numeric data only, or when the range of the numeric data is not wide enough to split in the number of streams we would like to (e.g. Gender Key can be 1 or 2, while we may want to split in 10 parallel data streams). Therefore, its functionality is not equivalent to the BDD transform (thanks to Vidas Matelis for asking me to include it in the case study).

The second tests show how fast it is with reversed input and output (raw file to SQL Server table) and everything else identical.

  1. Straight insert: 56 seconds
  2. Balanced Data Distributor: 1 minute and 47 seconds
  3. Script Component: 1 minute and 57 seconds
  4. Conditional Split: 1 minute and 54 seconds

Over 40M rows the difference between the BDD transform and the Script Component – 1:16 vs 2:13 (vs 1:24 for the equivalent Conditional Split), or 57 seconds difference when inserting in a raw file. In general, the overall performance improvement seems to be around 35-45% in that direction. Since I am inserting in my local SQL Server in the same table the parallel split does not seem to be beneficial even though the destination is slower than the source. In fact the straight insert outperforms the parallel data flows in both cases. If we were to insert into a partitioned table over different filegroups hosted on separate drives the results could be quite different. Either way, in my opinion it is a nice and welcome addition to our arsenal of SSIS data flow components.

Edit: Len Wyatt from the SQL Performance Team at Microsoft has provided a link to his post with great bit of detail about the BDD transform in the comments below. Please take a minute and have a look if interested.

If you enjoyed this post, make sure you subscribe to my RSS feed!

 

SSIS , ,

  1. | #1

    Hi Boyan,

    Excellent info. Did you try comparing the BDD against the Multicast transform? It seems very similiar and the Multicast transform is multitreaded also in SQL Server 2008, so I was curious to see the performance difference.

  2. | #2

    Hi James,

    The multicast creates two outputs identical to the input stream. The BDD does not – it splits the input in two. So, if you have 10 rows in the input you get 2×5 in the two outputs. Therefore, the multicast is hardly the same as the BDD. We could emulate by placing a conditional split on each multicast output, but this would be less effective as we would need to do twice as much work as in the single conditional split case and allocate more resources.

  3. | #3

    Thanks for clearing that up Boyan. So does the BDD send the odd rows to one output, and the even rows to the other output?

  4. | #4

    It synchronously, multithreaded-ly splits the input in two. There is no even/odd logic. For a small amount of data (I tried with 6 rows) it may also send all rows to one of the outputs. The even/odd logic I implemented in the script component emulates but does not replicate this behavior on 100%. It was just for comparing…

  5. | #5

    So if I understand it correctly now, the BDD will split the data, but there is no way to know how it is deciding to split the data or to know which input records will go to which output. Is that correct? If so, the purpose of the BDD would be to split a bunch of input records into two outputs so you can, for example, have both outputs use a data conversion task that would run on seperate threads, thereby converting all the records up to twice as fast. Then use a merge join task to combine all the records into one dataset again. Is my thinking correct?

  6. | #6

    That is my understanding, yes. It would be useful if you want to increase parallelism in the dataflow.

  7. | #7

    Hmm, I wonder if there’s a use for this with Dryad or PDW…?

  8. | #8

    Interesting thought, Chris. But also, Jamie Thomson wrote this one:

    http://sqlblog.com/blogs/jamie_thomson/archive/2011/01/12/investigation-can-different-combinations-of-components-effect-dataflow-performance.aspx

    Seems like depending on the scenario it could be beneficial to parallelise the data flow.

    On the other hand, Todd McDermid wrote about the dangers of doing so here:

    http://toddmcdermid.blogspot.com/2010/09/parallelism-in-ssis-multiple-lookups.html

    So, it all depends, but it’s cool to see new thingies coming out of the press.

  9. Len Wyatt
    | #9

    For more information on the Balanced Data Distributor, look here: http://blogs.msdn.com/b/sqlperf/archive/2011/05/25/the-balanced-data-distributor-for-ssis.aspx

Comments are closed.