Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
1  
An estimated query plan would be helpful here... It gives extra info about how large SQL Server believes the target tables are. I don't think you can attach files, but if you host it and link to it, that might work. –  Michael J Swart Oct 30 '12 at 15:11
    
@MichaelJSwart Would I need to run the query again to get the query plan? I can run it again if I need to, but at 30 minutes it's using up all of the server's resources so I'd like to avoid it, if at all possible. –  Vijay Oct 30 '12 at 15:13
    
@MichaelJSwart - See update, times have been improved, I've posted the query plan to see if there are other improvements I can make. –  Vijay Oct 30 '12 at 15:23
    
Your edit was pretty much my answer. I would also get rid of the SELECT * and Replace with SELECT [COLUMN NAMES] as it makes it more clear.(Disclaimer could be personal preference) –  Zane Oct 30 '12 at 15:54
    
How many records you have in INTO #partitioned once it is executed ? –  Farfarak Oct 31 '12 at 16:08
show 2 more comments

2 Answers

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.

share|improve this answer
    
I'll look at your suggestion in more detail on Monday, right now I'm up to 90 seconds with another update (required) I'll post that later and see what I can do to speed that up too... thanks –  Vijay Nov 2 '12 at 22:52
add comment

What can improve performance is to create index on #sequenced for with keys PlayerID and MasterSeqID.

Create index on Events with key FiringPlayerID and included columns ([HitPlayerID],[FiringTime])

For following join:

 INNER JOIN Player ON Player.PlayerID = sequenced.PlayerID

Change it to following and lets see what happens:

 INNER LOOP JOIN Player ON Player.PlayerID = sequenced.PlayerID

To log execution time for each statement use following:

DECLARE @StartTime DATETIME. @Message NVARCHAR(MAX)

Before running statement:

SET @StartTime = GETDATE

After statement finished put:

SET @Message = 'Time took to run '+CAST(DATEDIFF(millisecond,@StartTime,GETDATE()))    
RAISERROR(@Message,0,0) WITH NOWAIT

And you will now how long each part takes to run.

share|improve this answer
    
I'll post my results on Monday once I've had chance to go through this with the full record set. –  Vijay Nov 2 '12 at 22:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.