Optimize Subquery with Windowing Function

Question

As my performance tuning skills never seem to feel sufficient, I always wonder if there is more optimization I can perform against some queries. The situation that this question pertains to is a Windowed MAX function nested within a subquery.

The data that I'm digging through is a series of transactions on various groups of larger sets. I've got 4 fields of importance, the unique ID of a transaction, the Group ID of a batch of transactions, and dates associated with the respective unique transaction or group of transactions. Most times the Group Date matches the Maximum Unique Transaction Date for a Batch, but there are times where manual adjustments come through our system and a unique date operation occurs after the group transaction date is captured. This manual edit doesn't adjust the group date by design.

What I identify in this query are those records where the Unique Date falls after the Group Date. The following sample query builds out a rough equivalent of the my scenario and the SELECT statement returns the records I'm looking for, however, am I approaching this solution in the most efficient manner? This takes a while to run during my fact table loads as my record counts number in the upper 9 digits, but mostly my disdain for subqueries makes me wonder if there's a better approach here. I'm not as concerned about any indexes as I'm confident those are already in place; what I'm looking for is an alternative query approach that will achieve the same thing, but even more efficiently. Any feedback is welcome.

CREATE TABLE #Example
(
    UniqueID INT IDENTITY(1,1)
  , GroupID INT
  , GroupDate DATETIME
  , UniqueDate DATETIME
)

