When should we use Views, Temporary Tables and Direct Queries ? What are the Performance issues in a

Posted by Shantanu Gupta on Stack Overflow See other posts from Stack Overflow or by Shantanu Gupta
Published on 2010-06-10T07:53:57Z Indexed on 2010/06/10 8:02 UTC
Read the original article Hit count: 244

I want to know the performance of using Views, Temp Tables and Direct Queries Usage in a Stored Procedure.

I have a table that gets created every time when a trigger gets fired. I know this trigger will be fired very rare and only once at the time of setup.

Now I have to use that created table from triggers at many places for fetching data and I confirms it that no one make any changes in that table. i.e ReadOnly Table.

I have to use this tables data along with multiple tables to join and fetch result for further queries say

select * from triggertable

By Using temp table

select ... into #tx from triggertable join t2 join t3 and so  on

select a,b, c from #tx --do something
select d,e,f from #tx ---do somethign
--and so on 
--around 6-7 queries in a row in a stored procedure.

By Using Views

create view viewname
(
select ... from triggertable join t2 join t3 and so  on
)

select a,b, c from viewname --do something
select d,e,f from viewname ---do somethign
--and so on 
--around 6-7 queries in a row in a stored procedure.

This View can be used in other places as well. So I will be creating at database rather than at sp

By Using Direct Query

select a,b, c from select ... into #tx from triggertable join t2 join t3 join ... --do something
select a,b, c from select ... into #tx from triggertable join t2 join t3 join ... --do something
.
.
--and so on 
--around 6-7 queries in a row in a stored procedure.

Now I can create a view/temporary table/ directly query usage in all upcoming queries.

What would be the best to use in this case.

© Stack Overflow or respective owner

Related posts about sql

Related posts about best-practices