还是关于乱建ID导致效率低下

今天改的一个存储过程发现改了还是效率低下,仔细看了看,原来那个是一个IP日志表,因为没有适当的索引,导致全表的聚集扫描,速度非常慢。仔细分析了一下:
IPLog:
一个自增ID,一个访问时间,一个访问次数,一个IP,4个字段,80万记录。
索引:一个聚集索引在自增ID上。
执行以下语句:
set statistics io on
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 毫秒。


优化:
将自增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 毫秒。


很鲜明的对比,这就是我一直强调的日志类型表不要建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
posted on 2008-05-28 21:58  Keep Walking  阅读(869)  评论(5编辑  收藏  举报