选择物料清单的记录of

Question

我的数据库中有一个部件的层次结构。例如，一个部分(称为0010102-0112-315)是一个电阻器。它被用于十亿个不同的顶级程序集。我的最终目标是获取与低级别ID(例如0010102-0112-315)相关的所有顶级产品ID。

我目前的方法是将表放到自己上8次(超过最大产品深度的1次)，当recordId after是NULL时，它必须是顶级的recordId。

但是，这个查询需要7分钟才能运行，我有一种感觉我做错了什么。

据我所知，我不能使用CTE，因为我们使用的是SQL Server 2003 (CTE之前)。有更好的方法吗？

以下是我的当前代码：

SELECT I1.IMA_ItemID as ItemID1, 
    I1.IMA_ProdFam as ProdFam1
    ,I1.IMA_RecordID as RecordId1 -- same as PSH.PSH_IMA_RecordID
    ,I2.IMA_RecordID as RecordId2
    ,I3.IMA_RecordID as RecordId3
    ,I4.IMA_RecordID as RecordId4
    ,I5.IMA_RecordID as RecordId5
    ,I6.IMA_RecordID as RecordId6
    ,I7.IMA_RecordID as RecordId7
    ,I8.IMA_RecordID as RecordId8
    ,I9.IMA_RecordID as RecordId9
FROM   Item AS I1
    left join ProductStructureHeader AS PSH1 ON PSH1.PSH_IMA_RecordID = I1.IMA_RecordID
    left join ProductStructure AS PS1 on PS1.PST_PSH_RecordID = PSH1.PSH_RecordID
    left join Item AS I2 ON I2.IMA_RecordID = PS1.PST_IMA_RecordID

    left join ProductStructureHeader AS PSH2 ON PSH2.PSH_IMA_RecordID = I2.IMA_RecordID
    left join ProductStructure AS PS2 on PS2.PST_PSH_RecordID = PSH2.PSH_RecordID
    left join Item AS I3 ON I3.IMA_RecordID = PS2.PST_IMA_RecordID

    left join ProductStructureHeader AS PSH3 ON PSH3.PSH_IMA_RecordID = I3.IMA_RecordID
    left join ProductStructure AS PS3 on PS3.PST_PSH_RecordID = PSH3.PSH_RecordID
    left join Item AS I4 ON I4.IMA_RecordID = PS3.PST_IMA_RecordID          

    left join ProductStructureHeader AS PSH4 ON PSH4.PSH_IMA_RecordID = I4.IMA_RecordID
    left join ProductStructure AS PS4 on PS4.PST_PSH_RecordID = PSH4.PSH_RecordID
    left join Item AS I5 ON I5.IMA_RecordID = PS4.PST_IMA_RecordID          

    left join ProductStructureHeader AS PSH5 ON PSH5.PSH_IMA_RecordID = I5.IMA_RecordID
    left join ProductStructure AS PS5 on PS5.PST_PSH_RecordID = PSH5.PSH_RecordID
    left join Item AS I6 ON I6.IMA_RecordID = PS5.PST_IMA_RecordID          

    left join ProductStructureHeader AS PSH6 ON PSH6.PSH_IMA_RecordID = I6.IMA_RecordID
    left join ProductStructure AS PS6 on PS6.PST_PSH_RecordID = PSH6.PSH_RecordID
    left join Item AS I7 ON I7.IMA_RecordID = PS6.PST_IMA_RecordID          

    left join ProductStructureHeader AS PSH7 ON PSH7.PSH_IMA_RecordID = I7.IMA_RecordID
    left join ProductStructure AS PS7 on PS7.PST_PSH_RecordID = PSH7.PSH_RecordID
    left join Item AS I8 ON I8.IMA_RecordID = PS7.PST_IMA_RecordID

    left join ProductStructureHeader AS PSH8 ON PSH8.PSH_IMA_RecordID = I8.IMA_RecordID
    left join ProductStructure AS PS8 on PS8.PST_PSH_RecordID = PSH8.PSH_RecordID
    left join Item AS I9 ON I9.IMA_RecordID = PS8.PST_IMA_RecordID

其他一些可能有用的模糊事实：

• 上面的RecordId1是最高级别的RecordId。
• 选择*其中IMA_ItemStatusCode = 'Active' and IMA_ItemTypeCode = 'Purchased Item'从Item表中给出最低级别的项
• 我想不出别的了，但你可以自由地问，因为可能会有什么事。

Answer 1

以下是几个问题：

1. 你试过在你的加入中倒序吗？即从PS=>PSH=>Item开始。
2. 你试过内部连接和左连接吗？可以看到速度的提高(一定会减少数据)
3. 有什么办法只对物品进行回收吗？换句话说，您能从项目行中获取PS1.PST_IMA_RecordID吗？如果是这样，那么您将只在一个表上递归。

Answer 2

或者您可以使用临时表并在其上进行递归。这将节省大量的数据获取。类似于：

use mydb

CREATE TABLE #myTmpTable 
   ( 
        IMA_RecordID uniqueidentifier default null
        , PST_IMA_RecordID uniqueidentifier default null
   )

-- Put recordID and parentID in temp table
insert into #myTmpTable
select 
    I1.IMA_RecordID
    , PS1.PST_IMA_RecordID
FROM   Item AS I1
    left join ProductStructureHeader AS PSH1 ON PSH1.PSH_IMA_RecordID = I1.IMA_RecordID
    left join ProductStructure AS PS1 on PS1.PST_PSH_RecordID = PSH1.PSH_RecordID
    --left join Item AS I2 ON I2.IMA_RecordID = PS1.PST_IMA_RecordID

-- Now you can recurse on temp table
SELECT t1.IMA_ItemID as ItemID1, 
    --I1.IMA_ProdFam as ProdFam1 --you can left join this from item
     t1.IMA_RecordID as RecordId1
    ,t2.IMA_RecordID as RecordId2
    ,t3.IMA_RecordID as RecordId3
    ,t4.IMA_RecordID as RecordId4
    ,t5.IMA_RecordID as RecordId5
    ,t6.IMA_RecordID as RecordId6
    ,t7.IMA_RecordID as RecordId7
    ,t8.IMA_RecordID as RecordId8
    ,t9.IMA_RecordID as RecordId9
from #myTmpTable t1
inner join #myTmpTable t2 on t2.IMA_RecordID = t1.PST_IMA_RecordID
inner join #myTmpTable t3 on t3.IMA_RecordID = t2.PST_IMA_RecordID
inner join #myTmpTable t4 on t4.IMA_RecordID = t3.PST_IMA_RecordID
inner join #myTmpTable t5 on t5.IMA_RecordID = t4.PST_IMA_RecordID
inner join #myTmpTable t6 on t6.IMA_RecordID = t5.PST_IMA_RecordID
inner join #myTmpTable t7 on t7.IMA_RecordID = t6.PST_IMA_RecordID
inner join #myTmpTable t8 on t8.IMA_RecordID = t7.PST_IMA_RecordID
inner join #myTmpTable t9 on t9.IMA_RecordID = t8.PST_IMA_RecordID

DROP TABLE #myTmpTable