web-development-kb-eu.site

Effizientes INSERT IN eine Tabelle mit Clustered Index

Ich habe eine SQL-Anweisung, die Zeilen in eine Tabelle mit einem Clustered-Index für die Spalte TRACKING_NUMBER einfügt.

Z.B.:

INSERT INTO TABL_NAME (TRACKING_NUMBER, COLB, COLC) 
SELECT TRACKING_NUMBER, COL_B, COL_C 
FROM STAGING_TABLE

Meine Frage ist: Hilft es, eine ORDER BY-Klausel in der SELECT-Anweisung für die Clustered-Index-Spalte zu verwenden, oder würde ein erzielter Gewinn durch die für die ORDER BY-Klausel erforderliche zusätzliche Sortierung negiert?

sql-serverperformanceinsertclustered-index
29
GWR

Wie aus den anderen Antworten bereits hervorgeht, kann SQL Server explizit sicherstellen, dass die Zeilen vor dem insert in gruppierter Indexreihenfolge sortiert werden.

Dies hängt davon ab, ob für den Clustered-Index-Operator im Plan die Eigenschaft DMLRequestSort festgelegt ist (was wiederum von der geschätzten Anzahl der eingefügten Zeilen abhängt).

Wenn Sie feststellen, dass SQL Server dies aus irgendeinem Grund unterschätzt, können Sie der Abfrage SELECT ein explizites ORDER BY Hinzufügen, um die Seitenteilung zu minimieren und die Fragmentierung der Operation INSERT zu minimieren

Beispiel: 

use tempdb;

GO

CREATE TABLE T(N INT PRIMARY KEY,Filler char(2000))

CREATE TABLE T2(N INT PRIMARY KEY,Filler char(2000))

GO

DECLARE @T TABLE (U UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),N int)

INSERT INTO @T(N)
SELECT number 
FROM master..spt_values
WHERE type = 'P' AND number BETWEEN 0 AND 499

/*Estimated row count wrong as inserting from table variable*/
INSERT INTO T(N)
SELECT T1.N*1000 + T2.N
FROM @T T1, @T T2

/*Same operation using explicit sort*/    
INSERT INTO T2(N)
SELECT T1.N*1000 + T2.N
FROM @T T1, @T T2
ORDER BY T1.N*1000 + T2.N


SELECT avg_fragmentation_in_percent,
       fragment_count,
       page_count,
       avg_page_space_used_in_percent,
       record_count
FROM   sys.dm_db_index_physical_stats(2, OBJECT_ID('T'), NULL, NULL, 'DETAILED')
;  


SELECT avg_fragmentation_in_percent,
       fragment_count,
       page_count,
       avg_page_space_used_in_percent,
       record_count
FROM   sys.dm_db_index_physical_stats(2, OBJECT_ID('T2'), NULL, NULL, 'DETAILED')
;

Zeigt an, dass T massiv fragmentiert ist

avg_fragmentation_in_percent fragment_count       page_count           avg_page_space_used_in_percent record_count
---------------------------- -------------------- -------------------- ------------------------------ --------------------
99.3116118225536             92535                92535                67.1668272794663               250000
99.5                         200                  200                  74.2868173956017               92535
0                            1                    1                    32.0978502594514               200

Bei T2 Ist die Fragmentierung jedoch minimal

avg_fragmentation_in_percent fragment_count       page_count           avg_page_space_used_in_percent record_count
---------------------------- -------------------- -------------------- ------------------------------ --------------------
0.376                        262                  62500                99.456387447492                250000
2.1551724137931              232                  232                  43.2438349394613               62500
0                            1                    1                    37.2374598468001               232

Umgekehrt möchten Sie manchmal SQL Server zwingen, die Zeilenanzahl zu unterschätzen, wenn Sie wissen, dass die Daten bereits vorsortiert sind, und eine unnötige Sortierung vermeiden möchten. Ein bemerkenswertes Beispiel ist das Einfügen einer großen Anzahl von Zeilen in eine Tabelle mit einem newsequentialid Clustered-Indexschlüssel. In Versionen von SQL Server vor Denali fügt SQL Server eine unnötige und möglicherweise teure Sortieroperation hinz . Dies kann vermieden werden durch

DECLARE @var INT =2147483647

INSERT INTO Foo
SELECT TOP (@var) *
FROM Bar

SQL Server schätzt dann, dass 100 Zeilen eingefügt werden, unabhängig von der Größe von Bar, die unter dem Schwellenwert liegt, bei dem eine Sortierung zum Plan hinzugefügt wird. Wie in den Kommentaren unten ausgeführt, bedeutet dies jedoch, dass die Einfügung die minimale Protokollierung leider nicht nutzen kann.

18
Martin Smith

Wenn der Optimierer entscheidet, dass es effizienter ist, die Daten vor dem Einfügen zu sortieren, geschieht dies irgendwo vor dem Einfügeoperator. Wenn Sie eine Sortierung als Teil Ihrer Abfrage einführen, sollte der Optimierer erkennen, dass die Daten bereits sortiert sind, und dies erneut unterlassen. Beachten Sie, dass der ausgewählte Ausführungsplan von Lauf zu Lauf variieren kann, abhängig von der Anzahl der Zeilen, die aus Ihrer Staging-Tabelle eingefügt wurden.

Wenn Sie Ausführungspläne des Prozesses mit und ohne explizite Sortierung erfassen können, fügen Sie sie Ihrer Frage zur Kommentierung hinzu.

Bearbeiten: 2011-10-28 17:00

@ Gonsalus Antwort scheint zu zeigen, dass immer eine Sortieroperation stattfindet, dies ist nicht der Fall. Demoskripte erforderlich!

Da die Skripte ziemlich groß wurden, habe ich sie nach Gist verschoben. Zur Erleichterung des Experimentierens verwenden die Skripte den SQLCMD-Modus. Die Tests laufen auf 2K5SP3, Dual Core, 8 GB.

Die Insert-Tests decken drei Szenarien ab:

  1. Staging-Datenclusterindex in derselben Reihenfolge wie das Ziel.
  2. Staging-Datenclusterindex in umgekehrter Reihenfolge.
  3. Staging-Daten, die von col2 geclustert werden und eine zufällige INT enthalten.

Führen Sie zuerst 25 Zeilen ein.

1st run, 25 rows

Alle drei Ausführungspläne sind gleich, es erfolgt nirgendwo im Plan eine Sortierung, und der Clustered-Index-Scan lautet "geordnet = falsch".

Zweiter Lauf, 26 Zeilen einfügen.

2nd run, 26 rows

Diesmal unterscheiden sich die Pläne.

  • Der erste zeigt den Clustered-Index-Scan als bestellt = falsch. Es ist keine Sortierung aufgetreten, da die Quelldaten entsprechend sortiert sind.
  • Im zweiten Schritt wird der Clustered-Index wie geordnet = true rückwärts gescannt. Wir haben also keine Sortieroperation, aber die Notwendigkeit, die Daten zu sortieren, wird vom Optimierer erkannt und in umgekehrter Reihenfolge gescannt.
  • Der dritte zeigt einen Sortieroperator.

Es gibt also einen Wendepunkt, an dem der Optimierer eine Sortierung für notwendig hält. Wie @MartinSmith zeigt, scheint dies auf den geschätzten einzufügenden Zeilen zu beruhen. Auf meinem Prüfstand benötigt 25 keine Sortierung, 26 (2K5SP3, Dual Core, 8 GB)

Das SQLCMD-Skript enthält Variablen, mit denen sich die Größe der Zeilen in der Tabelle (Ändern der Seitendichte) und die Anzahl der Zeilen in dbo.MyTable vor den zusätzlichen Einfügungen ändern können. Nach meinen Tests hat keiner einen Einfluss auf den Wendepunkt.

Wenn Leser dazu neigen, bitte führen Sie die Skripte aus und fügen Sie Ihren Wendepunkt als Kommentar hinzu. Interessiert zu hören, ob es zwischen Prüfständen und/oder Versionen variiert.

