michal.lisinski (6/25/2013)
2. Check size of data sets inserted to the temp tables, if they are small (few rows), use varaible table.
Disagree there, table variables are not magically faster than temp tables, they can be slower due to lack of stats.
3. Convert some parts to the functions and call them from SP.
Disagree there, functions are notorious for causing performance problems, not solving them.
Oh, and for the OP, last time I tuned something this size it took a full week, 8-10 hours a day, 6 days.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability