I'm working on a query that essentially aggregates a slew of tables together.
The first method I tried was to inner join all of the tables into one nice big query that pretty much looked like this:
select a.col1 , a.col2 , a.col3 ... , b.col1 ... ... , l.col1 , l.col2 from myprimarytable a join secondtable b on a.key = b.key join third table c on a.key = c.key ... join thelasttable l on a.key = b.key
This took forever to run. However, a second solution that looked like this:
create table #output ( primkey char(40) , mycol1 char(1) null , mycol2 decimal(20,1) null ... , mylastcol varchar(max) null ) insert into #output select a.col1 , a.col2 ... from myprimarytable a update #output set mycol3 = b.col1 , mycol4 = b.col2 ... from #output a join secondtable b on a.primkey = b.key ...
Why does the temp table with updates take so much less time ( about 30x faster in my particular case), than one with all of the joins in one go?