Wednesday, April 30, 2008

SSIS Performance Comparison

While working with a client this past week, I ran into a scenario where I needed to compare two dates in a SSIS package. One date would be the same for all rows, and one date would be different for each row. There are two possible implementations: include the "same date" in all rows and do a column comparison, or put the "same date" into a variable and do a comparison between the variable and column. My first instinct was that the column comparison would be faster because the extra trip to the server would be more expensive than extra column space. As I thought about it more, I realized that as the size of the dataset increases, the time to handle the extra column might overtake the hit from the extra trip.

So I ran a test to find out!

Scenario:

I created a temp table in the AdventureWorksDW database called FactLottaInternetSales that contains multiple copies of the FactInternetSales database, but with all orderdatekeys set to 1.

I created two packages to do the comparison.
Package 1: Column.dtsx

1 Data Flow Task containing:

  • 1 OLE DB Source:
    SELECT top x *
    FROM [AdventureWorksDW].[dbo].[FactLottaInternetSales]
    WHERE orderdatekey=1
  • 1 Conditional Split: OrderDateKey <>
  • 1 Row Count (as a terminator)

Package 2: Variable.dtsx

1 Execute SQL Task which assigns the result to the variable DateKey:
select max(orderdatekey)
from adventureworksdw.dbo.FactLottaInternetSales
where orderdatekey=1

1 Data Flow Task containing:

  • 1 OLE DB Source:
    SELECT top x [ProductKey] ,[OrderDateKey] ,[DueDateKey] ,[ShipDateKey] ,[CustomerKey] ,[PromotionKey] ,[CurrencyKey] ,[SalesTerritoryKey] ,[SalesOrderNumber] ,[SalesOrderLineNumber] ,[RevisionNumber] ,[OrderQuantity] ,[UnitPrice] ,[ExtendedAmount] ,[UnitPriceDiscountPct] ,[DiscountAmount] ,[ProductStandardCost] ,[TotalProductCost] ,[SalesAmount] ,[TaxAmt] ,[Freight] ,[CarrierTrackingNumber] ,[CustomerPONumber]
    FROM [AdventureWorksDW].[dbo].[FactLottaInternetSales]
    where orderdatekey=1
  • 1 Conditional Split: @[User::DateKey] <>
  • 1 Row Count (as a terminator)

Results:




The variable package takes longer up to about 2 million rows. After that, the column package takes longer! Happy performance tuning :)


Version: SQL Server 2005 SP2

1 comment:

Anonymous said...

Excellent analysis!