合并字段,for xml与函数的比较

看博客园也有一段时间了,光看不练,没有明显的提升,现在终于决定写一点东西了,菜鸟一个,求各位大神指点哈

  先说下需求背景,订单表分为主表和明细表,主表是订单的信息,子表是订单的货品详细信信息,现在有一个报表需要显示订单的信息和子订单的货品代号,用”;”隔开.

例如订单

orderid orderdate

1                      2013-12-07

orderid   prdno

1  prdno-1-1

1  prdno-1-2

  期望的结果

  orderid     orderdate      prdno

1               2013-12-07  prdno-1-1; prdno-1-2;

先准备好测试的数据

--主订单表
create table orders 
(
orderid int ,
orderdate datetime
 CONSTRAINT [PK__orders] PRIMARY KEY CLUSTERED 
    (
        orderid ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
)
--子订单表
create table order_prds
(
  orderid int,
  prdno nvarchar(200)
)
CREATE NONCLUSTERED INDEX [PK_prd_no] ON order_prds 
(
    orderid ASC
)
--插入数据
declare @i int 
declare @m int 
set @i=1
set @m=1
while(@i<=100000)--插入10W个订单
begin
  insert into orders
  select @i,GETDATE()
  while(@m<=7)--每个订单插入7个货品
  begin
    insert into order_prds
    select @i,'prdno-'+CAST(@i as varchar(10))+'-'+CAST(@m as varchar(10))
    
    set @m=@m+1
  end
  set @m=1
  set @i=@i+1
end

--拼接字段的函数
create Function [dbo].[GetOrderPrdno]
(   
   @orderid int
)
returns nvarchar(4000)
as 
begin
   declare @prd_no nvarchar(4000);
   set @prd_no= ''; 
   select @prd_no = @prd_no+prdno+';' 
   from  order_prds where orderid=@orderid
   return @prd_no
end

这里创建了两个简单的表,插入了10W的数据

先看下用函数查询所需要的时间:

declare @bdd datetime
set @bdd=GETDATE()
select orderid,orderdate,[dbo].[GetOrderPrdno](a.orderid) from orders a 
select DATEDIFF(ms,@bdd,getdate())

执行结果大概9400MS

然后再看下用for xml方式所需要的时间:

declare @bdd datetime
set @bdd=GETDATE()
select orderid,orderdate,(select prdno+';' from order_prds b where a.orderid=b.orderid for xml path('')) from orders a 
select DATEDIFF(ms,@bdd,getdate())

执行时间大概要6400MS

从实验结果来看,for xml执行的效率是比函数快了不少,当然也可能是我的函数写得不好,如果大家还有更好的方法,请指点一下