如果引入一个临时表 将A表的数据转成如下结构 #A id parentid categoryid nodeid deviceid有没有办法优化呢?
临时表在你这里会引入额外的insert和索引方面的开销,性能不见得一定有提升
我是这么写的 SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS RowIndex, ria.ParentCategoryID AS ParentCategoryID, cc.Name as ParentCategory, ria.ParentNodeID AS ParentNodeID, st.Name as ParentNode, ria.ParentDeviceID AS ParentDeviceID, am.Name as ParentDevice, ria.ChildCategoryID AS ChildCategoryID,cc2.Name as ChildCategory, ria.ChildNodeID AS ChildNodeID,st2.Name as ChildNode, ria.ChildDeviceID AS ChildDeviceID,am2.Name as ChildDevice FROM dbo.A as ria left join dbo.B as cc on ria.ParentCategoryID = cc.CategoryID left join dbo.B as cc2 on ria.ChildCategoryID = cc2.CategoryID left join dbo.C as st on ria.ParentNodeID = st.NodeID left join dbo.C as st2 on ria.ChildNodeID = st2.NodeID left join dbo.D as am on ria.ParentDeviceID = am.DeviceID left join dbo.D as am2 on ria.ChildDeviceID = am.DeviceID
将A表的数据转成如下结构
#A
id parentid categoryid nodeid deviceid有没有办法优化呢?
我是这么写的 SELECT
ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS RowIndex,
ria.ParentCategoryID AS ParentCategoryID, cc.Name as ParentCategory,
ria.ParentNodeID AS ParentNodeID, st.Name as ParentNode,
ria.ParentDeviceID AS ParentDeviceID, am.Name as ParentDevice,
ria.ChildCategoryID AS ChildCategoryID,cc2.Name as ChildCategory,
ria.ChildNodeID AS ChildNodeID,st2.Name as ChildNode,
ria.ChildDeviceID AS ChildDeviceID,am2.Name as ChildDevice
FROM dbo.A as ria
left join dbo.B as cc
on ria.ParentCategoryID = cc.CategoryID
left join dbo.B as cc2
on ria.ChildCategoryID = cc2.CategoryID
left join dbo.C as st
on ria.ParentNodeID = st.NodeID
left join dbo.C as st2
on ria.ChildNodeID = st2.NodeID
left join dbo.D as am
on ria.ParentDeviceID = am.DeviceID
left join dbo.D as am2
on ria.ChildDeviceID = am.DeviceID
CTRL-M 查看execution plan都落在了clustered index上。目前测试数据较少,性能不是啥问题。我没有什么sql背景,猛然看到这么多的left join,总有种refactor的冲动