为什么执行效率如此低？

[crownsoft]

我的目的是将A数据库符合条件的数据Copy到B数据库，其中用了游标，需要Copy的数据大约10000条，经测试（在笔记本上Centrino1.6M,256M,MS SQL 7.0）,一分钟才copy20-40条数据，竟然要处理几个小时，要命!请各位大大指点指点！！

代码如下：


declare @NewDB varchar(100), @TableName varchar(100), @EndDate datetime, @ProduceNo varchar(100)

set @NewDB='Carton'
set @EndDate='2005-8-1'

exec PRO_DeliverySum @EndDate

--符合条件的所有单号（ProduceNo）先插入到临时表#A
SELECT OrderInDet.ProduceNo
into #A
FROM OrderIn INNER JOIN OrderInDet
ON OrderIn.OrderInNo = OrderInDet.OrderInNo LEFT OUTER JOIN DeliverySumTemp
ON OrderInDet.ProduceNo = DeliverySumTemp.ProduceNo LEFT OUTER JOIN PdtInSum
ON OrderInDet.ProduceNo = PdtInSum.ProduceNo LEFT OUTER JOIN PdtOutDet
ON OrderInDet.ProduceNo = PdtOutDet.ProduceNo
GROUP BY OrderInDet.ProduceNo, OrderIn.CusIDK, OrderIn.OptDate,
OrderInDet.QTY, PdtInSum.PdtInQTY, DeliverySumTemp.QTY
HAVING ((OrderInDet.QTY - ISNULL(DeliverySumTemp.QTY, 0) > 0) or
(ISNULL(PdtInSum.PdtInQTY, 0)-ISNULL(SUM(PdtOutDet.QTY), 0)-ISNULL(DeliverySumTemp.QTY, 0)>0))
declare @TempNo varchar(100)
declare @Counts int, @i int
declare #CopyData cursor for
select ProduceNo from #A

set @i = 0
open #CopyData
--利用游标分别插入到不同表中
fetch next from #CopyData into @ProduceNo
while @@fetch_status=0
begin
--础璹虫
if @i=0
begin
insert into Carton.dbo.OrderIn select * from OrderIn
end
insert into Carton.dbo.OrderInDet select * from OrderInDet where ProduceNo= @ProduceNo
insert into Carton.dbo.QualityNeed select * from QualityNeed where ProduceNo= @ProduceNo

--础ネ玻璸购
if @i=0
begin

insert into Carton.dbo.ProPlan select * from ProPlan
end

insert into Carton.dbo.ProPlanDet(ProPlanNo, ProArrangeNo, ProduceNo, CusIDK, PoNo, ItemNo, ItemName,
CusCarton, CartonID, Quality, Units, Len, Wid, Hig, OrderQTY, PlanQTY,
PrintPlanQTY, CutQlyPlanQTY, CutCorPlanQTY, BeerPlanQTY, NailPlanQTY,
OrderDate, SendDate, QlyWid1, QlyLen1, QlyQTY1, QlyWid2, QlyLen2, QlyQTY2,
PdtQTY, ProCutQly, ProPrint, ProCutCor, ProBeer, ProNail, ProProcess, ProDate,
PrintMoNo, PrintInkNo, PackRemark)
select ProPlanNo, ProArrangeNo, ProduceNo, CusIDK, PoNo, ItemNo, ItemName,
CusCarton, CartonID, Quality, Units, Len, Wid, Hig, OrderQTY, PlanQTY,
PrintPlanQTY, CutQlyPlanQTY, CutCorPlanQTY, BeerPlanQTY, NailPlanQTY,
OrderDate, SendDate, QlyWid1, QlyLen1, QlyQTY1, QlyWid2, QlyLen2, QlyQTY2,
PdtQTY, ProCutQly, ProPrint, ProCutCor, ProBeer, ProNail, ProProcess, ProDate,
PrintMoNo, PrintInkNo, PackRemark from ProPlanDet where ProduceNo= @ProduceNo

--础潦虫
if @i=0
begin
insert into Carton.dbo.Purchase select * from Purchase
end

