When should we use Views, Temporary Tables and Direct Queries ? What are the Performance issues in a
- by Shantanu Gupta
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.
