求高手帮俺看看如何优化这段存储过程代码，谢谢。

Question

此存储过程用于系统查询某个模块的报表，如果时间差长一点，查询将近要半个多小时才出来结果，所以请求各位牛哥在线帮忙。  CREATE proc usp_Report_DmReqOrdersAndItemVm  
--@compId int,  
@timeBegin varchar(23),  
@timeEnd varchar(23),  
@i_wherestr varchar(max)  
as begin  
  
--按单据时间查  
--加几个字段：商务批准数量，总仓出货数量，商务未保障原因，仓库未保障原因  
  
 --exec usp_Report_DmReqOrdersAndItemVm '2010-03-01','2010-08-30',''  
 --exec usp_Report_DmReqOrdersAndItemVm '2008-12-01','2009-05-01',' and t3.itemno=''1.01.1.07.3.1'' and hrdp.description=''直营'''  
  
  
--1.原始申请单  
create TABLE #order(  
 billdate datetime/*单据(下单)日期*/,dealtime datetime/*处理时间*/  
 ,ReqDeptID int,ItemInfoID int  
 ,DmReqOrdersid int,DMReqItemDetailsid int  
 ,PurchaseNum decimal(24,8)/*订货数量*/,DealNum decimal(24,8)/*处理数量*/  
 ,DealRe nvarchar(400)/*处理备注*/  
)    
  
declare @sql varchar(8000)  
set @sql='  
insert into #order   
select   
t1.billdate,t1.dealtime  
,t1.ReqDeptID,t2.itemid  
,t1.DmReqOrdersid,t2.DMReqItemDetailsid  
,t2.PurchaseNum  
,t2.DealNum  
,t2.DealRe  
from   
--配送调拨申请单  
DmReqOrders t1, DMReqItemDetails t2  
, basiteminfo t3, bizentity t4, hrdeptment t5,BasDeptRegion t6    
,hrdeptprop hrdp  
where   
t1.deletedflag = 0   
--and t1.billstatus not in(1)   
and docstatus=5  
and t1.DmReqOrdersid = t2.DmReqOrdersid and t1.ReqDeptID=t4.bizentityid and t4.deletedflag = 0   
and t1.ReqDeptID = t5.orgid and t1.ReqDeptID = t6.DeptId and t2.deletedflag = 0   
and t2.itemid = t3.basiteminfoid and t3.deletedflag = 0    
and t5.deptpropid = hrdp.hrdeptpropid  
and t1.billdate>='''+@timeBegin+''' and t1.billdate<'''+@timeEnd+'''  
'+@i_wherestr  
--print @sql  
  
exec(@sql)  
  
--多连几个表方便加查询条件  
  
  
--IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE [NAME] LIKE '#tb_fourCol%') DR OP TABLE #tb_fourCol  
  
--2.获得门店、商品 组合的：商务批准数量，总仓出货数量，商务未保障原因，仓库未保障原因  
CREATE TABLE #tb_fourCol
(
      ReqDeptID INT ,
      ItemInfoID INT ,
      SwNum DECIMAL(24, 8) ,
      ckNum DECIMAL(24, 8) ,
      swNotProtectCause NVARCHAR(4000) ,
      ckNotProtectCause NVARCHAR(4000)
)  
INSERT  INTO #tb_fourCol
        ( ReqDeptID ,
          ItemInfoID
        )
        SELECT DISTINCT
                ReqDeptID ,
                ItemInfoID
        FROM    #order  
  
DECLARE @ReqDeptID int,@ItemInfoID int  DECLARE twoidCursor CURSOR FOR select ReqDeptID,ItemInfoID from #tb_fourCol  
OPEN twoidCursor  
FETCH NEXT FROM twoidCursor INTO @ReqDeptID,@ItemInfoID  
WHILE @@FETCH_STATUS = 0  
BEGIN  
 DECLARE @SwNum decimal(24,8),@swNotProtectCause nvarchar(4000),@ckNum decimal(24,8),@ckNotProtectCause nvarchar(4000)  
 SET @SwNum=0  
 SET @swNotProtectCause=''  
 SET @ckNum=0  
 SET @ckNotProtectCause=''  
   
 select   
 @SwNum=@SwNum+swNum  
 ,@swNotProtectCause=@swNotProtectCause+(case when CHARINDEX(swNotProtectCause,@swNotProtectCause)>=1 then '' else swNotProtectCause end)  
 from (  
  --多个申请明细对应同一批准处理的时候，数量不可重复  
  select distinct ddbt.DmDistributeDetailid  
  ,(isnull(ddbt.NumA,0)+isnull(ddbt.NumB,0)) as swNum --商务批准数量  
  ,(case when ddbt.NotProtectCause>1 then npcDes.description else '' end) as swNotProtectCause --商务未保障原因  
  from DmDistributeDetail ddbt,(  
   --该门店、商品的申请id  
   select DMReqItemDetailsid   
   from #order od  
   where od.ReqDeptID=@ReqDeptID and od.ItemInfoID=@ItemInfoID   
  )ritid  
  ,BizNotProtectCause npcDes  
  where ddbt.deptid=@ReqDeptID and ddbt.ItemInfoID=@ItemInfoID   
  and ddbt.deletedflag=0  
  AND ddbt.NotProtectCause=npcDes.BizNotProtectCauseid  
  and CHARINDEX(cast(ritid.DMReqItemDetailsid as nvarchar(10)), ddbt.reqitemslist)>=1  
 )distinctDmDistributeDetail  
  
 --测试环境，总仓出货数量为0是因为  
 select   
 @ckNum=@ckNum+(isnull(D.ActualANum,0) + isnull(D.ActualBNum,0))-- as ckNum  
 --@ckNum=@ckNum+(isnull(D.ANum,0) + isnull(D.BNum,0))  
-- ,@ckNotProtectCause=@ckNotProtectCause+npcDes.description-- as ckNotProtectCause   
 ,@ckNotProtectCause=@ckNotProtectCause+(case when (npcDes.description is null or CHARINDEX(npcDes.description,@ckNotProtectCause)>=1) then '' else npcDes.description end)  
  from (  
  --找出对应 DMItemsDistrBillid  
  select distinct ddbt.DMItemsDistrBillid  
  from DmDistributeDetail ddbt,(  
   --该门店、商品的申请id  
   select DMReqItemDetailsid   
   from #order od  
   where od.ReqDeptID=@ReqDeptID and od.ItemInfoID=@ItemInfoID   
  )ritid  
  --,BizNotProtectCause npcDes  
  where ddbt.deptid=@ReqDeptID and ddbt.ItemInfoID=@ItemInfoID   
  and ddbt.deletedflag=0  
  --AND ddbt.NotProtectCause=npcDes.BizNotProtectCauseid  
  and CHARINDEX(cast(ritid.DMReqItemDetailsid as nvarchar(10)), ddbt.reqitemslist)>=1  
 )distinctDMItemsDistrBillid  
 inner join DMItemsDistrBill with(nolock) on distinctDMItemsDistrBillid.DMItemsDistrBillid=DMItemsDistrBill.DMItemsDistrBillid and DMItemsDistrBill.deletedflag=0  
 inner join SmWholesaleBill whbill with(nolock) on whbill.SrcBillNo=DMItemsDistrBill.BillNo and whbill.deletedflag=0  
 inner join SmWholesaleDetail D with(nolock) on D.SmWholesaleBillid=whbill.SmWholesaleBillid and D.deletedflag=0  
 left join WhNotProtectCause npcDes on D.NotProtectCause=npcDes.WhNotProtectCauseid  
 where    
 whbill.customerID=@ReqDeptID--DeptID    
 and D.ItemInfoID=@ItemInfoID  
  
 UPDATE #tb_fourCol   
 SET   
 SwNum=@SwNum  
 ,swNotProtectCause=@swNotProtectCause   
 ,ckNum=@ckNum  
 ,ckNotProtectCause=@ckNotProtectCause  
 WHERE ReqDeptID=@ReqDeptID AND ItemInfoID=@ItemInfoID  
  
 FETCH NEXT FROM twoidCursor INTO @ReqDeptID,@ItemInfoID  
END  
CLOSE  twoidCursor  
DEALLOCATE twoidCursor  
  
  
--[#DRP-2452] 如果一张配送调拔申请单被多次汇总并且最后终于给处理完毕了, 这时在报表里看到的相应商品明细的 '商务未保障原因'字段 应该是空内容, 即已经保障了  
update numt   
set swNotProtectCause=''   
from (  
 select   
 ReqDeptID,ItemInfoID  
 ,sum(PurchaseNum) as PurchaseNum,sum(DealNum) as DealNum   
 from #order   
 group by ReqDeptID,ItemInfoID  
) bas,#tb_fourCol numt  
where bas.ReqDeptID=numt.ReqDeptID and bas.ItemInfoID=numt.ItemInfoID and isnull(numt.SwNum,0)>=bas.PurchaseNum  
  
  
--3.返回查询结果  
select   
bas.*   
,t3.itemno, t3.itemname  
,t4.bizentitynum as ReqDeptNo, t4.name as ReqDeptName  
, isnull(hrdp.description,'') as depttype  
,t6.RegionNo as bregionno, t6.RegionName as bregionname   
,isnull(numt.SwNum,0) as SwNum --商务批准数量  
,isnull(numt.ckNum,0) as ckNum --总仓出货数量  
,isnull(numt.swNotProtectCause,'') as swNotProtectCause --商务未保障原因  
,isnull(numt.ckNotProtectCause,'') as ckNotProtectCause --仓库未保障原因  
from (  
 select   
  (select top 1 billdate from #order where ReqDeptID=t1.ReqDeptID and ItemInfoID=t1.ItemInfoID order by billdate desc) as billdate --处理时间取最后一个，一般汇总一次。（mean by nan）  
 ,(select top 1 dealtime from #order where ReqDeptID=t1.ReqDeptID and ItemInfoID=t1.ItemInfoID order by dealtime desc) as dealtime  
 ,ReqDeptID,ItemInfoID  
 ,sum(PurchaseNum) as PurchaseNum,sum(DealNum) as DealNum,DealRe   
 from #order  t1  
 group by ReqDeptID,ItemInfoID,DealRe  
) bas   
inner join basiteminfo t3 on bas.ItemInfoID = t3.basiteminfoid and t3.deletedflag = 0   
inner join bizentity t4 on bas.ReqDeptID=t4.bizentityid and t4.deletedflag = 0   
inner join hrdeptment t5 on bas.ReqDeptID = t5.orgid  
inner join BasDeptRegion t6 on bas.ReqDeptID = t6.DeptId  
left join hrdeptprop hrdp on t5.deptpropid = hrdp.hrdeptpropid  
left join #tb_fourCol numt on bas.ReqDeptID=numt.ReqDeptID and bas.ItemInfoID=numt.ItemInfoID   
  
  
end  

Answer 1

从你写的SQL来看，可以从这几个方面考虑来优化，
1.最好不要用动态SQL;
2.最好不用游标；
3.CHARINDEX(cast(ritid.DMReqItemDetailsid as nvarchar(10)), ddbt.reqitemslist) 效率低，无法使用索引，请检查索引建立情况；
4.少用嵌套查询；
5.临时表如果保持的数据量大，建议建立索引；
6。如果数据量少，可以使用内存表

Answer 2

不要用游标了，用表变量、case啊什么的实现吧