优化实战:不要随便将字段折腾来折腾去的
到新公司先看了看数据库的性能,查看一个存储占用的CPU巨多,而且执行次数也特别多,打开一看:
很是汗颜啊!一个简单的需求被写成了这样。
最后我改成的是:
前一个是将字段转成了char然后再比较,很无赖啊。
alter PROCEDURE [dbo].[IPLogInsert]
@IP char(15) = '255.255.255.255'
AS
BEGIN
SET NOCOUNT ON;
declare @currId int
declare @today datetime
--SET @currID = 0
SET @today = getdate()
SELECT @currId = id from IPLog
where convert(char(10), @today,101) = convert(char(10),AccessTime, 101) and @IP = IP if( @currID = 0)
insert into IPLog (AccessTime,IP,[Count])
values (@today, @IP, 1)
else
update IPLog
SET [count]=[count]+1
WHERE convert(char(10), @today,101) = convert(char(10),AccessTime, 101) and @IP = IP
END
@IP char(15) = '255.255.255.255'
AS
BEGIN
SET NOCOUNT ON;
declare @currId int
declare @today datetime
--SET @currID = 0
SET @today = getdate()
SELECT @currId = id from IPLog
where convert(char(10), @today,101) = convert(char(10),AccessTime, 101) and @IP = IP if( @currID = 0)
insert into IPLog (AccessTime,IP,[Count])
values (@today, @IP, 1)
else
update IPLog
SET [count]=[count]+1
WHERE convert(char(10), @today,101) = convert(char(10),AccessTime, 101) and @IP = IP
END
很是汗颜啊!一个简单的需求被写成了这样。
最后我改成的是:
update IPlog set Count = Count+1 where Ip = @IP and AccessTime between dateadd(day,1,datediff(day,0,@today-1)) and dateadd(day,1,datediff(day,0,@today))
if(@@rowcount =0)
begin
insert into IPLog (AccessTime,IP,[Count])
values (@today,@IP,1)
if(@@rowcount =0)
begin
insert into IPLog (AccessTime,IP,[Count])
values (@today,@IP,1)
前一个是将字段转成了char然后再比较,很无赖啊。