SQL Server 2005 中新CTE语法 递归性能测试

qq524061227

　　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行数据
http://files.iyunv.com/elzero/images/08090403.jpg
　　
　　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
　　
　　测试结果:
http://files.iyunv.com/elzero/images/08090401.jpg
　　从图可以看出用旧的 While 方法只用了 453ms，而新的的CTE递归用了8530ms，测试同一机种，返回的结果都是646行（即这个机种用了多少个半成品或物料)
　　细心的朋友可能会发现，两种方法返回的结果(line和sid列)不一样，是的。因为CTE递归创建数据时，不变成员和递归成员的数据结构要完全一样，包括数据类型、长度与精确位数(不然会通不过编译，如下图，用老方法就没这个规定)。如果要把CTE递归返回的结果要和旧的一样line显示层次结构、sid显示物料层次，那还要用Convert把line和sid在递归部分给改上，如果再用Convert，那CTE会不会更慢？我也懒得试了
http://files.iyunv.com/elzero/images/08090402.jpg
　　从执行时间看453和8530根本不在同等级，不知道是不是我CTE的用法不对，还是什么原因，因为我想MS不会出现这样低级的错误吧，望各位sql牛人指教