This article assumes that you already have an optimal data model along with the most efficient data tier in your application.  You’ve spent plenty of time optimizing indexes, poring over the SQL explain plans, and tuning the SQL queries that your application uses to retrieve data from your SQL Server database.  The application has been running fairly smoothly, performance isn’t great but has been acceptable to the end users.

At some point, due to application load from the users, data volume, or other external variables, you notice that the queries you optimized utilizing the Management Studio’s estimated explain plan do not perform as expected. 

Let’s take a look at the table we’ll be querying.  We’re using the VARCHAR data type to store all string data in our database.

In my last post, I covered how to use an unpivot transformation in SSIS.  There are, however, at least two other ways the same output can be achieved.  In addition to the built in transformation, a multicast into a union all or a script task can be used.  While all three methods produce the same dataset, the latter two are slower and one is much less efficient than the native transformation.   If you are interested in each of the three methods, continue reading; otherwise, you can jump to the results.