通过大量联接提高视图的性能 [英] Improving performance on a view with a LOT of joins

问题描述

我有一个视图，该视图使用11个外部联接和两个内部联接来创建数据.这导致超过800万行.当我在表上进行计数(*)时，大约需要5分钟才能运行.我对如何改善此表的性能一无所知.有人对从哪里开始有什么建议吗?在所有要连接的列上似乎都有索引(尽管有些是复合的，不确定是否有区别……)

I have a view that uses 11 outer joins and two inner joins to create the data. This results in over 8 million rows. When I do a count (*) on the table it takes about 5 minutes to run. I'm at a loss as to how to improve the performance of this table. Does anyone have any suggestions on where to begin? There appear to be indexes on all of the columns that are joining (though some are composit, not sure if that makes a difference...)

任何帮助表示赞赏.

推荐答案

这很困难，对于复杂的视图，您还可能与针对该视图的查询进行交互，因此要保证合理的性能将非常困难.视图中的外部联接(尤其是复杂的视图)也容易给查询优化器带来麻烦.

This is a hard one, with a complex view you also have potential interactions with queries against the view, so guaranteeing reasonable performance will be quite hard. Outer joins in views (especially complex ones) are also prone to cause trouble for the query optimiser.

一种选择是实现视图(在SQL Server上称为索引视图").但是，您可能需要监视更新性能以检查它不会带来太多开销.同样，在物化视图中的外部联接可能会阻止实时刷新；如果需要，则可能必须将视图重新实现为非规范化表并使用触发器维护数据.

One option would be to materialise the view (called 'indexed views' on SQL Server). However you may need to monitor update performance to check that it does not impose too much overhead. Also, outer joins in a materialised view may preclude real-time refresh; if you need this then you may have to re-implement the view as a denormalised table and maintain the data with triggers.

另一种可能性是检查该视图是否可以分为两个或三个更简单的视图，可能实现某些而非全部视图.实现某些视图并以这种方式从系统获得性能可能会更容易.

Another possibility would be to examine whether the view could be split into two or three simpler views, possibly materialising some but not all of the view. It may be easier to materialise some of the view and get performance from the system that way.

这篇关于通过大量联接提高视图的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！