我该怎么做才能加速这个SQL查询?

时间:2022-06-01 20:13:25

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,每次引用一次。