求助提高SQL语句展开BOM最底层清单的速度

显示全部楼层 · 2011-2-18 11:43:34

本人菜鸟级别，想计算系统中所有制造物料的实际成本，这样必须提到BOM最底层的物料，用其成本去相加得到制造物料的。
现在的问题是：我参考网上的资料弄了下存储过程，不提供条件时搜索出来的数据有300多万条，用时用了13多分钟。假如给定制造物料的编号，看速度的快慢（最近我们公司服务器时好时坏，资源占用的比较厉害），一般不会超过6秒。
请各位大虾帮我看看能不能提高点速度呀，13分钟太长时间了吧，而且有时服务器资源被别人占用比较厉害时还会超时退出。
比如物料A是由B、C组成的，B由D组成，C由E、F组成，D由E、G组成这种的。
BOM格式如下：

A
//制造物料

B
C
//第一层

D
EF
//第三层

E G
//第四层

…….
表字段 Mitm（主物料）sitm（子件） quality（用量）
A
B
1
A
C
1.6
B
D
2
C
E
2
C
F
9
D
E
4
D
G
3
……….
如此类的
-------------------------------------------------------------------------------------
存储过程如下：
Creat procedure [tempBOM]
@item varchar(100)
as
declare @tempitem varchar(100)
set @tempitem='%'+@item+'%'
DECLARE @l INT
SET @l = 1
select t_mitm,'
' as muitem,
t_sitm, Sum_t_qana = t_qana,Level = @linto [#tempBOMtable] from BOM
where t_mitm like @tempitem
--主项物料下挂哪几层物料，字段muitem为下一层的上级物料（起到给我参考的作用）
WHILE @@ROWCOUNT > 0
BEGIN
SET @l = @l + 1
INSERT #tempBOMtable
SELECT B.t_mitm,B.t_sitm as muitem,A.t_sitm, Sum_t_qana = B.Sum_t_qana * A.t_qana, @l
FROM BOM A, #tempBOMtable B
WHERE A.t_mitm = B.t_sitm
AND B.Level = @l - 1
END
--循环中间层插入临时表
SELECT * into tempBOMExplosion FROM #tempBOMtable A
WHERE NOT EXISTS(
SELECT * FROM BOM
WHERE A.t_sitm = t_mitm)
--最后一级原材料
select * from tempBOMExplosion
------------------------------------------------
--得到最后一级原材料后插入一个临时表tempBOMExplosion ,这样我可以拿这些物料去乘于系统中的实际采购单价，最后汇总即可。
在执行这个存储过程之前，还要做个判断，就是如果系统中存在临时表tempBOMExplosion时，必须删除，这样才能保证计算得到的是最新的物料BOM展开。这个可以放在存储过程中执行吗？
参数@tempitem传单个物料进去时，速度还可以，但是不输入条件的话，就非常非常非常慢。请各位帮帮忙，看看我语句哪里可以优化的，并把改正后的告之（本人技艺不精，不贴出来不懂得哇），小女子拜谢各位大哥大姐大叔大妈，hoho~~~~

新手,刚注册的,所以不能给帮助我的大虾送分，希望各位知道的不要不理呀

千问 · 2011-2-18 11:43:34

帮你顶起，，SQL达人帮忙解答。，嘿嘿。。

千问 · 2011-2-18 11:43:34

各位高手不要吝啬呀，大家多帮帮忙呀

千问 · 2011-2-18 11:43:34

WITH BOM(Father,ItemCode,Quantity,iLevel)
AS
(
SELECT T0.Code,T1.Code ,CONVERT(NUMERIC(23,15), T1.Quantity/T0.Qauntity) Quantity , 1 iLevel
FROM OITT T0
JOIN ITT1 T1 ON T0.Code = T1.Father
UNION ALL
SELECT T0.Father,T2.Code,CONVERT(NUMERIC(23,15),(T2.Quantity/T1.Qauntity)*T0.Quantity) ,T0.iLevel+1
FROM BOM T0
JOIN OITT T1 ON T0.ItemCode = T1.Code
JOIN ITT1 T2 ON T1.Code = T2.Father
)
通过CTE直接生成产品到最终材料的"用量", 是否满足你的需求?

千问 · 2011-2-18 11:43:34

用递归

千问 · 2011-2-18 11:43:34

eric_e

千问 · 2011-2-18 11:43:34

eric_e
你能说得详细点吗?CTE这个我没有用过。
而且看你给我列的语句里，t0表和t1表是二个表？
顺带说下我用sql2005

千问 · 2011-2-18 11:43:34

eric_e

谢谢你
后来我去网上找了下CTE的递归，发现用不了。虽然我是用SQL2005去连，但也只能是当客户端工具，因为我们公司系统本身的数据库版本是2000版的，不支持CTE，所以你提供的方法我用不了。