SQL2000中存储过程,调用10多万的记录,从中取得数据。在“SQL查询分析器”中,两种方式执行
1、exec UP_GetResidueToTable 执行时间需要1个多小时完成
2、将存储过程中的所有代码,即删除过程最上两行,执行时间需9分钟多点请问,是同样的代码,为什么用时不同呢?如何使exec UP_GetResidueToTable这种方式用时少点?
代码如下:CREATE procedure UP_GetResidueToTable
asif OBJECT_ID('ut_residue') IS NULL
create table ut_residue
(item nvarchar(30),
QtyRealResidue decimal(19,8),
QtyPlnResidue decimal(19,8),
QtyReq decimal(19,8),
QtyOnHand decimal(19,8),
QtyRecvPO decimal(19,8),
QtyRecvPPLN decimal(19,8))
else
delete ut_residueselect PlanningDetailsView.item,DueDateDay,QtyOnHand,QtyReq,QtyRecv,MrpRef into #PlanningDetails_p
from PlanningDetailsView
left outer join item on item.item=PlanningDetailsView.item
where item.p_m_t_code='p'insert ut_residue(item) select item from Item where p_m_t_code='p'update ut_residue set ut_residue.QtyReq=a.QtyReq from
(select item,sum(QtyReq) as QtyReq from #PlanningDetails_p group by item) a
where ut_residue.item=a.itemupdate ut_residue set ut_residue.QtyOnHand=a.QtyOnHand from
(select item,QtyOnHand from #PlanningDetails_p where DueDateDay is null) a
where ut_residue.item=a.itemupdate ut_residue set ut_residue.QtyRecvPO=a.QtyRecvPO from
(select item,sum(QtyRecv) as QtyRecvPO from #PlanningDetails_p where substring(MrpRef,1,2)='PO' group by item) a
where ut_residue.item=a.itemupdate ut_residue set ut_residue.QtyRecvPPLN=a.QtyRecvPPLN from
(select item,sum(QtyRecv) as QtyRecvPPLN from #PlanningDetails_p where substring(MrpRef,1,3)='PLN' group by item) a
where ut_residue.item=a.itemupdate ut_residue set ut_residue.QtyRealResidue=isnull(QtyOnHand,0)+isnull(QtyRecvPO,0)-isnull(QtyReq,0) update ut_residue set ut_residue.QtyPlnResidue=isnull(QtyOnHand,0)+isnull(QtyRecvPO,0)-isnull(QtyReq,0)+isnull(QtyRecvPPLN,0)drop table #PlanningDetails_pdeclare @MrpEndDate datetime
if OBJECT_ID('UT_ResidueRptMRPEndDate') IS NULL
create table UT_ResidueRptMRPEndDate
(MrpEndDate datetime)
else
delete UT_ResidueRptMRPEndDateset @MrpEndDate=(select top 1 CompletionDate from BGTaskHistory where TaskName = 'MRPPlanning'
ORDER BY CompletionDate DESC)insert UT_ResidueRptMRPEndDate(MrpEndDate) values(@MrpEndDate)GO
1、exec UP_GetResidueToTable 执行时间需要1个多小时完成
2、将存储过程中的所有代码,即删除过程最上两行,执行时间需9分钟多点请问,是同样的代码,为什么用时不同呢?如何使exec UP_GetResidueToTable这种方式用时少点?
代码如下:CREATE procedure UP_GetResidueToTable
asif OBJECT_ID('ut_residue') IS NULL
create table ut_residue
(item nvarchar(30),
QtyRealResidue decimal(19,8),
QtyPlnResidue decimal(19,8),
QtyReq decimal(19,8),
QtyOnHand decimal(19,8),
QtyRecvPO decimal(19,8),
QtyRecvPPLN decimal(19,8))
else
delete ut_residueselect PlanningDetailsView.item,DueDateDay,QtyOnHand,QtyReq,QtyRecv,MrpRef into #PlanningDetails_p
from PlanningDetailsView
left outer join item on item.item=PlanningDetailsView.item
where item.p_m_t_code='p'insert ut_residue(item) select item from Item where p_m_t_code='p'update ut_residue set ut_residue.QtyReq=a.QtyReq from
(select item,sum(QtyReq) as QtyReq from #PlanningDetails_p group by item) a
where ut_residue.item=a.itemupdate ut_residue set ut_residue.QtyOnHand=a.QtyOnHand from
(select item,QtyOnHand from #PlanningDetails_p where DueDateDay is null) a
where ut_residue.item=a.itemupdate ut_residue set ut_residue.QtyRecvPO=a.QtyRecvPO from
(select item,sum(QtyRecv) as QtyRecvPO from #PlanningDetails_p where substring(MrpRef,1,2)='PO' group by item) a
where ut_residue.item=a.itemupdate ut_residue set ut_residue.QtyRecvPPLN=a.QtyRecvPPLN from
(select item,sum(QtyRecv) as QtyRecvPPLN from #PlanningDetails_p where substring(MrpRef,1,3)='PLN' group by item) a
where ut_residue.item=a.itemupdate ut_residue set ut_residue.QtyRealResidue=isnull(QtyOnHand,0)+isnull(QtyRecvPO,0)-isnull(QtyReq,0) update ut_residue set ut_residue.QtyPlnResidue=isnull(QtyOnHand,0)+isnull(QtyRecvPO,0)-isnull(QtyReq,0)+isnull(QtyRecvPPLN,0)drop table #PlanningDetails_pdeclare @MrpEndDate datetime
if OBJECT_ID('UT_ResidueRptMRPEndDate') IS NULL
create table UT_ResidueRptMRPEndDate
(MrpEndDate datetime)
else
delete UT_ResidueRptMRPEndDateset @MrpEndDate=(select top 1 CompletionDate from BGTaskHistory where TaskName = 'MRPPlanning'
ORDER BY CompletionDate DESC)insert UT_ResidueRptMRPEndDate(MrpEndDate) values(@MrpEndDate)GO
那可能就是delete ut_residue的问题
as我说的删除两行是上面这两行啊!