今天改的一个存储过程发现改了还是效率低下,仔细看了看,原来那个是一个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
次。
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
SQL Server 执行时间:
CPU 时间
=
172
毫秒,占用时间
=
514
毫秒。
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
SQL Server 执行时间:
CPU 时间
=
0
毫秒,占用时间
=
1
毫秒。
表
'
IPLog
'
。扫描计数
0
,逻辑读取
3
次,物理读取
0
次,预读
0
次,lob 逻辑读取
0
次,lob 物理读取
0
次,lob 预读
0
次。
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
SQL Server 执行时间:
CPU 时间
=
0
毫秒,占用时间
=
252
毫秒。
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
SQL Server 执行时间:
CPU 时间
=
172
毫秒,占用时间
=
771
毫秒。
SQL Server 分析和编译时间:
CPU 时间
=
0
毫秒,占用时间
=
1
毫秒。
优化:
将自增ID去掉,将聚集索引建在accesstime上
执行同样语句结果如下:
SQL Server 分析和编译时间:
CPU 时间
=
16
毫秒,占用时间
=
30
毫秒。
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
SQL Server 执行时间:
CPU 时间
=
0
毫秒,占用时间
=
1
毫秒。
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
SQL Server 执行时间:
CPU 时间
=
0
毫秒,占用时间
=
1
毫秒。
表
'
IPLog
'
。扫描计数
1
,逻辑读取
4
次,物理读取
0
次,预读
0
次,lob 逻辑读取
0
次,lob 物理读取
0
次,lob 预读
0
次。
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
SQL Server 执行时间:
CPU 时间
=
0
毫秒,占用时间
=
24
毫秒。
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
SQL Server 执行时间:
CPU 时间
=
0
毫秒,占用时间
=
1
毫秒。
表
'
IPLog
'
。扫描计数
0
,逻辑读取
3
次,物理读取
0
次,预读
0
次,lob 逻辑读取
0
次,lob 物理读取
0
次,lob 预读
0
次。
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
SQL Server 执行时间:
CPU 时间
=
0
毫秒,占用时间
=
6
毫秒。
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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
))
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
if
(
@@rowcount
=
0
)
begin
insert
into
IPLog (AccessTime,IP,
[
Count
]
)
values
(
@today
,
@IP
,
1
)
end
end