Hey,

Another suggestion you do as much filtering as you can of your data before you do you JOIN. So filter each table into Temporary variables and do the JOIN on the Temporary variables. Only retrieve the columns that you need to satisfy the join. School_Code will need to be retrieved for all tables and obviously you can filter on date on two of the tables. The other two tables (without the date) you can filter on based on the School_Code retrieved from the previous filtered query.

E.g

SELECT School_Code, acyear INTO #tmpERData

FROM ERData WHERE acyear = '2006-07'

-- Do the same for BasicData filtering on acyear

-- in the next temporary table i have filterered further based on School_codes from the previous filtered table as thats what you are going to be joining on anyway

SeLECT School_Code, Building_status, Tot_Clrooms, Classrooms_in_good_condition INTO #tmpFacilityData

FRM FacilityData

WHRE School_Code IN (Select School_Code from #tmpERData)

...

Then you should have your data filtered and you can use the temporary tables as a basis for your JOIN.

...

FROM #tmpBasicData B

INNER JOIN #tmpERData E ON B.School_Code = E.School_Code

INNER JOIN #tmpFacilityData F ON F.School_Code = E.School_Code

INNER JOIN #tmpGeneralData G ON G.School_Code = E.School_Code

Hope that makes sense. You can then wrap your whole code in a proc and just execute that Proc Passing in your Date as a parameter.

However going back to basics how is your TempDB configured? Maybe its too small anyway? Check there's enough space to grow on the disk its on and that there is no cap on it. A million rows in each table is not that much data unless they are very wide table. Also as stated in a previous post with regards to the use of your data types such as nvarchar(255) for acyear is certainly making them much more inefficient.