insert into Carton.dbo.PurchaseDet(PurchaseNo, BatID, ProduceNo, Quality, QlyWid, QlyLen, QTY, SamQTY, PerPrice,
isSpecPrice, Discount, UnitPrice, UnitWeight, Amount, Weight, isNotOrderIn, Remark,
Remark1, CusID, ChangeFlag)
select PurchaseNo, BatID, ProduceNo, Quality, QlyWid, QlyLen, QTY, SamQTY, PerPrice,
isSpecPrice, Discount, UnitPrice, UnitWeight, Amount, Weight, isNotOrderIn, Remark,
Remark1, CusID, ChangeFlag from PurchaseDet where ProduceNo= @ProduceNo


--础狾畐
if @i=0
begin
insert into Carton.dbo.QlyIn select * from QlyIn
end

insert into Carton.dbo.QlyInDet(QlyInNo, BatID, PurchaseNo, ProduceNo, Quality, QlyWid, QlyLen, PurQTY, QTY,
SamQTY, PerPrice, Discount, UnitPrice, UnitWeight, Amount, Weight, Place, Remark, CusID)
select QlyInNo, BatID, PurchaseNo, ProduceNo, Quality, QlyWid, QlyLen, PurQTY, QTY,
SamQTY, PerPrice, Discount, UnitPrice, UnitWeight, Amount, Weight, Place, Remark, CusID
from QlyInDet where ProduceNo= @ProduceNo

--础狾畐
if @i=0
begin
insert into Carton.dbo.QlyOut select * from QlyOut
end

insert into Carton.dbo.QlyOutDet
select * from QlyOutDet where ProduceNo= @ProduceNo

--础Θ珇畐
if @i=0
begin
insert into Carton.dbo.PdtIn select * from PdtIn
end

insert into Carton.dbo.PdtInDet
select * from PdtInDet where ProduceNo= @ProduceNo

--础Θ珇畐
insert into Carton.dbo.PdtOutDet
select * from PdtOutDet where ProduceNo= @ProduceNo

--础癳砯逼虫
if @i=0
begin
insert into Carton.dbo.DelArrange select * from DelArrange
end

insert into Carton.dbo.DelArrangeDet(DelArrangeNo, ProduceNo, CusIDK, PoNo, ItemNo, ItemName, CusCarton,
CartonID, CusQuality, CusUnit, CusLen, CusWid, CusHig, OrderQTY, ArrQTY, DelQTY,
OrderDate, SendDate, Remark, UnitPrice, Amount)
select DelArrangeNo, ProduceNo, CusIDK, PoNo, ItemNo, ItemName, CusCarton,
CartonID, CusQuality, CusUnit, CusLen, CusWid, CusHig, OrderQTY, ArrQTY, DelQTY,
OrderDate, SendDate, Remark, UnitPrice, Amount
from DelArrangeDet where ProduceNo= @ProduceNo

--础癳砯
if @i=0
begin
insert into Carton.dbo.Delivery select * from Delivery
end

insert into Carton.dbo.DeliveryDet(DeliveryNo, ProduceNo, PoNo, ItemNo, ItemName, CartonID, CusCarton,
CusQuality, CusUnit, CusLen, CusWid, CusHig, QTY, SamQTY, PerPrice, isSpecPrice,
isSendDatePrice, UnitPrice, UnitWeight, Amount, Weight, QuoteType, AtcPrice,
AtcPriceType, DK, CJK, Remark, UnitCub, CubM)
select DeliveryNo, ProduceNo, PoNo, ItemNo, ItemName, CartonID, CusCarton,
CusQuality, CusUnit, CusLen, CusWid, CusHig, QTY, SamQTY, PerPrice, isSpecPrice,
isSendDatePrice, UnitPrice, UnitWeight, Amount, Weight, QuoteType, AtcPrice,
AtcPriceType, DK, CJK, Remark, UnitCub, CubM
from DeliveryDet where ProduceNo= @ProduceNo

--础祇布
if @i=0
begin
insert into Carton.dbo.Invoice select * from Invoice
end

