【优化SQL Server循环更新、插入耗时长的问题】

一： 工作当中遇到更新较多数据时，使用循环(while,或游标)进行增删改时，特别费时 

WHILE @i <= @rowsBEGIN 　　SELECT @appNo = AppNumber, @roleid = RoleId, @statusi= Status, @empId = EmployeeId FROM #Atable WHERE ID = @i　　IF EXISTS(SELECT 1 FROM Btable WHERE AppNumber = @appNo AND RoleID = @roleid AND EmployeeId = @empId)       BEGIN      　　UPDATE Btable　　　　SET Status = @statusi, CreatedBy = NULL, ModifiedOn = GETDATE()　　　　WHERE AppNumber = @appNoi AND RoleId = @roleidi AND EmployeeId = @empId　　END　　ELSE　　BEGIN　　　　INSERT INTO Btable(EmployeeId, AppNumber, RoleId, Status, CreatedOn, CreatedBy, ModifiedOn, ModifiedBy)　　　　SELECT EmployeeId, AppNumber, RoleId, Status, GETDATE(), CreatedBy, GETDATE(), ModifiedBy FROM #Atable WHERE ID = @i　　END

　　SELECT @i= @i + 1END

二：利用表左、右连接进行批量删除解决循环增删改耗时长的问题

　　update aru set aru.Status = arul.Status, aru.ModifiedOn = GETDATE() 　　from Btable as aru 　　inner join Atable as arul 　　on aru.AppNumber = arul.AppNumber and aru.RoleId = arul.RoleId and aru.EmployeeID = arul.EmployeeID

三：A,B两张表中有可能数据不相等，可以声明两个变量@count1,@count2

　　DECLARE @Count1 int,@Count2 int 　　select @Count1 = COUNT(1) from Atable 　　select @Count2 = COUNT(1) from Btable

四： 根据@count1和@count2的大小判断是否新增还是删除

IF(@Count1 > @Count2) BEGIN　　 　　SELECT * INTO Btable 　　from Atable as arul 　　left join Btable as aru 　　on aru.AppNumber = arul.AppNumber and aru.RoleId = arul.RoleId and aru.EmployeeID = arul.EmployeeID 　　where aru.AppNumber is null and aru.RoleId is null and aru.EmployeeID is null END ELSE BEGIN 　　update aru set aru.Status = 0 　　from Btable as aru 　　left join Atable as arul 　　on aru.AppNumber = arul.AppNumber and aru.RoleId = arul.RoleId and aru.EmployeeID = arul.EmployeeID 　　where arul.AppNumber is null and arul.RoleId is null and arul.EmployeeID is null END

数据量较多时，又比较、又更新、又新增时，利用循环特别费时，批量更新的效率是循环的几十倍甚至上百倍

 

个人工作中总结出来，如有转载请注明出处！

转载于:https://www.cnblogs.com/chengxiaofei2018/p/10065706.html