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
– Ashkan Mobayen Khiabani, Commented Mar 30, 2014 at 4: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
– j_random_hacker, Commented Mar 30, 2014 at 4:22

Aasish Kr. Sharma · Accepted Answer · 2014-04-01 09:08:24Z

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 1, 2014 at 9:08

answered Mar 30, 2014 at 13:26

Aasish Kr. Sharma

5566 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Steve Pettifer Over a year ago

That in and of itself is not a good reason to choose table variables over temporary tables. This answer explains very clearly how you should make that choice.

Aasish Kr. Sharma Over a year ago

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.

Steve Pettifer Over a year ago

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.

Collectives™ on Stack Overflow

using same select multiple times vs creating temp table

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related