I've created this SQL query with the help of @Dems 🙂
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 one of you SQL gurus to take a look and help me get this down from it's 30 minute execution time! :-S
I'm not sure exactly how much information you need to be able to help me on this one, please fire away with any questions and I'll reply ASAP. Sorry if the level of detail is lacking a little, I just don't know what you want/need to know!
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. 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 looong 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…
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
EDIT
By changing my query to use temp tables rather than nested CTEs I've improved on the execution time down to 23 seconds – Here is a link to the execution plan: http://www.filedropper.com/executionplan
Here is the query now:
DECLARE @n INT , @DistanceLimit INT
SELECT @n = 2 , @DistanceLimit = 5;
SELECT *, CASE WHEN Distance < @DistanceLimit THEN 1
ELSE 0
END AS PartitionID
INTO #partitioned
FROM EntityStateEvent
WHERE ExerciseID = '8B50D860-6C4E-11E1-8E70-0025648E65EC'
SELECT *, ROW_NUMBER() OVER ( PARTITION BY PlayerID ORDER BY EventTime ) AS MasterSeqID ,
ROW_NUMBER() OVER ( PARTITION BY PlayerID, PartitionID ORDER BY EventTime ) AS PartIDSeqID
INTO #sequenced
FROM #partitioned;
WITH 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 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
Best Answer
So if you've got it down to 23 seconds and are hoping for better than 16 seconds... Consider an index on #sequenced(EventTime) and/or an index on Events(FiringTime)
It might be worth a test.