May 24, 2006 at 7:18 am
I've recently inhereted a large OLAP db. In the interest of performance tuning, the guy before me broke down some of the bigger tables into montly partitions.
I've been wishing I could have some materialized views for a lot of the long running queries they're asking me to optimize, but with the current set up, I'd have to UNION all the montly tables together which disallows a materialized view.
Would it be better to reconstruct the huge 50MM row table, then create matted views for each month? or was the guy before me on the right track?
Now that I think of it... a matted view can't reference another matted view, can it?
May 24, 2006 at 8:51 am
if you get it right partitioned views work really well. You can create hierachies of partitioned views for searching without any problems.
What I did find with partitioned views is that you musn't try searching or updating on non-indexed columns.
You might find creating smaller views, say in groups of 6 tables, and building logic to handle which view to use will give performance benefits. For updates including the partitioning column in the update will always improve performance, even if you have to do a seek first to get which value ( probably too difficult to explain here )
Indexed views are good but duplicate your physical data which might not be good. Make sure stats and indexes are optimal for performance - I often find the main mistake with olap/dw databases is to not update stats after data loads.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply