Here is some detail, I tried to make a SQLFiddle but I kept getting errors with my variables. This works in Sql Server 2008. My question is, how can I make my query faster? I know I'm doing a number of things wrong here (repeated nester queries), I'm hoping to get someone to take a look and help me get this down from its 30 minute execution time! :-S
这里有一些细节,我试着制作一个SQLFiddle,但是我的变量一直出错。这适用于Sql Server 2008.我的问题是,如何让我的查询更快?我知道我在这里做了很多错事(重复的nester查询),我希望有人看看并帮助我从30分钟的执行时间中解决这个问题! :-S
The basic idea behind the query is that in the game I want to find all players which haven't moved 5 units for a period of time, who have fired whilst stood still and did not fire for 60 minutes before they stopped moving.
查询背后的基本思想是,在游戏中我想找到所有在一段时间内没有移动5个单位的玩家,他们在停止移动之前已经开火并且没有开火60分钟。
The query works, but it's the AND NOT EXISTS clause which is slowing things down to a crawl, before I added that it took 16 seconds to run! 16 seconds is still a long time, so any other improvements would be appreciated, but for now with this being my own POC game (just throwing bits and pieces together), 16 seconds is acceptable...
该查询有效,但它是AND NOT EXISTS子句,它减慢了爬行速度,在我添加它需要16秒运行之前! 16秒仍然是一个很长的时间,所以任何其他的改进将不胜感激,但现在这是我自己的POC游戏(只是把点点滴滴在一起),16秒是可以接受的...
DECLARE @n INT , @DistanceLimit INT
SELECT @n = 2 , @DistanceLimit = 5;
WITH partitioned
AS ( SELECT * ,
CASE WHEN Distance < @DistanceLimit THEN 1
ELSE 0
END AS PartitionID
FROM EntityStateEvent
WHERE ExerciseID = '8B50D860-6C4E-11E1-8E70-0025648E65EC'
),
sequenced
AS ( SELECT ROW_NUMBER() OVER ( PARTITION BY PlayerID ORDER BY EventTime ) AS MasterSeqID ,
ROW_NUMBER() OVER ( PARTITION BY PlayerID, PartitionID ORDER BY EventTime ) AS PartIDSeqID ,
*
FROM partitioned
),
filter
AS ( SELECT MasterSeqID - PartIDSeqID AS GroupID ,
MIN(MasterSeqID) AS GroupFirstMastSeqID ,
MAX(MasterSeqID) AS GroupFinalMastSeqID ,
PlayerID
FROM sequenced
WHERE PartitionID = 1
GROUP BY PlayerID ,
MasterSeqID - PartIDSeqID
HAVING COUNT(*) >= @n
)
SELECT
DISTINCT ( sequenced.PlayerID ) ,
MIN(sequenced.EventTime) AS StartTime ,
MAX(sequenced.EventTime) AS EndTime ,
DATEDIFF(minute, MIN(sequenced.EventTime),
MAX(sequenced.EventTime)) AS StaticTime ,
Player.Designation AS 'Player'
FROM filter
INNER JOIN sequenced ON sequenced.PlayerID = filter.PlayerID
AND sequenced.MasterSeqID >= filter.GroupFirstMastSeqID
AND sequenced.MasterSeqID <= filter.GroupFinalMastSeqID
INNER JOIN Events ON Events.FiringPlayerID = sequenced.PlayerID
INNER JOIN Player ON Player.PlayerID = sequenced.PlayerID
AND Player.Force = 'FR'
AND NOT EXISTS ( SELECT *
FROM Events
WHERE Events.FiringPlayerID = Player.PlayerID
GROUP BY Events.FiringTime
HAVING Events.FiringTime BETWEEN DATEADD(minute,
-60,
( SELECT
MIN(s.EventTime)
FROM
sequenced s
WHERE
s.PlayerID = filter.PlayerID
AND s.MasterSeqID >= filter.GroupFirstMastSeqID
AND s.MasterSeqID <= filter.GroupFinalMastSeqID
))
AND
( SELECT
MIN(s.EventTime)
FROM
sequenced s
WHERE
s.PlayerID = filter.PlayerID
AND s.MasterSeqID >= filter.GroupFirstMastSeqID
AND s.MasterSeqID <= filter.GroupFinalMastSeqID
) )
INNER JOIN Player HitPlayer ON HitPlayer.PlayerID = Events.HitPlayerID
WHERE HitPlayer.[FORCE] = 'HO'
GROUP BY GroupID ,
sequenced.PlayerID ,
Events.FiringPlayerID ,
Events.FiringTime ,
Player.Designation
HAVING DATEDIFF(minute, MIN(sequenced.EventTime),
MAX(sequenced.EventTime)) > 5
AND Events.FiringTime BETWEEN MIN(sequenced.EventTime)
AND MAX(sequenced.EventTime)
ORDER BY StartTime
1 个解决方案
#1
3
The first thing I'd do is materialize the sequenced CTE, since it is used 4 times in the overall schema of things.
我要做的第一件事是实现有序的CTE,因为它在整体模式中使用了4次。
This would mean moving around some code and using #temp tables in place of the sequential CTEs. It would also work out an order of magnitude better since you can cluster #temp tables and create useful indexes for the JOINs.
这意味着移动一些代码并使用#temp表代替顺序CTE。它还可以更好地处理一个数量级,因为您可以聚合#temp表并为JOIN创建有用的索引。
See this SQLFiddle that shows that CTEs can be evaluated many times, once for each reference.
请参阅此SQLFiddle,其中显示可以多次评估CTE,每次引用一次。
#1
3
The first thing I'd do is materialize the sequenced CTE, since it is used 4 times in the overall schema of things.
我要做的第一件事是实现有序的CTE,因为它在整体模式中使用了4次。
This would mean moving around some code and using #temp tables in place of the sequential CTEs. It would also work out an order of magnitude better since you can cluster #temp tables and create useful indexes for the JOINs.
这意味着移动一些代码并使用#temp表代替顺序CTE。它还可以更好地处理一个数量级,因为您可以聚合#temp表并为JOIN创建有用的索引。
See this SQLFiddle that shows that CTEs can be evaluated many times, once for each reference.
请参阅此SQLFiddle,其中显示可以多次评估CTE,每次引用一次。