using same select multiple times vs creating temp table

Question

I have code like

select count(*) from(
select t1.col1, t1.col2, t1,col3, t2.col2 from table1 t1
left join table2 t2 on t1.col1=t2.col1) x

select * from(
select t1.col1, t1.col2, t1,col3, t2.col2 from table1 t1
left join table2 t2 on t1.col1=t2.col1) x
where x.col1 > 10

I'm selecting and joining same tables with same columns twice. If i do it like:

declare @table table(col1 int,col2 int,col3 varchar,col4 int)
insert into @table(col1,col2,col3,col4) select * from (
select t1.col1, t1.col2, t1,col3, t2.col2 from table1 t1
left join table2 t2 on t1.col1=t2.col1
) x
select count(*) from @table
select * from @table where col1>10

which one would have better performance? creating temp table and reusing it multiple times or just selecting multiple times?

@JohnSaunders this case happens a lot. I don't want results for a specific case. I want to know the difference in general. — Ashkan Mobayen Khiabani, Mar 30 '14 at 04:20
Well, in general, if computing the sub-SELECT is time-consuming, and you will use that sub-SELECT in many different queries, then obviously it must be faster to put the results in a temporary table. But you know this already. — j_random_hacker, Mar 30 '14 at 04:22

Aasish Kr. Sharma · Accepted Answer · 2014-04-01T09:08:24.273

5

I will not suggest selecting multiple time same query, if data are not changing on the table at run time and also number of rows selected are under 10000-15000 (depending on resources). Better is if we can hold once selected data in cache and make what ever manipulation required. Variable table have benefit over temp table in such case because when scope of query is over, resources utilized by variable table are also released.

edited Apr 01 '14 at 09:08

answered Mar 30 '14 at 13:26

Aasish Kr. Sharma

556
6
17

That in and of itself is not a good reason to choose table variables over temporary tables. [This answer](http://stackoverflow.com/questions/11857789/when-should-i-use-a-table-variable-vs-temporary-table-in-sql-server) explains very clearly how you should make that choice. – Steve Pettifer Apr 01 '14 at 08:09
Yes, that is right but Temp table behave as real table and is accessible out of the current scope, so when multiple user access same procedure then conflict may arise while inserting and deleting records. – Aasish Kr. Sharma Apr 01 '14 at 13:22
1

Only global temporary tables (prefixed ##). Local temporary tables (prefixed #) are not available outside your session, even from sessions on the same connection/with the same username. Local temp tables are dropped when your session ends. In addition, table variables cannot have indexes (other than primary keys and unique constraints). This generally makes table variables unsuited to large data sets. – Steve Pettifer Apr 01 '14 at 14:03

using same select multiple times vs creating temp table

1 Answers1