=====================车速异常情况之大于上限限==============================================
我做的是智能公交车这块,需要对公交车的信息进行实时监控,要计算公交车30个参数的超过限定值的时间差。下面是我的代码,当数据量为几百万的时候还好,但是当数据量达到几千万的时候花费的时间很久至少要一两个小时,因为有30个参数都要计算时间差,所以合计花费的时间更加久。请问各位大神有没有很好的方法帮帮我!!我在线等WITH tab_bus AS
(
SELECT a.belongTo, a.compName, a.SubCode, a.SubName, a.teamCode, a.teamName , a.RoadCode, a.RoadName,
a.busid , a.BusTID , a.BusCode, a.BusPlate , a.billdate AS StartTime, a.CanValue AS StartValue,
b.billdate AS EndTime, b.CanValue AS EndValue, DATEDIFF(ss,a.billdate,b.billdate)AS Time_s,
a.CanUpLimt, a.CanDownLimt, a.CanUnit, a.ParaCode, a.ParaName, a.billname , a.billcode , a.CanCode ,
a.CanName , a.CanTime , a.CanState, a.empCode , a.compCode , a.dirverCode, a.CanConf , a.RuleConf ,
a.dirverConf, a.CrawlConf, a.faultConf, a.GpsCode, a.usercode, a.piaocode , a.othercode, a.billType ,
a.billState , a.memo , a.newdate , a.reserve1, a.reserve2, a.reserve3
FROM dbo.Y_BusSpeed a
CROSS APPLY
(
SELECT TOP 1 billdate, CanValue
FROM dbo.Y_BusSpeed
WHERE
billdate =(
SELECT MIN(billdate)
FROM dbo.Y_BusSpeed
WHERE
BusPlate = a.BusPlate
AND CanValue<CanUpLimt AND billdate > a.billdate
)
ORDER BY billdate DESC
) AS b
WHERE a.CanValue>=a.CanUpLimt
)
INSERT INTO dbo.H_SpeedAbnormal( belongTo, compName, SubCode, SubName, teamCode , teamName, RoadCode, RoadName,
BusID , BusTID, BusCode , BusPlate, BeginValue, BeginDate, MaxVaLue , AvgValue ,
EndValue, EndDate , Time_s , CanUpLimt, CanDownLimt, CanUnit , ParaCode, ParaName ,
billname , billcode , CanCode , CanName , CanTime , CanState , empCode , compCode ,
dirverCode, CanConf, RuleConf , dirverConf, CrawlConf, faultConf, GpsCode, usercode,
piaocode , othercode, billType , billState, memo , newdate , reserve1 , reserve2,
reserve3
)SELECT c.belongTo, c.compName, c.SubCode, c.SubName, c.teamCode, c.teamName , c.RoadCode, c.RoadName,
c.busid , c.BusTID, c.BusCode, c.BusPlate , StartValue, StartTime, d.Maxvalue, d.AvgValue,
EndValue, EndTime, Time_s, c.CanUpLimt, c.CanDownLimt,c.CanUnit, c.ParaCode, c.ParaName ,
c.billname , c.billcode , c.CanCode , c.CanName , c.CanTime , c.CanState, c.empCode , c.compCode ,
c.dirverCode,c.CanConf , c.RuleConf , c.dirverConf, c.CrawlConf, c.faultConf, c.GpsCode, c.usercode,
c.piaocode , c.othercode, c.billType , c.billState , c.memo , c.newdate , c.reserve1, c.reserve2,
c.reserve3 FROM tab_bus c cross apply (SELECT TOP 1 Max(CanValue) as MaxValue,Avg(CanValue)as AvgValue
FROM dbo.Y_BusSpeed
WHERE
BusPlate = c.BusPlate
AND billdate BETWEEN StartTime AND EndTime
)as dwhere StartTime in
(
SELECT MIN(StartTime)
FROM tab_bus
GROUP BY EndTime,BusPlate
)
ORDER BY belongTo, SubCode, teamCode, RoadCode,BusPlate ,StartTime
我做的是智能公交车这块,需要对公交车的信息进行实时监控,要计算公交车30个参数的超过限定值的时间差。下面是我的代码,当数据量为几百万的时候还好,但是当数据量达到几千万的时候花费的时间很久至少要一两个小时,因为有30个参数都要计算时间差,所以合计花费的时间更加久。请问各位大神有没有很好的方法帮帮我!!我在线等WITH tab_bus AS
(
SELECT a.belongTo, a.compName, a.SubCode, a.SubName, a.teamCode, a.teamName , a.RoadCode, a.RoadName,
a.busid , a.BusTID , a.BusCode, a.BusPlate , a.billdate AS StartTime, a.CanValue AS StartValue,
b.billdate AS EndTime, b.CanValue AS EndValue, DATEDIFF(ss,a.billdate,b.billdate)AS Time_s,
a.CanUpLimt, a.CanDownLimt, a.CanUnit, a.ParaCode, a.ParaName, a.billname , a.billcode , a.CanCode ,
a.CanName , a.CanTime , a.CanState, a.empCode , a.compCode , a.dirverCode, a.CanConf , a.RuleConf ,
a.dirverConf, a.CrawlConf, a.faultConf, a.GpsCode, a.usercode, a.piaocode , a.othercode, a.billType ,
a.billState , a.memo , a.newdate , a.reserve1, a.reserve2, a.reserve3
FROM dbo.Y_BusSpeed a
CROSS APPLY
(
SELECT TOP 1 billdate, CanValue
FROM dbo.Y_BusSpeed
WHERE
billdate =(
SELECT MIN(billdate)
FROM dbo.Y_BusSpeed
WHERE
BusPlate = a.BusPlate
AND CanValue<CanUpLimt AND billdate > a.billdate
)
ORDER BY billdate DESC
) AS b
WHERE a.CanValue>=a.CanUpLimt
)
INSERT INTO dbo.H_SpeedAbnormal( belongTo, compName, SubCode, SubName, teamCode , teamName, RoadCode, RoadName,
BusID , BusTID, BusCode , BusPlate, BeginValue, BeginDate, MaxVaLue , AvgValue ,
EndValue, EndDate , Time_s , CanUpLimt, CanDownLimt, CanUnit , ParaCode, ParaName ,
billname , billcode , CanCode , CanName , CanTime , CanState , empCode , compCode ,
dirverCode, CanConf, RuleConf , dirverConf, CrawlConf, faultConf, GpsCode, usercode,
piaocode , othercode, billType , billState, memo , newdate , reserve1 , reserve2,
reserve3
)SELECT c.belongTo, c.compName, c.SubCode, c.SubName, c.teamCode, c.teamName , c.RoadCode, c.RoadName,
c.busid , c.BusTID, c.BusCode, c.BusPlate , StartValue, StartTime, d.Maxvalue, d.AvgValue,
EndValue, EndTime, Time_s, c.CanUpLimt, c.CanDownLimt,c.CanUnit, c.ParaCode, c.ParaName ,
c.billname , c.billcode , c.CanCode , c.CanName , c.CanTime , c.CanState, c.empCode , c.compCode ,
c.dirverCode,c.CanConf , c.RuleConf , c.dirverConf, c.CrawlConf, c.faultConf, c.GpsCode, c.usercode,
c.piaocode , c.othercode, c.billType , c.billState , c.memo , c.newdate , c.reserve1, c.reserve2,
c.reserve3 FROM tab_bus c cross apply (SELECT TOP 1 Max(CanValue) as MaxValue,Avg(CanValue)as AvgValue
FROM dbo.Y_BusSpeed
WHERE
BusPlate = c.BusPlate
AND billdate BETWEEN StartTime AND EndTime
)as dwhere StartTime in
(
SELECT MIN(StartTime)
FROM tab_bus
GROUP BY EndTime,BusPlate
)
ORDER BY belongTo, SubCode, teamCode, RoadCode,BusPlate ,StartTime
查看 CPU , IO 耗时 进行优化