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

0
votes
1
answer
69
views
0
votes
0
answers
321
views

Fanout in R-Tree

Mar 27 at 08:07 jamie 1
tree indexing
0
votes
1
answer
104
views

Think About It

Mar 26 at 12:54 Markus Winand ♦♦ 511
reflection