Page Comparison

...

Visual

SQL

Plan

Fixed LOD Expression:

Expand

title	Can you guess the code? <click to expand>

Code Block

language	sql

SELECT 
[t1].[Region] AS [Region]
,[t1].[Segment] AS [Segment]
,SUM([t2].[__measure__1]) AS [__measure__2]
		
FROM (
	SELECT 
	[Orders].[Category] AS [Category]
	,[Orders].[Region] AS [Region]
	,[Orders].[Segment] AS [Segment]
			
	FROM [dbo].[Orders] [Orders]
	WHERE ([Orders].[Category] NOT IN ('Furniture'))
	GROUP BY [Orders].[Category], [Orders].[Region],[Orders].[Segment]
		
) [t1]
INNER JOIN (

	SELECT 
	[Orders].[Category] AS [Category]
	,[Orders].[Segment] AS [Segment]
	,SUM([Orders].[Sales]) AS [__measure__1]
			
	FROM [dbo].[Orders] [Orders]
	GROUP BY [Orders].[Category],[Orders].[Segment]
	
) [t2] ON ((([t1].[Category] = [t2].[Category]) OR (([t1].[Category] IS NULL) AND ([t2].[Category] IS NULL))) 
			AND (([t1].[Segment] = [t2].[Segment]) OR (([t1].[Segment] IS NULL) AND ([t2].[Segment] IS NULL))))
		
GROUP BY [t1].[Region], [t1].[Segment]

Fixed LOD Expression:

Expand

title	And now the plan <click to expand>

Notice that double table scan - yup, regardless as to whether the table is optimised or not, this query generates two scans

Window_Sum Calculation:

Image Added

Window_Sum Calculation:

Expand

Code Block

language	sql

SELECT 
[Orders].[Region] AS [Region]
,[Orders].[Segment] AS [Segment]
,SUM([Orders].[Sales]) AS [sum:Sales:ok]
	
FROM [dbo].[Orders] [Orders]
WHERE ([Orders].[Category] NOT IN ('Furniture'))
GROUP BY [Orders].[Region], [Orders].[Segment]

Window_Sum Calculation:

Expand

title	The Window_Sum Plan <expand>

This plan is a lot smaller, almost 60% smaller than the LOD plan, and with only a single table scan (or index scan / seek if indexed) and crucially, no joins

...

Versions Compared

Old Version 20

New Version 21

Key