derived vs temp table performance
Posted on 2011-02-11
select col1,col2,col3 etc.. into temp from tabel1 where x=1 and y=2
select * from temp, table2 where temp.x = table2.x and etc..
drop table temp
select * from
(select col1,col2,col3 etc.. from tabel1 where x=1 and y=2 ) table1, table2
where table1.x = table2.x and etc..
lets say both tables have large amount of rows.
1) which is better performance wise? and best practice
2) assume we put an index on table2.x and #temp.x, will the performance change since you cant put an index on derived tables (table1). I guess the question really is creating an index + do a select on the index vs select on an non-index column for large tables.
3) What if instead of just two tables, now you have 10 tables you need to join.