The code includes 1 while statement which is used to loop through the "SP_FillTable" for each year. Run alone, it takes about a minute to perform this task for each year. Therefore the 'slow' part is in this stored procedure which happens to cross join several large temp tables and derived tables. The task I am doing works on million-row tables and given the scenario is fast enough. I've tried to get people to look at it, heck people have even asked for the query, but its almost useless without understanding the rather complicated scope of the goal in mind. It used to take nearly 4hours to run, and I have got it down to 2-3min.

Anyhow, thanks for the help so far. I talked to the boss and apparently he wants me to keep the table along and update it as I go, not clear it and use it for storage. This brings up two questions.

1.) So now I suppose I have to find a way to check the table for records and update accordingly or insert if the daily records don't exist. Is there an easy way to do this, I once saw something having to do with UPDATE SET .. If @@rowcount=0 INSERT.

2.) Is there a way for SQL server to isolate processes on a table when one stored procedure is updating and another is reading.

My main concern through this all is how concurrent reads and updates are handled. Or are we just to assume every process is super fast and never has any overlap to worry about, but seriously I don't think that is a good assumption.