SQL Server Skripte für „Sortieren und Gruppieren“


Diese Seite für

Dieser Abschnitt enthält die SQL Server Skripte und Ausführungspläne für Kapitel 6.

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;

Für die order by-Klausel wird keine explizite Sortierung durchgeführt:

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

Derselbe Ausführungsplan wird benutzt, wenn nur nach PRODUCT_ID gruppiert wird:

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

Die Benutzung des Größer-Gleich Operators erzwingt eine Sortierung:

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

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)

Indexed ASC/DESC und NULLS FIRST/LAST

Der Index kann auch rückwärts benutzt werden:

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)

Gemischtes ASC/DESC erzwingt wieder eine Sortierung:

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)

ASC/DESC kann aber auch indiziert werden, damit die Sortierung wieder entfallen kann:

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 setzt die NULLS Erweiterung nicht um:

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 Ausführung:

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)

SQL Server verwendet ein explizites Sort/Group wenn zwei Tage gelesen werden (Parallelisierung zur besseren Lesbarkeit abgeschalten).

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)

Wenn man einen größeren Zeitraum wählt, wird der Hash-Algorithmus benutzt:

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()))

Über den Autor

Photo of Markus Winand
Markus Winand stimmt Entwickler auf SQL-Performance ein. Er hat das Buch SQL Performance Explained veröffentlicht und bietet inhouse Schulungen sowie Tuning-Leistungen auf http://winand.at/ an.