CREATE CLUSTERED INDEX [CX_1] ON [#Example]
(
    [UniqueID] ASC
)


SET NOCOUNT ON

--Populate some test data
DECLARE @i INT = 0, @j INT = 5, @UniqueDate DATETIME, @GroupDate DATETIME

WHILE @i < 10000
BEGIN

    IF((@i + @j)%173 = 0)
    BEGIN
        SET @UniqueDate = GETDATE()+@i+5
    END
    ELSE
    BEGIN
        SET @UniqueDate = GETDATE()+@i
    END

    SET @GroupDate = GETDATE()+(@j-1)

    INSERT INTO #Example (GroupID, GroupDate, UniqueDate)
    VALUES (@j, @GroupDate, @UniqueDate)

    SET @i = @i + 1

    IF (@i % 5 = 0)
    BEGIN
        SET @j = @j+5
    END
END
SET NOCOUNT OFF

CREATE NONCLUSTERED INDEX [IX_2_4_3] ON [#Example]
(
    [GroupID] ASC,
    [UniqueDate] ASC,
    [GroupDate] ASC
)
INCLUDE ([UniqueID])

-- Identify any UniqueDates that are greater than the GroupDate within their GroupID
SELECT UniqueID
     , GroupID
     , GroupDate
     , UniqueDate
FROM (
    SELECT UniqueID
         , GroupID
         , GroupDate
         , UniqueDate
         , MAX(UniqueDate) OVER (PARTITION BY GroupID) AS maxUniqueDate
    FROM #Example
    ) calc_maxUD
WHERE maxUniqueDate > GroupDate
    AND maxUniqueDate = UniqueDate

DROP TABLE #Example

dbfiddle here

If you want to performance tune a query, the indexes on your table are very much an important part of the question. — Daniel Hutmacher, Apr 14, 2017 at 18:33
@DanielHutmacher I completely agree, though I'm not going to dump a schema for my DWH and Staging area, so this is the best I can do within reason. — John Eisbrener, Apr 14, 2017 at 18:49

Paul White · Accepted Answer · 2017-04-19 07:26:58Z

I'm assuming there's no index, as you haven't provided any.

Right off the bat, the following index will eliminate a Sort operator in your plan, which would otherwise potentially consume a lot of memory:

CREATE INDEX IX ON #Example (GroupID, UniqueDate) INCLUDE (UniqueID, GroupDate);

The subquery isn't a performance problem in this case. If anything, I would look at ways to eliminate the window function (MAX... OVER) to avoid the Nested Loop and Table Spool construct.

With the same index, the following query may at first glance look less efficient, and it does go from two to three scans on the base table, but it eliminates a huge number of reads internally because it lacks Spool operators. I'm guessing that it'll still perform better, particularly if you have enough CPU cores and IO performance on your server:

SELECT e.UniqueID
     , e.GroupID
     , e.GroupDate
     , e.UniqueDate
FROM (
    SELECT GroupID, MAX(UniqueDate) AS maxUniqueDate
    FROM #Example
    GROUP BY GroupID) AS agg
INNER JOIN #Example AS e ON agg.GroupID=e.GroupID
WHERE agg.maxUniqueDate > e.GroupDate
    AND agg.maxUniqueDate = e.UniqueDate
OPTION (MERGE JOIN);

(Note: I added a MERGE JOIN query hint, but this should probably happen automatically if your statistics are in order. Best practice is to leave hints like these out if you can.)

It is uglier, but the execution plan is prettier. That's the magic of declarative languages like T-SQL. — Daniel Hutmacher, Apr 14, 2017 at 18:58

Paul White · Accepted Answer · 2017-04-15 05:51:46Z

When and if you are able to upgrade from SQL Server 2012 to SQL Server 2016, you may be able to take advantage of the much improved performance (especially for frameless window aggregates) provided by the new batch mode Window Aggregate operator.

Almost all large data processing scenarios work better with columnstore storage than rowstore. Even without changing to columnstore for your base tables, you can still gain the benefits of the new 2016 operator and batch mode execution by creating an empty nonclustered columnstore filtered index on one of the base tables, or by redundantly outer joining to a columnstore-organized table.

Using the second option, the query becomes:

-- Just to get batch mode processing and the window aggregate operator
CREATE TABLE #Dummy (a integer NOT NULL, INDEX DummyCC CLUSTERED COLUMNSTORE);

-- Identify any UniqueDates that are greater than the GroupDate within their GroupID
SELECT
    calc_maxUD.UniqueID,
    calc_maxUD.GroupID,
    calc_maxUD.GroupDate,
    calc_maxUD.UniqueDate
FROM 
(
    SELECT
        E.UniqueID,
        E.GroupID,
        E.GroupDate,
        E.UniqueDate,
        maxUniqueDate = MAX(UniqueDate) OVER (
            PARTITION BY GroupID)
    FROM #Example AS E
    LEFT JOIN #Dummy AS D -- The only change to the original query
        ON 1 = 0
) AS calc_maxUD
WHERE 
    calc_maxUD.maxUniqueDate > calc_maxUD.GroupDate
    AND calc_maxUD.maxUniqueDate = calc_maxUD.UniqueDate;

db<>fiddle

Note the only change to the original query is creating an empty temporary table and adding the left join. The execution plan is:

(58 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0
Table '#Example'. Scan count 1, logical reads 40, physical reads 0, read-ahead reads 0

For more information and options, see Itzik Ben-Gan's excellent series, What You Need to Know about the Batch Mode Window Aggregate Operator in SQL Server 2016 (in three parts).

Erik Darling · Accepted Answer · 2017-04-14 19:22:36Z

I'm just gonna throw the ol' Cross Apply out there:

SELECT e.*
    FROM #Example AS e
    CROSS APPLY ( SELECT TOP 1 e2.UniqueDate AS maxUniqueDate
                    FROM #Example AS e2
                    WHERE e2.GroupID = e.GroupID 
                    ORDER BY e2.UniqueDate DESC
                    ) AS ca
    WHERE ca.maxUniqueDate > e.GroupDate
        AND ca.maxUniqueDate = e.UniqueDate;

With some kinda whatever indexes, it does pretty well.

CREATE CLUSTERED INDEX cx_whatever ON #Example (GroupID)

CREATE UNIQUE NONCLUSTERED INDEX ix_whatever ON #Example (GroupID, UniqueDate DESC, GroupDate)

The stats time and io look like this (your query is the first result)

Table 'Worktable'. Scan count 3, logical reads 28004, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Example'. Scan count 1, logical reads 51, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 20 ms.

Table '#Example'. Scan count 10001, logical reads 21336, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 11 ms.

Query plans are here (again, yours is first):

https://www.brentozar.com/pastetheplan/?id=BJYJvqAal

Why I prefer this version? I avoid the spools. If those start spilling to disk, it's gonna get ugly.

But you might wanna try this out, too.

SELECT e.*
    FROM #Example AS e
    CROSS APPLY ( SELECT e2.UniqueDate AS maxUniqueDate
                    FROM #Example AS e2
                    WHERE e2.GroupID = e.GroupID 
                    ) AS ca
    WHERE ca.maxUniqueDate > e.GroupDate
        AND ca.maxUniqueDate = e.UniqueDate;

If this is a large DW, you might prefer the Hash Join, and the row filtering in the join, rather than at the end in the TOP 1 query as a Filter operator.

Plan is here: https://www.brentozar.com/pastetheplan/?id=BkUF55ATx

Stats time and io here:

Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Example'. Scan count 2, logical reads 84, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 5 ms.

Hope this helps!

One edit, based on @ypercube's idea, and a new index.

CREATE NONCLUSTERED INDEX ix_meh ON #Example (UniqueDate,GroupDate) INCLUDE (UniqueID,GroupID);

WITH t1 AS 
(
    SELECT DISTINCT
    e.GroupID ,
    MAX(UniqueDate) AS MaxUniqueDate
    FROM #Example AS e
    GROUP BY e.GroupID
)
SELECT *
FROM #Example AS e
CROSS APPLY (
SELECT *
FROM t1
    WHERE t1.MaxUniqueDate > e.GroupDate
        AND t1.MaxUniqueDate = e.UniqueDate
        AND t1.GroupID = e.GroupID
) ca

Here's the stats time and io:

Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Example'. Scan count 2, logical reads 91, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 4 ms.

Here's the plan:

https://www.brentozar.com/pastetheplan/?id=SJv8foR6g

Seems my example was a little too clean, as there are scenarios where I can have multiple Unique Dates greater than the Group Date in my actual environment. This condition will invalidate your 2nd Cross Apply query, but the other approaches both work without issue. Thanks for some more options! — John Eisbrener, Apr 14, 2017 at 20:00

SqlZim · Accepted Answer · 2017-04-14 19:48:05Z

I would take a look at top with ties

If GroupDate is the same per GroupId then:

select top 1 with ties 
   UniqueID
 , GroupID
 , GroupDate
 , UniqueDate
from #Example
where UniqueDate > GroupDate
order by row_number() over (partition by GroupId order by UniqueDate desc)

Else: using top with ties in a common table expression

with cte as (
  select top 1 with ties 
      UniqueID
    , GroupID
    , GroupDate
    , UniqueDate
  from #Example
  order by row_number() over (partition by GroupId order by UniqueDate desc)
)
select *
from cte
where UniqueDate > GroupDate

dbfiddle: http://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=c058994c2f5f3d99b212f06e1dae9fd3

Original Query

Table 'Worktable'. Scan count 3, logical reads 28001, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Example____________________________________________________________________________________________________________0000000000CB'. Scan count 1, logical reads 43, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 31 ms,  elapsed time = 31 ms.

vs top with ties in a common table expression

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Example____________________________________________________________________________________________________________0000000000CB'. Scan count 1, logical reads 43, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 15 ms.

John Eisbrener · Accepted Answer · 2017-04-14 20:19:39Z

So I did some analysis on the various approaches posted so far, and in my environment, it looks like Daniel's approach wins out consistently on the execution times. Surprisingly (to me) sp_BlitzErik's third CROSS APPLY approach wasn't that far behind. Here're the outputs if anyone's interested, but thanks a TON for all the alternative approaches. I learned more from digging into the answers on this question than I have in quite a while!

Windowed Function - baseline metric

(10406 row(s) affected)
Table 'DateDim'. Scan count 9, logical reads 791, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableFact'. Scan count 9, logical reads 140181, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 89815, logical reads 42553550, physical reads 0, read-ahead reads 84586, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table01Dim'. Scan count 9, logical reads 7688, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table02Dim'. Scan count 9, logical reads 7819, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 87753 ms,  elapsed time = 13031 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.


Basic Aggregated Subquery - Daniel Hutmacher

(10406 row(s) affected)
Table 'DateDim'. Scan count 18, logical reads 1194, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableFact'. Scan count 18, logical reads 280362, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 48, logical reads 82408, physical reads 9629, read-ahead reads 72779, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 89791, logical reads 6861425, physical reads 0, read-ahead reads 14565, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table01Dim'. Scan count 9, logical reads 7688, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table02Dim'. Scan count 18, logical reads 15726, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 40527 ms,  elapsed time = 6182 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.


CROSS APPLY Operation A - sp_BlitzErik

(10406 row(s) affected)
Table 'DateDim'. Scan count 9, logical reads 6199331, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableFact'. Scan count 3099273, logical reads 12844012, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table01Dim'. Scan count 3109676, logical reads 9350502, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table02Dim'. Scan count 3109676, logical reads 9482456, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 132632 ms,  elapsed time = 20955 ms.


CROSS APPLY Operation C - sp_BlitzErik

(10406 row(s) affected)
Table 'DateDim'. Scan count 18, logical reads 1194, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableFact'. Scan count 18, logical reads 280362, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 56, logical reads 92800, physical reads 10872, read-ahead reads 81928, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 89791, logical reads 6861425, physical reads 0, read-ahead reads 14563, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table01Dim'. Scan count 18, logical reads 15376, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table02Dim'. Scan count 18, logical reads 15726, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 46082 ms,  elapsed time = 6804 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.


TOP 1 WITH TIES - B - SqlZim

(10406 row(s) affected)
Table 'DateDim'. Scan count 9, logical reads 791, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableFact'. Scan count 9, logical reads 140181, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 89791, logical reads 6866304, physical reads 0, read-ahead reads 93468, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table01Dim'. Scan count 9, logical reads 7688, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table02Dim'. Scan count 9, logical reads 7835, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 79406 ms,  elapsed time = 15852 ms.

I was just looking at how the posted options would stack up if I bumped your example to 100k rows and added everyone's index suggestions. Seems pretty representative of your actual results as well. It seems my version of top with ties buckles with that many rows. dbfiddle.uk/… — SqlZim, Apr 14, 2017 at 20:27

Stack Exchange Network

Optimize Subquery with Windowing Function

5 Answers 5

Your Answer

Not the answer you're looking for? Browse other questions tagged
sql-server
sql-server-2012
performance
window-functions
query-performance
or ask your own question.

Hot Network Questions

Optimize Subquery with Windowing Function

5 Answers 5

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql-serversql-server-2012performancewindow-functionsquery-performance or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
sql-server
sql-server-2012
performance
window-functions
query-performance
or ask your own question.