SQL Server 205针对数据操作语言DML增加了相当多的语法，例如 CTE,Pivot,UnPivot 等，今天想把以前的展开BOM(Bill of Materials)的旧方法用CET实现，看可不可以提高性能，测试最后还是不要动好。CET（Common table expression）兼具视图表和衍生数据表的能力。你可以视之为临时的视图表，或是在同一批处理查询语法中可重复使用的衍生数据表。
先看下要测试的BOM结构,EL_NO是物料或半成品，BO_NO是EL_NO的上阶，BO_USE是用量，EL_INVID是表示是物料还是机种或成品，整张BOM表差不多5W行数据
1.旧的递归方法
ALTER FUNCTION [dbo].[f_bom_dal]
(
@bo_no nvarchar(15)
)
RETURNS
@r Table(
line varchar(255),
el_no nvarchar(15),
el_name nvarchar(20),
bo_use float,
el_invid nchar(1),
level int, --层次
sid varchar(255)) --排序字段,通过这个来排序,可以体现出树形的层次
AS
BEGIN
declare @l int, @ReStr as varchar(50)
select @l=0,@ReStr='';
insert @r select bo_no,el_no,el_name,1,el_invid,@l,@bo_no
from Robo00_dal
where bo_no=@bo_no and bo_use is null
while @@rowcount>0
begin
set @l=@l+1
insert @r select substring(Left(@ReStr,@l)+'├──────────────────',1,8),b.el_no,b.el_name,b.bo_use*r.bo_use,b.el_invid,@l,r.sid+'_'+b.el_no
from robo00_dal b,@r r
where r.level=@l-1
and b.bo_no=r.el_no
and b.bo_use>0
end
RETURN
END
2.改用CET的方法SQL语句
ALTER FUNCTION [dbo].[f_bom_dal_1]
(
@bo_no nvarchar(15)
)
RETURNS
@r Table(
line varchar(255),
el_no nvarchar(15),
el_name nvarchar(20),
bo_use float,
el_invid nchar(1),
level int, --层次
sid varchar(255)) --排序字段,通过这个来排序,可以体现出树形的层次
AS
BEGIN
-- Fill the table variable with the rows for your result set
WITH T(line,el_no,el_name,bo_use,el_invid,level,sid) AS(
SELECT bo_no,el_no,el_name,convert(float,1.0),el_invid,0,@bo_no from Robo00_dal where bo_no=@bo_no and bo_use is null
UNION ALL
SELECT r.bo_no,r.el_no,r.el_name,convert(float,T.bo_use*r.bo_use),r.el_invid,T.level+1 level,r.el_no sid FROM ROBO00_dal r INNER JOIN T ON r.bo_no=T.el_no and r.bo_use>0
)
INSERT INTO @r SELECT * FROM T
RETURN
END
测试结果:
从图可以看出用旧的 While 方法只用了 453ms，而新的的CTE递归用了8530ms，测试同一机种，返回的结果都是646行（即这个机种用了多少个半成品或物料)
细心的朋友可能会发现，两种方法返回的结果(line和sid列)不一样，是的。因为CTE递归创建数据时，不变成员和递归成员的数据结构要完全一样，包括数据类型、长度与精确位数(不然会通不过编译，如下图，用老方法就没这个规定)。如果要把CTE递归返回的结果要和旧的一样line显示层次结构、sid显示物料层次，那还要用Convert把line和sid在递归部分给改上，如果再用Convert，那CTE会不会更慢？我也懒得试了
从执行时间看453和8530根本不在同等级，不知道是不是我CTE的用法不对，还是什么原因，因为我想MS不会出现这样低级的错误吧，望各位sql牛人指教
