谁帮我看看这个视图改如何优化
这是视图:select * from
dbo.tb_1 RIGHT OUTER JOIN
(
(
(
dbo.tb_2 INNER JOIN
dbo.tb_3
ON dbo.tb_2.RowGuid = dbo.tb_3.RowGuid
) INNER JOIN
(
(
(
dbo.tb_4 INNER JOIN
(
dbo.tb_5 INNER JOIN
dbo.tb_6
ON dbo.tb_5.ProjectGuid = dbo.tb_6.ProjectGuid
)
ON dbo.tb_4.BiaoDuanGuid = dbo.tb_6.BiaoDuanGuid
) INNER JOIN
dbo.tb_7
ON dbo.tb_6.BiaoDuanGuid = dbo.tb_7.BiaoDuanGuid
) INNER JOIN
dbo.tb_8 ON dbo.tb_6.BiaoDuanGuid = dbo.tb_8.BiaoDuanGuid
)
ON dbo.tb_2.BiaoDuanGuid = dbo.tb_6.BiaoDuanGuid
) LEFT OUTER JOIN
(
dbo.tb_9 INNER JOIN
dbo.tb_10
ON dbo.tb_9.DangAnGuid = dbo.tb_10.GuiDangGuid
)
ON dbo.tb_6.BiaoDuanGuid = dbo.tb_10.BiaoDuanGuid
)
ON dbo.tb_1.BiaoDuanGuid = dbo.tb_6.BiaoDuanGuid
这是本机数据库视图中包含的表的数据
select count(*) from tb_1 --6640
select count(*) from tb_2 --28074
select count(*) from tb_3 --28066
select count(*) from tb_4 --46202
select count(*) from tb_5 --22505
select count(*) from tb_6 --46144
select count(*) from tb_7 --46199
select count(*) from tb_8 --36731
select count(*) from tb_9 --11784
select count(*) from tb_10 --15439
表的数据可能会变的很大,现在这样查询本机大约要9秒。在服务器上就死机了可能大约要几十分钟,因为服务器上数据量很大。
这种视图我该如何优化呢,求解
------解决方案--------------------SQL code
SELECT *
FROM dbo.tb_1
RIGHT OUTER JOIN ( ( ( dbo.tb_2
INNER JOIN dbo.tb_3 ON dbo.tb_2.RowGuid = dbo.tb_3.RowGuid --RowGuid列建立索引
)
INNER JOIN ( ( ( dbo.tb_4
INNER JOIN ( dbo.tb_5
INNER JOIN dbo.tb_6 ON dbo.tb_5.ProjectGuid = dbo.tb_6.ProjectGuid --ProjectGuid列建立索引
) ON dbo.tb_4.BiaoDuanGuid = dbo.tb_6.BiaoDuanGuid --BiaoDuanGuid列建立索引
)
INNER JOIN dbo.tb_7 ON dbo.tb_6.BiaoDuanGuid = dbo.tb_7.BiaoDuanGuid --BiaoDuanGuid列建立索引
)
INNER JOIN dbo.tb_8 ON dbo.tb_6.BiaoDuanGuid = dbo.tb_8.BiaoDuanGuid --BiaoDuanGuid列建立索引
) ON dbo.tb_2.BiaoDuanGuid = dbo.tb_6.BiaoDuanGuid ----BiaoDuanGuid列建立索引
)
LEFT OUTER JOIN ( dbo.tb_9
INNER JOIN dbo.tb_10 ON dbo.tb_9.DangAnGuid = dbo.tb_10.GuiDangGuid --GuiDangGuid列建立索引
) ON dbo.tb_6.BiaoDuanGuid = dbo.tb_10.BiaoDuanGuid ----BiaoDuanGuid列建立索引
) ON dbo.tb_1.BiaoDuanGuid = dbo.tb_6.BiaoDuanGuid ----BiaoDuanGuid列建立索引
------解决方案--------------------
怎么这么多子查询?
------解决方案--------------------
------解决方案--------------------