Thanks Mohit. Very good point. I love Red Gate & their tools.

Anyway, thanks to Mohit's work, I noticed that you're joining the tables in the final select, but you're not using ANSI 92 joins.

Three more issues jump out. First, you're working through the data in a non-set based approach. Loading data into temporary tables and then joining temporary tables together for output by it's nature is moving the data around multiple times. The trick would be to bring the data together in a single select statement. Also, you're using table variables and you said you're dealing with thousands of rows. Table variables don't have statistics, so that's going to slow down the query quite a bit. You'd be better off, if you had to stick with this approach, using temporary tables, #temp, instead of table variables, @temp. Finally, with the IF statements breaking up the execution of the query so much, you're going to get recompiles every time this thing executes. That's adding to the overhead and time of the query. A better approach is to create a wrapper procedure that does all the flow control IF statements that then calls to other procedures from within it.

To really see what's happening though, you need to post the actual execution plan (not estimated). Can you do this?