还是关于乱建ID导致效率低下
今天改的一个存储过程发现改了还是效率低下,仔细看了看,原来那个是一个IP日志表,因为没有适当的索引,导致全表的聚集扫描,速度非常慢。仔细分析了一下:
IPLog:
一个自增ID,一个访问时间,一个访问次数,一个IP,4个字段,80万记录。
索引:一个聚集索引在自增ID上。
执行以下语句:
优化:
将自增ID去掉,将聚集索引建在accesstime上
执行同样语句结果如下:
很鲜明的对比,这就是我一直强调的日志类型表不要建ID的典型范例。
附[IPLogInsert]详细:
IPLog:
一个自增ID,一个访问时间,一个访问次数,一个IP,4个字段,80万记录。
索引:一个聚集索引在自增ID上。
执行以下语句:
set statistics io on
set statistics time on
exec IPLogInsert '255.255.255.255'
结果:set statistics time on
exec IPLogInsert '255.255.255.255'
SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。
表 'IPLog'。扫描计数 1,逻辑读取 3737 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
SQL Server 执行时间:
CPU 时间 = 172 毫秒,占用时间 = 514 毫秒。
SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。
表 'IPLog'。扫描计数 0,逻辑读取 3 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 252 毫秒。
SQL Server 执行时间:
CPU 时间 = 172 毫秒,占用时间 = 771 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。
CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。
表 'IPLog'。扫描计数 1,逻辑读取 3737 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
SQL Server 执行时间:
CPU 时间 = 172 毫秒,占用时间 = 514 毫秒。
SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。
表 'IPLog'。扫描计数 0,逻辑读取 3 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 252 毫秒。
SQL Server 执行时间:
CPU 时间 = 172 毫秒,占用时间 = 771 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。
优化:
将自增ID去掉,将聚集索引建在accesstime上
执行同样语句结果如下:
SQL Server 分析和编译时间:
CPU 时间 = 16 毫秒,占用时间 = 30 毫秒。
SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。
SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。
表 'IPLog'。扫描计数 1,逻辑读取 4 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 24 毫秒。
SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。
表 'IPLog'。扫描计数 0,逻辑读取 3 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 6 毫秒。
SQL Server 执行时间:
CPU 时间 = 16 毫秒,占用时间 = 61 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。
CPU 时间 = 16 毫秒,占用时间 = 30 毫秒。
SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。
SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。
表 'IPLog'。扫描计数 1,逻辑读取 4 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 24 毫秒。
SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。
表 'IPLog'。扫描计数 0,逻辑读取 3 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 6 毫秒。
SQL Server 执行时间:
CPU 时间 = 16 毫秒,占用时间 = 61 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。
很鲜明的对比,这就是我一直强调的日志类型表不要建ID的典型范例。
附[IPLogInsert]详细:
-- =============================================
-- Author: Xiangxiang
-- Create date:2008-5-29
-- Description: Insert a IP log
-- =============================================
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
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)
end
end
-- Author: Xiangxiang
-- Create date:2008-5-29
-- Description: Insert a IP log
-- =============================================
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
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)
end
end