Bearbeiten: 2011-10-28 20:15

Wiederholte Tests auf demselben Rig, jedoch mit 2K8R2. Diesmal beträgt der Wendepunkt 251 Zeilen. Auch hier hat das Variieren der Seitendichte und der vorhandenen Zeilenanzahl keine Auswirkung.

12
Mark Storey-Smith

Die Klausel ORDER BY in der Anweisung SELECT ist redundant.

Es ist redundant, da die Zeilen, die eingefügt werden sollen, wenn sie sortiert werden müssen trotzdem sortiert sind.

Lassen Sie uns einen Testfall erstellen.

CREATE TABLE #Test (
    id INTEGER NOT NULL
);

CREATE UNIQUE CLUSTERED INDEX CL_Test_ID ON #Test (id);

CREATE TABLE #Sequence (
    number INTEGER NOT NULL
);

INSERT INTO #Sequence
SELECT number FROM master..spt_values WHERE name IS NULL;

Aktivieren Sie die Textanzeige der tatsächlichen Abfragepläne, damit wir sehen können, welche Aufgaben vom Abfrageprozessor ausgeführt werden.

SET STATISTICS PROFILE ON;
GO

Lassen Sie uns nun INSERT 2K-Zeilen ohne ORDER BY -Klausel in die Tabelle einfügen.

INSERT INTO #Test
SELECT number
  FROM #Sequence

Der tatsächliche Ausführungsplan für diese Abfrage lautet wie folgt.

INSERT INTO #Test  SELECT number    FROM #Sequence
  |--Clustered Index Insert(OBJECT:([tempdb].[dbo].[#Test]), SET:([tempdb].[dbo].[#Test].[id] = [tempdb].[dbo].[#Sequence].[number]))
       |--Top(ROWCOUNT est 0)
            |--Sort(ORDER BY:([tempdb].[dbo].[#Sequence].[number] ASC))
                 |--Table Scan(OBJECT:([tempdb].[dbo].[#Sequence]))

Wie Sie sehen können, gibt es einen Sortieroperator, bevor das eigentliche INSERT erfolgt.

Lassen Sie uns nun die Tabelle löschen und INSERT 2k Zeilen mit der Klausel ORDER BY in die Tabelle einfügen.

TRUNCATE TABLE #Test;
GO

INSERT INTO #Test
SELECT number
  FROM #Sequence
 ORDER BY number

Der tatsächliche Ausführungsplan für diese Abfrage lautet wie folgt.

INSERT INTO #Test  SELECT number    FROM #Sequence   ORDER BY number
  |--Clustered Index Insert(OBJECT:([tempdb].[dbo].[#Test]), SET:([tempdb].[dbo].[#Test].[id] = [tempdb].[dbo].[#Sequence].[number]))
       |--Top(ROWCOUNT est 0)
            |--Sort(ORDER BY:([tempdb].[dbo].[#Sequence].[number] ASC))
                 |--Table Scan(OBJECT:([tempdb].[dbo].[#Sequence]))

Beachten Sie, dass es sich um denselben Ausführungsplan handelt, der für die Anweisung INSERT ohne die Klausel ORDER BY verwendet wurde.

Jetzt ist die Operation Sort nicht immer erforderlich, wie Mark Smith in einer anderen Antwort gezeigt hat (wenn die Anzahl der einzufügenden Zeilen gering ist), sondern der Code ORDER BY -Klausel ist in diesem Fall immer noch redundant, da selbst mit einer expliziten ORDER BY keine Sort -Operation vom Abfrageprozessor generiert wird.

Sie können eine INSERT -Anweisung in eine Tabelle mit einem Clustered-Index optimieren, indem Sie ein minimal protokolliertes INSERT verwenden. Dies ist jedoch für diese Frage nicht zulässig.

Aktualisiert am 02.11.2011: Wie Mark Smith gezeigt hat , INSERTs in eine Tabelle mit einem Cluster Der Index muss möglicherweise nicht immer sortiert werden. In diesem Fall ist die Klausel ORDER BY jedoch auch redundant.

8
gonsalu