我如何优化 sql server 中的视图以提高速度
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2735382/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how can i optimize views in sql server for speed
提问by girish
i have created views for my project now i want to optimize them for the speed purpose...how can i identify that the view can be optimize? is index usefull for this....
我已经为我的项目创建了视图，现在我想优化它们以提高速度……我如何确定可以优化视图？索引对此有用吗....
let's say the following example...
SELECT dbo.vw_WebInventory.skref AS SaleID, dbo.vw_WebInventory.lot_number AS LotNumber, dbo.vw_WebInventory.Description,
dbo.vw_WebInventory.Image AS HasImage, dbo.vw_WebInventory.Sold, dbo.vw_WebInventory.Withdrawn, dbo.vw_WebTopBids.TopBid,
ISNULL(dbo.vw_WebInventory.Mins_Extend_y, 0) AS BidTimeExtend, dbo.Sale.SaleTypeID, dbo.Sale.ClosingDate, dbo.vw_WebInventory.ExDate,
dbo.vw_WebInventory.CurrDate, CASE WHEN vw_WebInventory.ExDate > ISNULL(vw_WebInventory.LotClosingDate, Sale.ClosingDate)
THEN 1 ELSE 0 END AS ShowBidMessage
FROM dbo.vw_WebInventory INNER JOIN
dbo.Sale ON dbo.vw_WebInventory.skref = dbo.Sale.SaleID LEFT OUTER JOIN
dbo.vw_WebTopBids ON dbo.vw_WebInventory.skref = dbo.vw_WebTopBids.CatNumber AND dbo.vw_WebInventory.lot_number = dbo.vw_WebTopBids.LotNumber
where vm_webTopBids and vm_WebInventory are two different view... is it possible to optimize this view?
其中 vm_webTopBids 和 vm_WebInventory 是两个不同的视图...是否可以优化此视图？
回答by HLGEM
Nesting views that call other views is an extremely bad technique for performance. Since it can't be indexed, it has to call the entire underlying view in order to get the one record the top would return. Plus eventually you get enough layers and you hit the limit of how many tables you can call in a view (And if view1 calls view2 and view3 and both call the same underlying tables you are joining to them twice instead of once which is often bad for performance. Stop calling views from views or you will very shortly have a an unuseable system.
调用其他视图的嵌套视图对于性能来说是一种非常糟糕的技术。由于它不能被索引，它必须调用整个底层视图才能获得顶部将返回的一条记录。此外，最终您会获得足够的层，并且达到了可以在视图中调用的表数量的限制（如果 view1 调用 view2 和 view3 并且都调用相同的底层表，您将加入两次而不是一次，这通常对性能。停止从视图调用视图，否则你很快就会有一个无法使用的系统。
We are completely redesigning a system like this because the application developers did this and the multi-million-dollar client is going to leave us unless performance improves and we can't improve it with this struture, so now a we face a complete redesign that the client will not be paying for because the error was ours. DO NOT go down this road. Stop now. Views that call views are very, very bad.
我们正在完全重新设计这样的系统，因为应用程序开发人员这样做了，除非性能得到改善，否则价值数百万美元的客户将离开我们，而我们无法通过这种结构改进它，所以现在我们面临着彻底的重新设计客户不会支付，因为错误是我们的。不要走这条路。现在停止。调用视图的视图非常非常糟糕。
回答by gbn
A view is a macro that is expanded into the outer query. Unless it's an indexed view and you have enterprise edition, it's simply ignored.
视图是扩展到外部查询中的宏。除非它是索引视图并且您有企业版，否则它会被忽略。
So if you join 3 views and each views uses 5 tables, you have big join with 15 tables.
因此，如果您加入 3 个视图并且每个视图使用 5 个表，那么您就有 15 个表的大连接。
You best bet is the Database Tuning Advisoror a missing index script:
您最好的选择是数据库优化顾问或缺失的索引脚本：
SELECT
CONVERT(decimal(28, 1), migs.avg_total_user_cost * migs.avg_user_impact *
(migs.user_seeks + migs.user_scans)) AS improvement_measure,
'CREATE INDEX missing_index_' + CONVERT(varchar, mig.index_group_handle) +
'_' + CONVERT(varchar, mid.index_handle) + ' ON ' + mid.statement + ' (' +
ISNULL(mid.equality_columns, '') +
CASE WHEN mid.equality_columns IS NOT NULL AND
mid.inequality_columns IS NOT NULL THEN ','
ELSE ''
END + ISNULL(mid.inequality_columns, '') + ')' + ISNULL(' INCLUDE (' +
mid.included_columns +
')', '') AS create_index_statement,
migs.*,
mid.database_id,
mid.[object_id],
mig.index_group_handle,
mid.index_handle
FROM
sys.dm_db_missing_index_groups mig INNER JOIN
sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle INNER JOIN
sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE
CONVERT(decimal(28, 1), migs.avg_total_user_cost * migs.avg_user_impact *
(migs.user_seeks + migs.user_scans)) > 10 AND
database_id = DB_ID()
ORDER BY
migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks +
migs.user_scans) DESC
Edit, after example
编辑，示例后
You are nesting views on top of views. No optimisations are possible of the view itself.
您正在视图之上嵌套视图。视图本身无法优化。
As mentioned, this can't be indexed
如前所述，这不能被索引
回答by Daniel Renshaw
In this case, the view can't be indexed because it contains an OUTER JOIN.
在这种情况下，视图不能被索引，因为它包含一个 OUTER JOIN。
See this article for information on indexing views and the (many) restrictions on them: http://technet.microsoft.com/en-us/library/cc917715.aspx
有关索引视图及其（许多）限制的信息，请参阅本文：http: //technet.microsoft.com/en-us/library/cc917715.aspx