insert into Carton.dbo.InvoiceDet(InvoiceNo, DeliveryNo, ProduceNo, PoNo, ItemNo, ItemName, CartonID,
CusCarton, CusQuality, CusUnit, CusLen, CusWid, CusHig, QTY, PerPrice,
isSpecPrice, UnitPrice, UnitWeight, Amount, Weight, QuoteType, AtcPrice,
AtcPriceType, DK, CJK, DelDate, Remark)
select InvoiceNo, DeliveryNo, ProduceNo, PoNo, ItemNo, ItemName, CartonID,
CusCarton, CusQuality, CusUnit, CusLen, CusWid, CusHig, QTY, PerPrice,
isSpecPrice, UnitPrice, UnitWeight, Amount, Weight, QuoteType, AtcPrice,
AtcPriceType, DK, CJK, DelDate, Remark
from InvoiceDet where ProduceNo= @ProduceNo

--础班诀璸购
if @i=0
begin
insert into Carton.dbo.BeerPlan select * from BeerPlan
end

insert into Carton.dbo.BeerPlanDet(BeerPlanNo, ProPlanNo, ProduceNo, CusIDK, PoNo, ItemNo, ItemName,
CusCarton, CartonID, Quality, Units, Len, Wid, Hig, OrderQTY, PlanQTY,
BeerPlanQTY, OrderDate, SendDate, QlyWid1, QlyLen1, QlyQTY1, QlyWid2, QlyLen2,
QlyQTY2, PdtQTY, ProProcess, BeerQTY)
select BeerPlanNo, ProPlanNo, ProduceNo, CusIDK, PoNo, ItemNo, ItemName,
CusCarton, CartonID, Quality, Units, Len, Wid, Hig, OrderQTY, PlanQTY,
BeerPlanQTY, OrderDate, SendDate, QlyWid1, QlyLen1, QlyQTY1, QlyWid2, QlyLen2,
QlyQTY2, PdtQTY, ProProcess, BeerQTY
from BeerPlanDet where ProduceNo= @ProduceNo

set @i = @i + 1
print @i
fetch next from #CopyData into @ProduceNo
end

close #CopyData
deallocate #CopyData

drop table #A

[WangZWang]

相对于上面的古文 ～～

[LouisXIV]

游标是很慢的，我在测试服务器上生成10w行测试数据，用最简单的游标语法也花了30多分钟-_-

[撸大湿]

删除了游标
删除了无用几个变量
删除了无用的IF判断


declare @NewDB varchar(100), @TableName varchar(100), @EndDate datetime, @ProduceNo varchar(100)

set @NewDB='Carton'
set @EndDate='2005-8-1'

exec PRO_DeliverySum @EndDate

--符合条件的所有单号（ProduceNo）先插入到临时表#A
SELECT OrderInDet.ProduceNo
into #A
FROM OrderIn INNER JOIN OrderInDet
ON OrderIn.OrderInNo = OrderInDet.OrderInNo LEFT OUTER JOIN DeliverySumTemp
ON OrderInDet.ProduceNo = DeliverySumTemp.ProduceNo LEFT OUTER JOIN PdtInSum
ON OrderInDet.ProduceNo = PdtInSum.ProduceNo LEFT OUTER JOIN PdtOutDet
ON OrderInDet.ProduceNo = PdtOutDet.ProduceNo
GROUP BY OrderInDet.ProduceNo, OrderIn.CusIDK, OrderIn.OptDate,
OrderInDet.QTY, PdtInSum.PdtInQTY, DeliverySumTemp.QTY
HAVING ((OrderInDet.QTY - ISNULL(DeliverySumTemp.QTY, 0) > 0) or
(ISNULL(PdtInSum.PdtInQTY, 0)-ISNULL(SUM(PdtOutDet.QTY), 0)-ISNULL(DeliverySumTemp.QTY, 0)>0))

