求一个效率比较高的BOM反查方法

Question

有BOM表tab有以下字段
PID CID QTY
现在想找一个效率比较高的BOM反查方法. PID,CID都有建索引我在论坛上看了几个,运行效率都太底了,有时一个BOM反查需要两三分钟才能执行完

Answer 1

-->创建测试环境
DECLARE @t TABLE
(
  FullDept VARCHAR(20),   --部门全称
  Dept VARCHAR(20),       --部门
  ParentDept VARCHAR(20), --上级部门
  Supervisor VARCHAR(20)  --部门主管
)
INSERT @t SELECT 'S-IT'       ,'IT','S'       ,'Peter'
UNION ALL SELECT 'S-IT-CN'    ,'CN','S-IT'    ,'Mary' 
UNION ALL SELECT 'S-IT-CN-SH' ,'SH','S-IT-CN' ,'Jack' 
UNION ALL SELECT 'S-FS-AP'    ,'AP','S-FS'    ,'Colin' 
UNION ALL SELECT 'S-FS'       ,'FS','S'       ,'Jerry' 
UNION ALL SELECT 'S'          ,'0' ,'0'       ,'CiCi'  /*
 SELECT * FROM @t
--------------------
FullDept(部门全称)   Dept(部门)           ParentDept(上级部门) Supervisor(部门主管)
-------------------- -------------------- -------------------- --------------------
S-IT                 IT                   S                    Peter
S-IT-CN              CN                   S-IT                 Mary
S-IT-CN-SH           SH                   S-IT-CN              Jack
S-FS-AP              AP                   S-FS                 Colin
S-FS                 FS                   S                    Jerry
S                    0                    0                    CiCi(6 行受影响)
*/
-->2.使用CTE迭代返回指定子项的父项查询：
--->例1.查询部门主管为"Jack"的所有上级部门清单
;WITH t AS 
(
  SELECT *,0 as lvl FROM @t WHERE Supervisor = 'Jack'
  UNION ALL 
  SELECT a.*,b.lvl+1 FROM @t a JOIN t b /*CTE*/ ON b.ParentDept = a.FullDept 
)
SELECT * FROM t ORDER BY FullDept;
/*
FullDept             Dept                 ParentDept           Supervisor           lvl
-------------------- -------------------- -------------------- -------------------- -----------
S                    0                    0                    CiCi                 3
S-IT                 IT                   S                    Peter                2
S-IT-CN              CN                   S-IT                 Mary                 1
S-IT-CN-SH           SH                   S-IT-CN              Jack                 0(4 行受影响)
*/
--->例2.查询部门主管为"Colin"的所有上级部门清单
;WITH t AS 
(
  SELECT *,0 as lvl FROM @t WHERE Supervisor = 'Colin'
  UNION ALL 
  SELECT a.*,b.lvl+1 FROM @t a JOIN t b /*CTE*/ ON b.ParentDept = a.FullDept )
SELECT * FROM t ORDER BY FullDept;
/*
FullDept             Dept                 ParentDept           Supervisor           lvl
-------------------- -------------------- -------------------- -------------------- -----------
S                    0                    0                    CiCi                 2
S-FS                 FS                   S                    Jerry                1
S-FS-AP              AP                   S-FS                 Colin                0(3 行受影响)
*/

Answer 2

在樓主的表結構也只有用遞歸，只可以兩列建上索引，其它沒有什麼可優化
樓主的BOM數據量是?用存儲過程與函數的性能高