SQL Server Indexed View Question

Question

I have a requirement to create a report that kills the processor and takes a lot of time.

I think I could speed this up significantly by creating an index that stores all this data in one place, which will simplify the query / report. This opinion will not just be used for reporting, because I think it will benefit several areas in the data layer.

An indexed view could potentially contain 5 million + records, I don't seem to find any indication as to where the indexed views are no longer recommended. I assume that an index of this size will take a long time to build when SQL starts over, but I hope that after that the cost of maintaining it will be minimal.

Is there any best practice guide on when to use index views and when not to use them? Will the view be rebuilt after each server is restarted, or will it be stored somewhere on disk?

Answer 1

The index associated with your indexed view will be updated every time any of the columns in the index are updated.

A large number of updates is likely to kill the benefit. If it is mostly readable, then it will work fine.

The real benefits of indexed views are when you have aggregates that are too expensive to calculate in real time.

: SQL Server 2008:

:

• , .
• .
• .

. , , , . 1.

, :

• .
• .
• .
• - (OLAP).
• .

, :

• .
• .
• .
• .
• .

Answer 2

(aka ) SQL Server (). , , .

, :

select * into CachedReport from YourView

, , . , , select into .

Answer 3

. , "" , , , .

- , , , ( , - ).

Answer 4

, . - .

First of all, Timothy suggested checking the indexes on your main tables, then the statistics. Your query optimizer may be on the full path due to missing / outdated statistics.

If this does not help in performance, check what data is really needed for the view, since I assume that a) the number of rows and b) the row size is what kills your server, loading the whole view into the temp table and running it through competition input-output