sql server语句优化1：去掉循环语句

公司前同事写的一个存储过程，在上生产时数据量少时都没有问题，随着数据量的增大，这个存储过程执行时越来越慢，看了一下，里面用到了大量的嵌套循环，还有嵌套游标。经过对语句的调整，将循环取消，改为结构化批量执行，大大提高运行效率。取自其中一部分代码：

 原代码：

经分析，这段代码中有嵌套循环，

第一层循环：从表中一条条的取数，找到工艺路线

第二层循环：将工艺路线拆分，找到上下工艺后，插入到明细表中

select @DataTableCount=count(*) from #DataTable
if(@DataTableCount>0)
begin
	set @i=1
	while(@i<@DataTableCount+1)
	begin
		select @OrderId=OrderId,@Upi=ProductionDetailExtendEntity_Id,@Routing=Routing,@Barcode=Barcode,@Partposstr=Partposstr from #DataTable where IID=@i

		select @Routing = Routing,@OrderEntity_Id= h.Id,@ProductionDetailExtendEntity_Id = f.Id,
		@OrderId = h.OrderId,@ProductionBatchEntity_Id=a.Id,@Planno=a.PlanNo 
		from T_ProductionBatch a left join T_ProductionBatchDetail b on a.Id=b.ProductionBatchEntity_Id
		left join T_ProductionSolutionOrder c on c.Id=b.Id 
		left join T_BOM_Order h on c.OrderEntity_Id=h.Id
		left join T_BOM_ItemDetail d on c.Id=d.ProductionSolutionOrder_Id 
		left join T_BOM_ItemDetailExtend f on d.Id=f.ProductDetail_Id 
		where f.Id=@Upi

		if object_id('tempdb..#dtOP') Is Not Null
		drop table #dtOP 
        select id=identity(int,1,1),col into #dtOP 
		from dbo.f_SplitStr(@Routing,'_') a inner join T_Operation b on a.col=b.OPCode where b.IsNoWorkOrder=0 
		select @OperationContu=count(*) from #dtOP
		set @j=1
		while(@j<@OperationContu+1)
		begin
			set @LastOPCode=''
			set @NextOPCode=''
			select @OPCode=col from #dtOP where id=@j
			select @LastOPCode=col from #dtOP where id=@j-1
			select @NextOPCode=col from #dtOP where id=@j+1
			select @SequenceNo=Sequence from T_Operation where OPCode=@OPCode
			insert into #T_WorkOrderDetail(OPCode,OrderId,OrderEntity_Id,ProductionDetailExtendEntity_Id,ProductionBatchEntity_Id,SequenceNo,LastOPCode,NextOPCode,Partposstr,Barcode)
			values(@OPCode,@OrderId,@OrderEntity_Id,@ProductionDetailExtendEntity_Id,@ProductionBatchEntity_Id,@SequenceNo,@LastOPCode,@NextOPCode,@Partposstr,@Barcode)

			set @j=@j+1
		end
		set @i=@i+1	 
	end
end   

优化后的代码：

批量找到工艺表

批量插入到明细表中

select @DataTableCount=count(*) from #DataTable
if(@DataTableCount>0)
begin
	insert into  #dtOP(iid ,id,col ,creatdate)
	select a.IID,ROW_NUMBER() over(partition by a.iid order by a.iid) id,b.col,getdate()
	from #DataTable  a
	cross apply dbo.f_SplitStr(a.Routing,'_') b
	order by iid asc 
	--批量插入，取消循环操作
	insert into #T_WorkOrderDetail(OPCode,OrderId,OrderEntity_Id,ProductionDetailExtendEntity_Id,ProductionBatchEntity_Id,SequenceNo,LastOPCode,NextOPCode,Barcode,Partposstr,
									orderstatus , OrderInfo1,OrderInfo2,OrderInfo3,OrderInfo4,OrderInfo5,CreatedTime,
									Colour1,price1,price2,Price3, packageQtys,
									ProductionSolutionEntity_Id,Planno,fleng,fwidth,ProcessingCnt,EdgeLength)
	select b.col OPCode ,a.OrderId,a.OrderEntity_Id, a.ProductionDetailExtendEntity_Id ,a.ProductionBatchEntity_Id, e.Sequence SequenceNo, isnull(c.col,'') LastOPCode ,  isnull(d.col,'') NextOPCode  , a.Barcode , a.Partposstr,
			a.orderstatus ,a.OrderInfo1,a.OrderInfo2,a.OrderInfo3,a.OrderInfo4,a.OrderInfo5,a.CreatedTime,
			a.Colour1,a.price1,a.price2,a.Price3, a.packageQtys,
			a.ProductionSolutionEntity_Id,a.PlanNo,a.fleng,a.fwidth,a.ProcessingCnt,a.EdgeLength
	from #DataTable a 
	inner join  #dtOP b on a.iid = b.iid
	left join	#dtOP c on b.iid = c.iid and b.id -1 = c.id   --上个工序
	left join	#dtOP d on b.iid = d.iid and b.id +1 = d.id   --下个工序
	left join	T_Operation e on b.col = e.OPCode
	order by a.iid asc ,b.id asc
end