insert into Carton.dbo.OrderIn select * from OrderIn
insert into Carton.dbo.OrderInDet select * from OrderInDet a where exists( select 1 from #A where ProduceNo=a.ProduceNo)
insert into Carton.dbo.QualityNeed select * from QualityNeed a where exists( select 1 from #A where ProduceNo=a.ProduceNo)

insert into Carton.dbo.ProPlan select * from ProPlan
insert into Carton.dbo.ProPlanDet(ProPlanNo, ProArrangeNo, ProduceNo, CusIDK, PoNo, ItemNo, ItemName,
CusCarton, CartonID, Quality, Units, Len, Wid, Hig, OrderQTY, PlanQTY,
PrintPlanQTY, CutQlyPlanQTY, CutCorPlanQTY, BeerPlanQTY, NailPlanQTY,
OrderDate, SendDate, QlyWid1, QlyLen1, QlyQTY1, QlyWid2, QlyLen2, QlyQTY2,
PdtQTY, ProCutQly, ProPrint, ProCutCor, ProBeer, ProNail, ProProcess, ProDate,
PrintMoNo, PrintInkNo, PackRemark)
select ProPlanNo, ProArrangeNo, ProduceNo, CusIDK, PoNo, ItemNo, ItemName,
CusCarton, CartonID, Quality, Units, Len, Wid, Hig, OrderQTY, PlanQTY,
PrintPlanQTY, CutQlyPlanQTY, CutCorPlanQTY, BeerPlanQTY, NailPlanQTY,
OrderDate, SendDate, QlyWid1, QlyLen1, QlyQTY1, QlyWid2, QlyLen2, QlyQTY2,
PdtQTY, ProCutQly, ProPrint, ProCutCor, ProBeer, ProNail, ProProcess, ProDate,
PrintMoNo, PrintInkNo, PackRemark from ProPlanDet a where exists( select 1 from #A where ProduceNo=a.ProduceNo)

insert into Carton.dbo.Purchase select * from Purchase
insert into Carton.dbo.PurchaseDet(PurchaseNo, BatID, ProduceNo, Quality, QlyWid, QlyLen, QTY, SamQTY, PerPrice,
isSpecPrice, Discount, UnitPrice, UnitWeight, Amount, Weight, isNotOrderIn, Remark,
Remark1, CusID, ChangeFlag)
select PurchaseNo, BatID, ProduceNo, Quality, QlyWid, QlyLen, QTY, SamQTY, PerPrice,
isSpecPrice, Discount, UnitPrice, UnitWeight, Amount, Weight, isNotOrderIn, Remark,
Remark1, CusID, ChangeFlag from PurchaseDet a where exists( select 1 from #A where ProduceNo=a.ProduceNo)
insert into Carton.dbo.QlyIn select * from QlyIn
insert into Carton.dbo.QlyInDet(QlyInNo, BatID, PurchaseNo, ProduceNo, Quality, QlyWid, QlyLen, PurQTY, QTY,
SamQTY, PerPrice, Discount, UnitPrice, UnitWeight, Amount, Weight, Place, Remark, CusID)
select QlyInNo, BatID, PurchaseNo, ProduceNo, Quality, QlyWid, QlyLen, PurQTY, QTY,
SamQTY, PerPrice, Discount, UnitPrice, UnitWeight, Amount, Weight, Place, Remark, CusID
from QlyInDet a where exists( select 1 from #A where ProduceNo=a.ProduceNo)

insert into Carton.dbo.QlyOut select * from QlyOut
insert into Carton.dbo.QlyOutDet
select * from QlyOutDet a where exists( select 1 from #A where ProduceNo=a.ProduceNo)

insert into Carton.dbo.PdtIn select * from PdtIn
insert into Carton.dbo.PdtInDet
select * from PdtInDet a where exists( select 1 from #A where ProduceNo=a.ProduceNo)


insert into Carton.dbo.PdtOutDet
select * from PdtOutDet a where exists( select 1 from #A where ProduceNo=a.ProduceNo)

insert into Carton.dbo.DelArrange select * from DelArrange
insert into Carton.dbo.DelArrangeDet(DelArrangeNo, ProduceNo, CusIDK, PoNo, ItemNo, ItemName, CusCarton,
CartonID, CusQuality, CusUnit, CusLen, CusWid, CusHig, OrderQTY, ArrQTY, DelQTY,
OrderDate, SendDate, Remark, UnitPrice, Amount)
select DelArrangeNo, ProduceNo, CusIDK, PoNo, ItemNo, ItemName, CusCarton,
CartonID, CusQuality, CusUnit, CusLen, CusWid, CusHig, OrderQTY, ArrQTY, DelQTY,
OrderDate, SendDate, Remark, UnitPrice, Amount
from DelArrangeDet a where exists( select 1 from #A where ProduceNo=a.ProduceNo)

insert into Carton.dbo.Delivery select * from Delivery
insert into Carton.dbo.DeliveryDet(DeliveryNo, ProduceNo, PoNo, ItemNo, ItemName, CartonID, CusCarton,
CusQuality, CusUnit, CusLen, CusWid, CusHig, QTY, SamQTY, PerPrice, isSpecPrice,
isSendDatePrice, UnitPrice, UnitWeight, Amount, Weight, QuoteType, AtcPrice,
AtcPriceType, DK, CJK, Remark, UnitCub, CubM)
select DeliveryNo, ProduceNo, PoNo, ItemNo, ItemName, CartonID, CusCarton,
CusQuality, CusUnit, CusLen, CusWid, CusHig, QTY, SamQTY, PerPrice, isSpecPrice,
isSendDatePrice, UnitPrice, UnitWeight, Amount, Weight, QuoteType, AtcPrice,
AtcPriceType, DK, CJK, Remark, UnitCub, CubM
from DeliveryDet a where exists( select 1 from #A where ProduceNo=a.ProduceNo)

insert into Carton.dbo.Invoice select * from Invoice
insert into Carton.dbo.InvoiceDet(InvoiceNo, DeliveryNo, ProduceNo, PoNo, ItemNo, ItemName, CartonID,
CusCarton, CusQuality, CusUnit, CusLen, CusWid, CusHig, QTY, PerPrice,
isSpecPrice, UnitPrice, UnitWeight, Amount, Weight, QuoteType, AtcPrice,
AtcPriceType, DK, CJK, DelDate, Remark)
select InvoiceNo, DeliveryNo, ProduceNo, PoNo, ItemNo, ItemName, CartonID,
CusCarton, CusQuality, CusUnit, CusLen, CusWid, CusHig, QTY, PerPrice,
isSpecPrice, UnitPrice, UnitWeight, Amount, Weight, QuoteType, AtcPrice,
AtcPriceType, DK, CJK, DelDate, Remark
from InvoiceDet a where exists( select 1 from #A where ProduceNo=a.ProduceNo)

insert into Carton.dbo.BeerPlan select * from BeerPlan
insert into Carton.dbo.BeerPlanDet(BeerPlanNo, ProPlanNo, ProduceNo, CusIDK, PoNo, ItemNo, ItemName,
CusCarton, CartonID, Quality, Units, Len, Wid, Hig, OrderQTY, PlanQTY,
BeerPlanQTY, OrderDate, SendDate, QlyWid1, QlyLen1, QlyQTY1, QlyWid2, QlyLen2,
QlyQTY2, PdtQTY, ProProcess, BeerQTY)
select BeerPlanNo, ProPlanNo, ProduceNo, CusIDK, PoNo, ItemNo, ItemName,
CusCarton, CartonID, Quality, Units, Len, Wid, Hig, OrderQTY, PlanQTY,
BeerPlanQTY, OrderDate, SendDate, QlyWid1, QlyLen1, QlyQTY1, QlyWid2, QlyLen2,
QlyQTY2, PdtQTY, ProProcess, BeerQTY
from BeerPlanDeta where exists( select 1 from #A where ProduceNo=a.ProduceNo)

drop table #A

[oop80]

建议你分段调试，确定耗时的操作在哪一步

[crownsoft]

先多谢各位的提点
我先试试

[rouqu]

我晕 帖子拉了半天才见底

[crownsoft]

多谢tntzbzc(华裔大魔王—抗日英雄—抗日要从娃娃抓起)!
现在不用两分钟就完成了!!!

结帖....