SQL Server Scripts for “Sorting and Grouping”


This section contains the code and execution plans for Chapter 6 in a SQL Server database.

Indexed Order By

DROP INDEX sales_date ON sales;
GO

CREATE INDEX sales_dt_pr ON sales (sale_date, product_id);
GO

EXEC sp_updatestats;
GO

SET STATISTICS PROFILE ON;

SELECT sale_date, product_id, quantity
  FROM sales
 WHERE sale_date = DATEADD(day, -1, GETDATE())
 ORDER BY  sale_date, product_id;

The execution does not perform a sort operation:

Nested Loops(Inner Join, OUTER REFERENCES:[Bmk1000])
 |--Index Seek(OBJECT:([sales].[sales_dt_pr]),
 |  SEEK:[sales].[sale_date]=dateadd(day,(-1),getdate())
 |  ORDERED FORWARD)
 |--RID Lookup(OBJECT:([sales]),
    SEEK:[Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD

SQL Server uses the same execution plan, when sorting by PRODUCT_ID only.

SELECT sale_date, product_id, quantity
  FROM sales
 WHERE sale_date = DATEADD(day, -1, GETDATE())
 ORDER BY product_id;

Using an greater or equals condition requires an Sort operation:

SELECT sale_date, product_id, quantity
  FROM sales
 WHERE sale_date >= DATEADD(day, -1, GETDATE())
 ORDER BY product_id;

Although the row estimate got lower, causing the cost also to be lower:

Sort(ORDER BY:([test].[dbo].[sales].[product_id] ASC))
 |--Nested Loops(Inner Join, OPTIMIZED WITH UNORDERED PREFETCH)
    |--Compute Scalar(DEFINE:([Expr1009]=BmkToPage([Bmk1000])))
    |  |--Nested Loops(Inner Join)
    |     |--Compute Scalar([...])
    |     |  |--Constant Scan
    |     |--Index Seek(OBJECT:([sales].[sales_dt_pr]),
    |        SEEK:([sales].[sale_date] > [Expr1007]
    |         AND  [sales].[sale_date] < NULL) ORDERED FORWARD)
    |--RID Lookup(OBJECT:([sales]),
       SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

Indexing ASC, DESC and NULLS FIRST/LAST

Scanning an index backwards:

SELECT sale_date, product_id, quantity
  FROM sales
 WHERE sale_date >= DATEADD(day, -1, GETDATE())
 ORDER BY sale_date DESC, product_id DESC;
Nested Loops(Inner Join, OUTER WITH ORDERED PREFETCH)
 |--Nested Loops(Inner Join)
 |  |--Compute Scalar([...])
 |  |  |--Constant Scan
 |  |--Index Seek(OBJECT:([sales].[sales_dt_pr]),
 |     SEEK:([sales].[sale_date] > [Expr1007]
 |      AND  [sales].[sale_date] < NULL) ORDERED BACKWARD)
 |--RID Lookup(OBJECT:([sales]),
     SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

Mixing ASC and DESC causes an explicit sort:

SELECT sale_date, product_id, quantity
  FROM sales
 WHERE sale_date >= DATEADD(day, -1, GETDATE())
 ORDER BY sale_date ASC, product_id DESC;
Sort(ORDER BY:([sale_date] ASC, [product_id] DESC))
 |--Nested Loops(Inner Join, OPTIMIZED WITH UNORDERED PREFETCH)
    |--Compute Scalar(DEFINE:([Expr1009]=BmkToPage([Bmk1000])))
    |  |--Nested Loops(Inner Join)
    |     |--Compute Scalar([...])
    |     |  |--Constant Scan
    |     |--Index Seek(OBJECT:([sales].[sales_dt_pr]),
    |        SEEK:([sales].[sale_date] > [Expr1007] 
    |         AND  [sales].[sale_date] < NULL) ORDERED FORWARD)
    |--RID Lookup(OBJECT:([test].[dbo].[sales]),
       SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

Ordering the index with mixed ASC/DESC modifiers:

DROP INDEX sales_dt_pr ON sales;
GO

CREATE INDEX sales_dt_pr
    ON sales (sale_date ASC, product_id DESC);
GO

SELECT sale_date, product_id, quantity
  FROM sales
 WHERE sale_date >= DATEADD(day, -1, GETDATE())
 ORDER BY sale_date ASC, product_id DESC;
Nested Loops(Inner Join, WITH ORDERED PREFETCH)
 |--Nested Loops(Inner Join)
 |  |--Compute Scalar([...])
 |  |  |--Constant Scan
 |  |--Index Seek(OBJECT:([sales].[sales_dt_pr]),
 |     SEEK:([sales].[sale_date] > [Expr1007]
 |      AND  [sales].[sale_date] < NULL) ORDERED FORWARD)
 |--RID Lookup(OBJECT:([test].[dbo].[sales]),
    SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

SQL Server 2008R2 does not implement the order by NULLS extension.

SELECT sale_date, product_id, quantity
  FROM sales
 WHERE sale_date >= DATEADD(day, -1, GETDATE())
 ORDER BY sale_date ASC, product_id DESC NULLS LAST;
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'NULLS'.

Indexed Group By

Pipelined group by execution:

SELECT product_id, SUM(eur_value)
  FROM sales
 WHERE sale_date = DATEADD(day, -1, GETDATE())
 GROUP BY product_id;
Stream Aggregate(GROUP BY:[sales].[product_id] )
 |--Nested Loops(Inner Join)
    |--Index Seek(OBJECT:([sales].[sales_dt_pr]),
    |  SEEK:([sales].[sale_date]=dateadd(day,(-1),getdate()))
    |  ORDERED BACKWARD)
    |--RID Lookup(OBJECT:([sales]),
       SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

Explicit Sort/Group when retrieving the stats for two days (parallelism disable for plan readability):

SELECT product_id, SUM(eur_value)
  FROM sales
 WHERE sale_date >= DATEADD(day, -1, GETDATE())
 GROUP BY product_id
OPTION (MAXDOP 1);
Stream Aggregate(GROUP BY:[sales].[product_id])
 |--Sort(ORDER BY:([sales].[product_id] ASC))
    |--Nested Loops(Inner Join)
    |--Compute Scalar(DEFINE:[Expr1011]=BmkToPage[Bmk1000])
    |  |--Nested Loops(Inner Join)
    |     |--Compute Scalar([...])
    |     |  |--Constant Scan
    |     |--Index Seek(OBJECT:([sales].[sales_dt_pr]),
    |        SEEK:([sales].[sale_date] > [Expr1009]
    |         AND  [sales].[sale_date] < NULL) ORDERED FORWARD)
    |--RID Lookup(OBJECT:([test].[dbo].[sales]),
       SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

The Hash-Algorithm is used when aggregating a larger set:

SELECT product_id, SUM(eur_value)
  FROM sales
 WHERE sale_date >= DATEADD(day, -100, GETDATE())
 GROUP BY product_id
OPTION (MAXDOP 1);
Hash Match(Aggregate, HASH:([sales].[product_id]),
 |  RESIDUAL:([sales].[product_id] = [sales].[product_id])
 |--Table Scan(OBJECT:([sales]),
    WHERE:[sales].[sale_date]>=dateadd(day,(-100),getdate()))

About the Author

As an author, trainer, and coach Markus Winand specializes in helping developers cope with SQL performance issues. He also published the book SQL Performance Explained and tweets his best performance tips via @SQLPerfTips.http://winand.at/

Recent Questions at Ask.Use-The-Index-Luke.com

2
votes
1
answer
20
views

Table Names in Plural Form

20 hours ago simas 36
table
0
votes
1
answer
164
views

We want to buy the book but I can't

Jul 18 at 21:36 Markus Winand ♦♦ 596
book
0
votes
2
answers
166
views