如果只是更新效率如何?update b set b.[] = a.[] from A a join B b on a.[] = b.[]这样子。
ALTER PROCEDURE [dbo].[sp_Service_OperationScript_UpdateOperationScriptById] @OperationScriptId char(36), @OperationScriptActualBeginTime datetime=null, @OperationScriptActualEndTime datetime=null, @OperationScriptState int=null, @OperationScriptWarningInfo nvarchar(MAX)=nullAS BEGIN SET NOCOUNT ON UPDATE Service_OperationScript SET OperationScriptActualBeginTime=ISNULL(@OperationScriptActualBeginTime,OperationScriptActualBeginTime), OperationScriptActualEndTime=ISNULL(@OperationScriptActualEndTime,OperationScriptActualEndTime), OperationScriptState=ISNULL(@OperationScriptState,OperationScriptState), OperationScriptWarningInfo=ISNULL(@OperationScriptWarningInfo,OperationScriptWarningInfo) WHERE OperationScriptId = @OperationScriptId END这是我这边写的存储过程,现在是百万级数据,有的时候就会出现超时问题!
WHERE 时间= ...
AND 另外一个外键 = ...IF @@ROWCOUNT <= 0
INSERT TAB (...)
VALUES (...) 这样,减少一次查询,也减少被锁机会
insert into B
select ...
from A t
where not exists (select 1 from B where [] = t.[])
更新
update b
set b.[] = a.[]
from A a join B b on a.[] = b.[]
将要插入的表数据生成Hash tabl,提取对比表的数据也生成HASH TABLE
两表相比,用差量更新
set b.[] = a.[]
from A a join B b on a.[] = b.[]这样子。
ALTER PROCEDURE [dbo].[sp_Service_OperationScript_UpdateOperationScriptById]
@OperationScriptId char(36),
@OperationScriptActualBeginTime datetime=null,
@OperationScriptActualEndTime datetime=null,
@OperationScriptState int=null,
@OperationScriptWarningInfo nvarchar(MAX)=nullAS
BEGIN
SET NOCOUNT ON
UPDATE Service_OperationScript
SET
OperationScriptActualBeginTime=ISNULL(@OperationScriptActualBeginTime,OperationScriptActualBeginTime),
OperationScriptActualEndTime=ISNULL(@OperationScriptActualEndTime,OperationScriptActualEndTime),
OperationScriptState=ISNULL(@OperationScriptState,OperationScriptState),
OperationScriptWarningInfo=ISNULL(@OperationScriptWarningInfo,OperationScriptWarningInfo)
WHERE
OperationScriptId = @OperationScriptId
END这是我这边写的存储过程,现在是百万级数据,有的时候就会出现超时问题!