asked
How to improve performance of a view.
How to improve performance of a view.We have a view whose base table is full refresh everyday and it does a lot of left joins with staging and dimension tables.This view is then used to poplulate fact table.The issue is with its performance.how can we reduce the time to poplulate fact table from this view?
Your underlying problem might be the whole "full refresh" thing. For a long time I avoided incremental refreshing (just the changed rows of a fact table) because a) it can be tricky to set up when several transaction tables contribute to the makeup of the final fact table, and b) I just didn't trust that the vendor would always reset the [modified_date] on each affected "update" row (they were doing it through the application rather than triggers). Eventually "full refresh" became unworkably expensive so we were forced to bite the bullet and move to incremental refresh. Yes it's way more complicated, but the time taken is about a quarter of what it used to be. First things first though. Do you have a reliable way of detecting changes (Inserts/Updates/Deletes) on the transactional tables?
There is no way to just make a view faster except by materializing the view into a clustered index. A view is just a query. And, you're running queries against the view in some fashion. It's those queries that have to be correctly written in order to take advantage of any existing indexes in the table. Also, those queries will help you determine whether or not there are any new indexes needed. But, it's all about understanding how the queries are working and what they're doing in order to then tune the queries. For lots more details, see my book on Amazon
http://www.amazon.com/Server-Query-Performance-Tuning-Experts/dp/1430242035/ref=sr_1_2?ie=UTF8&qid=1409658682&sr=8-2&keywords=fritchey+query
17 People are following this question.
