到新公司先看了看数据库的性能，查看一个存储占用的CPU巨多，而且执行次数也特别多，打开一看：
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
)
前一个是将字段转成了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
很是汗颜啊！一个简单的需求被写成了这样。
最后我改成的是：
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
)
前一个是将字段转成了char然后再比较，很无赖啊。
