create proc Depth
as
declare @Count int ,@Date datetime,@CountIp varchar(200),@Depth int
DECLARE Flow_CUR CURSOR FOR
select subString(CONVERT(varchar, CountTime, 120 ),0,CHARINDEX(':',CountTime))+':00:00' as Date,
CountIp,count(CountUrl) as Depth
from Count_ReturnAndDepth where CustomerID='1'and StatisticsDepthOrFalse=2 group by CountIp,
subString(CONVERT(varchar, CountTime, 120 ),0,CHARINDEX(':',CountTime))+':00:00',HostName
OPEN Flow_CUR
fetch next from Flow_CUR into @Date,@CountIp,@Depth WHILE (@@FETCH_STATUS) = 0 BEGIN
select @Count=DepthCount from Count_DepthInformation where CustomerID='1'and CountIP=@CountIp and PostTime=@Date
if exists(select 1 from Count_DepthInformation where CustomerID='1'and CountIP=@CountIp and
PostTime=@Date)update Count_DepthInformation set DepthCount=1 +@Count where CustomerID='1'
and CountIP=@CountIp and PostTime=@Date
else insert into Count_DepthInformation (CustomerID,CountIp,DepthCount,PostTime)
values ('1',@CountIp,'1',@Date) FETCH NEXT FROM Flow_CUR into @Date,@CountIp,@Depth
END
CLOSE Flow_CUR
DEALLOCATE Flow_CUR
Update Count_ReturnAndDepth set StatisticsDepthOrFalse=1 where StatisticsDepthOrFalse=0
----------------------------------------------------
在做一个流量的统计,关系到两个表,我使用游标测试时,速度超慢,4000条要运行2分多钟,不知道能不能使用其他办法改进,请大家帮忙看看,谢谢!!
as
declare @Count int ,@Date datetime,@CountIp varchar(200),@Depth int
DECLARE Flow_CUR CURSOR FOR
select subString(CONVERT(varchar, CountTime, 120 ),0,CHARINDEX(':',CountTime))+':00:00' as Date,
CountIp,count(CountUrl) as Depth
from Count_ReturnAndDepth where CustomerID='1'and StatisticsDepthOrFalse=2 group by CountIp,
subString(CONVERT(varchar, CountTime, 120 ),0,CHARINDEX(':',CountTime))+':00:00',HostName
OPEN Flow_CUR
fetch next from Flow_CUR into @Date,@CountIp,@Depth WHILE (@@FETCH_STATUS) = 0 BEGIN
select @Count=DepthCount from Count_DepthInformation where CustomerID='1'and CountIP=@CountIp and PostTime=@Date
if exists(select 1 from Count_DepthInformation where CustomerID='1'and CountIP=@CountIp and
PostTime=@Date)update Count_DepthInformation set DepthCount=1 +@Count where CustomerID='1'
and CountIP=@CountIp and PostTime=@Date
else insert into Count_DepthInformation (CustomerID,CountIp,DepthCount,PostTime)
values ('1',@CountIp,'1',@Date) FETCH NEXT FROM Flow_CUR into @Date,@CountIp,@Depth
END
CLOSE Flow_CUR
DEALLOCATE Flow_CUR
Update Count_ReturnAndDepth set StatisticsDepthOrFalse=1 where StatisticsDepthOrFalse=0
----------------------------------------------------
在做一个流量的统计,关系到两个表,我使用游标测试时,速度超慢,4000条要运行2分多钟,不知道能不能使用其他办法改进,请大家帮忙看看,谢谢!!
as
--declare @Count int ,@Date datetime,@CountIp varchar(200),@Depth int
--建临时表
SELECT CONVERT(CHAR(13), CountTime, 120 )+':00:00' AS Date,CountIp,COUNT(1) AS Depth
INTO #T
FROM Count_ReturnAndDepth
WHERE CustomerID='1'AND StatisticsDepthOrFalse=2
GROUP BY CountIp,Date,HostName--更新
UPDATE Count_DepthInformation,#T SET Count_DepthInformation.DepthCount=1 +Count_DepthInformation.DepthCount
WHERE
Count_DepthInformation.CustomerID='1'
AND
#T.CountIP=Count_DepthInformation.CountIP
AND #T.Date=Count_DepthInformation.PostTime
--插入
INSERT INTO Count_DepthInformation (CustomerID,CountIp,DepthCount,PostTime)
SELECT '1',A.CountIp,'1',A.Date
FROM
#T A INNER JOIN Count_DepthInformation B
ON (A.CountIP<>B.CountIP OR A.Date <> B.PostTime) AND B.CustomerID='1'--最后的那条语句
Update Count_ReturnAndDepth set StatisticsDepthOrFalse=1 where StatisticsDepthOrFalse=0
DROP TABLE #T
--用临时表改的,可试一下。
比如先测一下
SELECT CONVERT(CHAR(13), CountTime, 120 )+':00:00' AS Date,CountIp,COUNT(1) AS Depth
FROM Count_ReturnAndDepth
WHERE CustomerID='1'AND StatisticsDepthOrFalse=2
GROUP BY CountIp,Date,HostName
--看看耗时多久。
如果耗时长的话想办法优化此句。