This was a great artical.

Here is my Experience. In My Previous Job we had a Stored Procedure that used to Take around 6 Hours to Run (Batch JOB) use to do a Lot of Things on a 35 GB Database. Many a times it used to fill up the Temp DB and Crash. Here are few things I did to fix it, and there was a great Improvement (1 Hour 40 Mts.)

There were a few Very Complex Query and used to Join with 9 Big Table. I broke that query and created a Temp Table, Used a While Loop on the Temp Table. Removed the Dynamic SSQL. Removed the Function like AVG , SUM on a Large Query and added it to Sub Query, Remember those function are lot of Overheads. Of course I had to create few Index.

But Yes !! There are quite a few ways of tuning a Stored Procedure. My Favorate is breaking up joins into various small queries and Pumping the Info in Temp Table and then Join them.

I am not sure if this is the best way but has always worked for me 😉

R A J