存储过程语句如下
里面有多张千万数据的表
就是把一个人的数据转移给另外一个人
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS OFF
GO
ALTER procedure [dbo].[SP_UserDataChange]
(
@NewUserid varchar(40),
@NewUserName varchar(20),
@NewMemberName varchar(20),
@OldUserid varchar(40)
)
as
begin TRANSACTION
update T_EfficiencyList set Userid=@NewUserid,UserName=@NewUserName,MemberName=@NewMemberName where Userid=@OldUserid
if @@Error <> 0 goto ErrMsg
update T_EfficiencyList set PDR=@NewUserid where PDR=@OldUserid
if @@Error <> 0 goto ErrMsg
update T_EfficiencyList set PTR=@NewUserid where PTR=@OldUserid
if @@Error <> 0 goto ErrMsg
update T_EfficiencyList set GSR=@NewUserid where GSR=@OldUserid
if @@Error <> 0 goto ErrMsg
update T_EfficiencyList set DCLR=@NewUserid where DCLR=@OldUserid
if @@Error <> 0 goto ErrMsg
update T_EfficiencyList set YFSWJSR=@NewUserid where YFSWJSR=@OldUserid
if @@Error <> 0 goto ErrMsg
update T_EfficiencyList set AuthorizeR=@NewUserid where AuthorizeR=@OldUserid
if @@Error <> 0 goto ErrMsg
update T_EfficiencyList set FXFHR=@NewUserid where FXFHR=@OldUserid
if @@Error <> 0 goto ErrMsg
update T_EfficiencyList set VerifyR=@NewUserid where VerifyR=@OldUserid
if @@Error <> 0 goto ErrMsg
update T_EfficiencyList set FSR=@NewUserid where FSR=@OldUserid
if @@Error <> 0 goto ErrMsg
update T_EfficiencyList set TranslateR=@NewUserid where TranslateR=@OldUserid
if @@Error <> 0 goto ErrMsg
update T_EfficiencyList set SQFHR=@NewUserid where SQFHR=@OldUserid
if @@Error <> 0 goto ErrMsg
update T_EfficiencyList set FYSHR=@NewUserid where FYSHR=@OldUserid
if @@Error <> 0 goto ErrMsg
update T_EfficiencyList set FXVerifyR=@NewUserid where FXVerifyR=@OldUserid
if @@Error <> 0 goto ErrMsg
update T_EfficiencAreaPersonAnalysis set Userid=@NewUserid,UserName=@NewUserName,MemberName=@NewMemberName where Userid=@OldUserid
if @@Error <> 0 goto ErrMsg
update T_EfficiencyAvTypePersonAnalysis set Userid=@NewUserid,UserName=@NewUserName,MemberName=@NewMemberName where Userid=@OldUserid
if @@Error <> 0 goto ErrMsg
update T_EfficiencyPersonAnalysis set Userid=@NewUserid,UserName=@NewUserName,MemberName=@NewMemberName where Userid=@OldUserid
if @@Error <> 0 goto ErrMsg
update T_DataDispense set Operator=@NewUserid,OperatorName=@NewUserName where Operator=@OldUserid
if @@Error <> 0 goto ErrMsg
update T_DataDispenseSum set Userid=@NewUserid,UserName=@NewUserName where Userid=@OldUserid
if @@Error <> 0 goto ErrMsg
update T_CheckEvaluation set MakerId=@NewUserid,Maker=@NewUserName where MakerId=@OldUserid
if @@Error <> 0 goto ErrMsg
update T_CheckEvaluation set Userid=@NewUserid where Userid=@OldUserid
if @@Error <> 0 goto ErrMsg
update T_CheckEvaluation_tj set MakerId=@NewUserid,Maker=@NewUserName where MakerId=@OldUserid
if @@Error <> 0 goto ErrMsg
update T_CheckEvaluation_tj set Userid=@NewUserid where Userid=@OldUserid
if @@Error <> 0 goto ErrMsg
update T_Annex set MakerId=@NewUserid,Maker=@NewUserName where MakerId=@OldUserid
if @@Error <> 0 goto ErrMsg
update T_AccreditOperation set MakerId=@NewUserid,Maker=@NewUserName where MakerId=@OldUserid
if @@Error <> 0 goto ErrMsg
update T_SideVerifyRecords set MakerId=@NewUserid,Maker=@NewUserName where MakerId=@OldUserid
if @@Error <> 0 goto ErrMsg
update T_InitiateState set MakerId=@NewUserid,Maker=@NewUserName where MakerId=@OldUserid
if @@Error <> 0 goto ErrMsg
update MS_Message set Sender=@NewUserid where Sender=@OldUserid
if @@Error <> 0 goto ErrMsg
update MS_Message set Receiver=@NewUserid where Receiver=@OldUserid
if @@Error <> 0 goto ErrMsg
update RE_StateFeed set MakerId=@NewUserid,Maker=@NewUserName where MakerId=@OldUserid
if @@Error <> 0 goto ErrMsg
update T_Operationlog set MakerId=@NewUserid,Maker=@NewUserName where MakerId=@OldUserid
if @@Error <> 0 goto ErrMsg
update T_OperationNodeLog set MakerId=@NewUserid,Maker=@NewUserName where MakerId=@OldUserid
if @@Error <> 0 goto ErrMsg
update T_HonorRoll set Userid=@NewUserid,UserName=@NewUserName,MemberName=@NewMemberName where Userid=@OldUserid
if @@Error <> 0 goto ErrMsg
update T_FEEDBACKLOG set Userid=@NewUserid,UserName=@NewUserName,MemberName=@NewMemberName where Userid=@OldUserid
if @@Error <> 0 goto ErrMsg
update T_DGZHHS set MakerId=@NewUserid,Maker=@NewUserName where MakerId=@OldUserid
if @@Error <> 0 goto ErrMsg
update T_DataCollectInfoLog set MakerId=@NewUserid,Maker=@NewUserName where MakerId=@OldUserid
if @@Error <> 0 goto ErrMsg
update T_DataCollectEngError set MakerId=@NewUserid,Maker=@NewUserName where MakerId=@OldUserid
if @@Error <> 0 goto ErrMsg
commit TRANSACTION;
return;
errmsg:
rollback TRANSACTION;
GO
------解决思路----------------------
对于相同的表，为什么不在一条update中完成相关字段的update操作呢？
另外，这些更新的字段有索引吗？如果没有必要（查询用不到，或者没啥查询），drop掉这些索引。
------解决思路----------------------
这么多表看着也是醉了,
既然有千万数据量的表,那更新,查询,删除的时候索引就非常重要了,任何一处遗漏就会导致性能问题.
不妨逐行调试一下看哪一句最慢.
------解决思路----------------------
既然有这种需求，一开始就应该定义一个角色表，给每个角色对应一个用户。
T_EfficiencyList 中用角色ID，并且直接不存储各种name。——name也存在角色表中。
你要变换用户只要改角色表一条记录就完成了。
现在的结构么，
------解决思路----------------------
你也已经把他放到一个存储过程了，相比一条条更新也优化了不少。毕竟1000万的数据量在那里，查询1000万的数据都比较费劲，更何况是更新。也是没法改动业务，楼主试试