1

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?

3

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.

  • Thanks. I do use CTE but it is not always possible to put everything in CTE and so I have to access the same table say 10 times in different queries then is it a good idea to take the data in temp table. – Pawan Nogariya Oct 3 at 15:53
  • Specially when it is a plain dataset without any logic so straight records from a table – Pawan Nogariya Oct 3 at 15:55
  • As I said, it's a "good idea" IF "you discover that putting the logic in a single query is not running acceptably". Otherwise it's premature optimization. – David Browne - Microsoft Oct 3 at 15:56
  • Okay. So if say I find that it is not possible to club queries into one then this should affect the performance, right? – Pawan Nogariya Oct 3 at 15:59
  • So is the actual table data and temp table data not same for procedure? In the sense that procedure runs within the database and so it shouldn't be same for it or temp table has some different mechanism that helps performance? – Pawan Nogariya Oct 3 at 16:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.