首页 新闻 搜索 专区 学院

怎么提高这个存储过程的执行速度

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;
小光的主页 小光 | 小虾三级 | 园豆:1862
提问于:2016-06-14 15:44
< >
分享
最佳答案
0

分布优化

1.优化查询:添加索引,使用临时表

2.优化插入:批量插入

 

小光 | 小虾三级 |园豆:1862 | 2016-06-15 18:32
其他回答(3)
0

多少分钟你能接受?

收获园豆:25
爱编程的大叔 | 园豆:30768 (高人七级) | 2016-06-14 15:52

最多一个小时吧,我以前在银行做的几百万数据才40分钟,我写的这个不给表加索引行么

支持(0) 反对(0) 小光 | 园豆:1862 (小虾三级) | 2016-06-14 16:00

@小光: 原来你居然可以接受超过1分钟的同步...

那就好办了.

1、加大内存

2、换个好CPU

3、换SSD硬盘

 

顺便说下,查询超过1~2秒我就不能接受了。

1个小时的话,这已经不叫同步了,反正都这么慢了,也不差那几分钟了。

如果可以接受1小时同步的话,晚上的时候用SQL AGENT自动执行一下脚本就好了。

一般晚上也比较少人使用服务器,速度也会快点。

 

在生产服务器上运行这个,难道你们的服务器平时这么闲?

支持(0) 反对(0) 爱编程的大叔 | 园豆:30768 (高人七级) | 2016-06-14 16:05

@爱编程的大叔: 我们打算每天定时执行一下脚本了,没有加索引或者sql优化的建议么。。。顺便问下,当where后面有两个条件列,是分别在每个列加索引,还是给两个列加一个索引,忘了

支持(0) 反对(0) 小光 | 园豆:1862 (小虾三级) | 2016-06-14 16:10

@小光: 你看楼下的文章吧,可以自行简单测试就有答案的问题就不要问了。

支持(0) 反对(0) 爱编程的大叔 | 园豆:30768 (高人七级) | 2016-06-14 16:30
0
收获园豆:25
dudu | 园豆:36366 (高人七级) | 2016-06-14 16:09
0

换个思路吧,这种循环单条插入不适合海量数据插入,可以考虑SqlBulkCopy去做批量插入,把数据读取到datatable里,每1000条插入一次,这样效果应该好点。

lxysoft | 园豆:204 (菜鸟二级) | 2016-06-16 10:47
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册