Sql-server – Does loading data to temp table improve performance

etlperformancesql server

When I write stored procedure I use views that have calculation inside them and so if I have to reuse the view multiple times in my procedure, I load it to a temp table and then use the temp table throughout the procedure to avoid the same calculation begin done everytime the view is accessed.

But does this thing applies for straight table's data too?

So if I have to access same table multiple time in the stored procedure is it a good idea to load the data in temp table and access it rather than the original table?

Does it improve performance in any way? Since the data is then local to the procedure?

I am not sure because for procedure table data is also local in that sense.

Can any please help?

Best Answer

if I have to access same table multiple time in the stored procedure is it a good idea to load the data in temp table and access it rather than the original table?

It sometimes is. It is a common performance optimization to "materialize" or "spool" intermediate results into a temp table, if putting the logic for returning the intermediate results in the main query turns out to be too complex or expensive.

This is not something you should do always. Just if you discover that putting the logic in a single query is not running acceptably.

A good way to organize this is with Common Table Expressions, where you can have a "pipeline" of query logic. eg:

with 
q1 as ( select ...),
q2 as ( select ...),
q3 as ( select ...),
select ...  
from q3;

Then if you want to spool one of the subqueries, it's as easy as

select ...
into #q1
from ...;

with 
q1 as ( select * from #q1 ),
q2 as ( select ...),
q3 as ( select ...),
select ...  
from q3;

"Lifting" the query logic from the CTE into a temp table load, and referencing the temp table in the CTE.

Related Question