我建立了up_counter存储过程,客服端每30秒钟调用一次up_counter更新数据,通常每次更新的数据40条左右。
实现使用中发现up_counter存储过程挺耗sql server资源如何优化这个存储过程?counter表设置主键:(date,hh,gate)CREATE PROCEDURE [dbo].[up_counter]
@date datetime,
@hh char(2),
@gate char(1),
@up int,
@down int,
@total int,
@net int
AS
BEGIN
if not exists(select * from counter where date=@date and hh=@hh and gateno=@gate)
insert into counter values(@date,@hh,@gate,@up,@down,@total,@net)
else
update counter set up=@up,down=@down,total=@total,net=@net
where date=@date and hh=@hh and gateno=@gate
END
实现使用中发现up_counter存储过程挺耗sql server资源如何优化这个存储过程?counter表设置主键:(date,hh,gate)CREATE PROCEDURE [dbo].[up_counter]
@date datetime,
@hh char(2),
@gate char(1),
@up int,
@down int,
@total int,
@net int
AS
BEGIN
if not exists(select * from counter where date=@date and hh=@hh and gateno=@gate)
insert into counter values(@date,@hh,@gate,@up,@down,@total,@net)
else
update counter set up=@up,down=@down,total=@total,net=@net
where date=@date and hh=@hh and gateno=@gate
END
加了索引對insert會產生影響
那有其他更高效的新表数据方法吗?
SET NOCOUNT ON;不显示操作的记录数
update counter set up=@up,down=@down,total=@total,net=@net
where date=@date and hh=@hh and gateno=@gate
if @@rowcount = 0
insert into counter values(@date,@hh,@gate,@up,@down,@total,@net)
delete counter
where date=@date and hh=@hh and gateno=@gate
insert into counter values(@date,@hh,@gate,@up,@down,@total,@net)