怎么提高这个存储过程的执行速度
0
悬赏园豆：50
[已解决问题]
解决于 2016-06-15 18:33
都说游标速度慢，我都换成临时表了，30w数据，40分钟才插入了7w2，帮忙看看哪里有问题
declare @gsmc varchar(50) declare @prjkey varchar(50) declare @prjsum varchar(50) declare @khbh varchar(50) declare @gsdz varchar(50) declare @sum varchar(50) declare @gsdz_key varchar(50) declare @gsdz_sum varchar(50) declare @floor_key varchar(50) declare @floor_name varchar(50) declare @floor_sum varchar(50) declare @floor_code varchar(50) declare @room_key varchar(50) declare @room_name varchar(50) declare @room_sum varchar(50) declare @room_code varchar(50) declare @cabinet_key varchar(50) declare @cabinet_name varchar(50) declare @cabinet_sum varchar(50) declare @cabinet_code varchar(50) declare @key varchar(50) declare @datatype char(1) declare @weizhixinxi_key varchar(50) declare @root_key varchar(50) DECLARE @ID INT DECLARE @i INT DECLARE @count INT DECLARE @i2 INT DECLARE @count2 INT DECLARE @i3 INT DECLARE @count3 INT DECLARE @i4 INT DECLARE @count4 INT DECLARE @i5 INT DECLARE @count5 INT DECLARE @i6 INT DECLARE @count6 INT DECLARE @i7 INT DECLARE @count7 INT --同步项目 create table #a(Sid int identity(1,1),"key" varchar(50),"text" varchar(50)) insert into #a("key","text") select prjkey,prjname from VisualNet_Project select @count = COUNT(1) from #a set @i = 1; while(@i<=@count) begin select @prjkey = "key",@gsmc = "text" from #a where Sid = @i select @root_key="key" from VisualNet_tvwPrj where prjkey = @prjkey and "relative" = 'root' create table #b(Sid int identity(1,1),"key" varchar(50),"text" varchar(50)) insert into #b("key","text") select "key","text" from VisualNet_tvwPrj where prjkey = @prjkey and "relative"= @root_key select @count2 = COUNT(1) from #b set @i2 = 1; while(@i2<=@count2) begin select @gsdz_key = "key",@gsdz = "text" from #b where Sid = @i2 select @sum = COUNT(1) from VisualNetKey where PrjectKey = @prjkey and CustomerAddress = @gsdz and DeleteFlg = 0 select @khbh = CustomerCode from VisualNetKey where PrjectKey = @prjkey and CustomerAddress = @gsdz and DeleteFlg = 0 if(@sum = 0) begin select @prjsum=COUNT(1) from Customers where gsmc = @gsmc and gsdz = @gsdz if(@prjsum = 0) begin select @khbh = MAX(khbh+1) from Customers if(@khbh is null) begin set @khbh = '0001' end else begin set @khbh = RIGHT(('0000'+@khbh),4) end insert into Customers(khbh,gsmc,gsdz,gsdh) values(@khbh,@gsmc,@gsdz,'') insert into VisualNetKey(PrjectKey,ItemKey,CustomerCode,CustomerAddress,DataType,SyncFlg,DeleteFlg) values(@prjkey,'',@khbh,@gsdz,0,1,0) end else begin select @khbh=khbh from Customers where gsmc = @gsmc and gsdz = @gsdz insert into VisualNetKey(PrjectKey,ItemKey,CustomerCode,CustomerAddress,DataType,SyncFlg,DeleteFlg) values(@prjkey,'',@khbh,@gsdz,0,1,0) end end else begin update VisualNetKey set SyncFlg = 1 where PrjectKey = @prjkey and CustomerCode = @khbh and CustomerAddress = @gsdz and DataType = 0 end set @i2=@i2+1 end --位置信息 select @weizhixinxi_key = "key" from VisualNet_tvwPrj where prjkey = @prjkey and "relative" = 'root' --地址 create table #c(Sid int identity(1,1),"key" varchar(50),"text" varchar(50)) insert into #c("key","text") select "key","text" from VisualNet_tvwPrj where prjkey = @prjkey and "relative" = @weizhixinxi_key select @count3 = COUNT(1) from #c set @i3 = 1; while(@i3<=@count3) begin select @gsdz_key = "key",@gsdz = "text" from #b where Sid = @i3 select @gsdz_sum = COUNT(1) from VisualNetKey where ItemKey = @gsdz_key and DeleteFlg = 0 select @gsdz = CustomerAddress from VisualNetKey where ItemKey = @gsdz_key and DeleteFlg = 0 if(@gsdz_sum = 0) begin insert into VisualNetKey(PrjectKey,ItemKey,CustomerCode,CustomerAddress,DataType,SyncFlg,DeleteFlg) values(@prjkey,@gsdz_key,@khbh,@gsdz,1,1,0) end else begin update VisualNetKey set SyncFlg = 1 where PrjectKey = @prjkey and CustomerCode = @khbh and CustomerAddress = @gsdz and DataType = 1 end --楼层 create table #d(Sid int identity(1,1),"key" varchar(50),"text" varchar(50)) insert into #d("key","text") select "key","text" from VisualNet_tvwPrj where prjkey = @prjkey and "relative" = @gsdz_key select @count4 = COUNT(1) from #d set @i4 = 1; while(@i4<=@count4) begin select @floor_key = "key",@floor_name = "text" from #d where Sid = @i4 select @floor_sum = COUNT(1) from VisualNetKey where ItemKey = @floor_key and PrjectKey = @prjkey and CustomerAddress = @gsdz and DeleteFlg = 0 select @floor_code = FloorCode from VisualNetKey where ItemKey = @floor_key and PrjectKey = @prjkey and CustomerAddress = @gsdz and DeleteFlg = 0 if(@floor_sum = 0) begin select @floor_code = MAX(floorcode+1) from CustomersFloor if(@floor_code is null) begin set @floor_code = '000001' end else begin set @floor_code = RIGHT(('000000'+@floor_code),6) end select @khbh = khbh from Customers where gsmc = @gsmc and gsdz = @gsdz insert into CustomersFloor(customercode,floorcode,floorname,floordevice) values(@khbh,@floor_code,@floor_name,0) insert into VisualNetKey(PrjectKey,ItemKey,CustomerCode,CustomerAddress,FloorCode,DataType,SyncFlg,DeleteFlg) values(@prjkey,@floor_key,@khbh,@gsdz,@floor_code,2,1,0) end else begin update CustomersFloor set floorname = @floor_name where floorcode = @floor_code update VisualNetKey set SyncFlg = 1 where FloorCode = @floor_code and DataType = 2 end --房间 create table #e(Sid int identity(1,1),"key" varchar(50),"text" varchar(50)) insert into #e("key","text") select "key","text" from VisualNet_tvwPrj where prjkey = @prjkey and "relative" = @floor_key select @count5 = COUNT(1) from #e set @i5 = 1; while(@i5<=@count5) begin select @room_key = "key",@room_name = "text" from #e where Sid = @i5 select @room_sum = COUNT(1) from VisualNetKey where ItemKey = @room_key and PrjectKey = @prjkey and CustomerAddress = @gsdz and DeleteFlg = 0 select @room_code = RoomCode from VisualNetKey where ItemKey = @room_key and PrjectKey = @prjkey and CustomerAddress = @gsdz and DeleteFlg = 0 if(@room_sum = 0) begin select @room_code = MAX(roomcode+1) from CustomersRoom if(@room_code is null) begin set @room_code = '000001' end else begin set @room_code = RIGHT(('000000'+@room_code),6) end select @khbh = khbh from Customers where gsmc = @gsmc and gsdz = @gsdz insert into CustomersRoom(customercode,floorcode,roomcode,roomname,roomdevice) values(@khbh,@floor_code,@room_code,@room_name,0) insert into VisualNetKey(PrjectKey,ItemKey,CustomerCode,CustomerAddress,FloorCode,RoomCode,DataType,SyncFlg,DeleteFlg) values(@prjkey,@room_key,@khbh,@gsdz,@floor_code,@room_code,3,1,0) end else begin update CustomersRoom set roomname = @room_name where roomcode = @room_code update VisualNetKey set SyncFlg = 1 where RoomCode = @room_code and DataType =3 end --机柜 create table #f(Sid int identity(1,1),"key" varchar(50),"text" varchar(50)) insert into #f("key","text") select "key","text" from VisualNet_tvwPrj where prjkey = @prjkey and "relative" = @room_key select @count6 = COUNT(1) from #f set @i6 = 1; while(@i6<=@count6) begin select @cabinet_key = "key",@cabinet_name = "text" from #f where Sid = @i6 select @cabinet_sum = COUNT(1) from VisualNetKey where ItemKey = @cabinet_key and PrjectKey = @prjkey and CustomerAddress = @gsdz and DeleteFlg = 0 select @cabinet_code = CabinetCode from VisualNetKey where ItemKey = @cabinet_key and PrjectKey = @prjkey and CustomerAddress = @gsdz and DeleteFlg = 0 if(@cabinet_sum = 0) begin select @cabinet_code = MAX(cabinetcode+1) from CustomersCabinet if(@cabinet_code is null) begin set @cabinet_code = '000001' end else begin set @cabinet_code = RIGHT(('000000'+@cabinet_code),6) end select @khbh = khbh from Customers where gsmc = @gsmc and gsdz = @gsdz insert into CustomersCabinet(customercode,floorcode,roomcode,cabinetcode,cabinetname,cabinetdevice) values(@khbh,@floor_code,@room_code,@cabinet_code,@cabinet_name,0) insert into VisualNetKey(PrjectKey,ItemKey,CustomerCode,CustomerAddress,FloorCode,RoomCode,CabinetCode,DataType,SyncFlg,DeleteFlg) values(@prjkey,@cabinet_key,@khbh,@gsdz,@floor_code,@room_code,@cabinet_code,4,1,0) end else begin update CustomersCabinet set cabinetname = @cabinet_name where cabinetcode = @cabinet_code update VisualNetKey set SyncFlg = 1 where CabinetCode = @cabinet_code and DataType =4 end set @i6=@i6+1 end drop table #f set @i5=@i5+1 end drop table #e set @i4=@i4+1 end drop table #d set @i3=@i3+1 end drop table #c drop table #b set @i=@i+1 end drop table #a update VisualNetKey set DeleteFlg = 1 where SyncFlg = 0; create table #g(Sid int identity(1,1),PrjectKey char(50),ItemKey char(50),CustomerCode char(4),CustomerAddress nvarchar(100),FloorCode char(6),RoomCode char(6),CabinetCode char(6),datatype int) insert into #g(PrjectKey,ItemKey,CustomerCode,CustomerAddress,FloorCode,RoomCode,CabinetCode,datatype) select PrjectKey,ItemKey,CustomerCode,CustomerAddress,FloorCode,RoomCode,CabinetCode,datatype from VisualNetKey where DeleteFlg = 1 select @count7 = COUNT(1) from #g set @i7 = 1; while(@i7<=@count7) begin select @prjkey=PrjectKey,@key=ItemKey,@khbh=CustomerCode,@gsdz=CustomerAddress,@floor_code=FloorCode,@room_code=RoomCode,@cabinet_code=CabinetCode,@datatype=datatype from #g where Sid = @i7 if(@datatype = 2) begin delete from CustomersFloor where floorcode = @floor_code end if(@datatype = 3) begin delete from CustomersRoom where roomcode = @room_code end if(@datatype = 4) begin delete from CustomersCabinet where cabinetcode = @cabinet_code delete from AssetSb where lxcode = @cabinet_code end end drop table #g update VisualNetKey set SyncFlg = 0;
最佳答案
0
分布优化
1.优化查询：添加索引，使用临时表
2.优化插入：批量插入
其他回答(3)
0
多少分钟你能接受？
收获园豆：25
最多一个小时吧，我以前在银行做的几百万数据才40分钟，我写的这个不给表加索引行么
@小光: 原来你居然可以接受超过1分钟的同步...
那就好办了.
1、加大内存
2、换个好CPU
3、换SSD硬盘
顺便说下，查询超过1~2秒我就不能接受了。
1个小时的话，这已经不叫同步了，反正都这么慢了，也不差那几分钟了。
如果可以接受1小时同步的话，晚上的时候用SQL AGENT自动执行一下脚本就好了。
一般晚上也比较少人使用服务器，速度也会快点。
在生产服务器上运行这个，难道你们的服务器平时这么闲？
@爱编程的大叔: 我们打算每天定时执行一下脚本了，没有加索引或者sql优化的建议么。。。顺便问下，当where后面有两个条件列，是分别在每个列加索引，还是给两个列加一个索引，忘了
@小光: 你看楼下的文章吧，可以自行简单测试就有答案的问题就不要问了。
0
0
换个思路吧，这种循环单条插入不适合海量数据插入，可以考虑SqlBulkCopy去做批量插入，把数据读取到datatable里，每1000条插入一次，这样效果应该好点。
