indexed view vs temp table to improve performance of a seldom executed query

250 Views Asked by At

i have a slow query whose structure is

select
  fields
from 
  table
join
  manytables
join (select fields from tables) as V1 on V1 on V1.field = ....
join (select fields1 from othertables) as V2
join (select fields2 from moretables) as V3 

The select subqueries in the last 3 joins are relatively simple but joins agains the, take time. If they were phisical tables it would be much better.

So i found out that i could turn the subqueries to indexed views or to temp tables.

By temp table i do not mean a table who is written hourly like explained here, but a temp table who is created before the query execution

Now my doubt comes from the fact that indexed views are ok in datawarehouses since they impact the performance. This db is not a datawarehouse but a production db of a non data intense application.

But in my case the above query is executed not often, even if the underlaying tables (the tables whose data would become part of the indexed view) are used more often.

In this case is it ok to use indexed views? Or shuold i favor temp table?

Also table variable with primary key keyword is an alternative.

0

There are 0 best solutions below