Overblog Folge diesem Blog
Edit post Administration Create my blog
Blog von Olaf Helper

Massendaten zwischen Tabellen schieben

28. Januar 2009 , Geschrieben von Olaf Helper Veröffentlicht in #T-SQL

Preisfrage: Wie verschiebt man in einer MS SQL Server Datenbank quasi im Bruchteil einer Sekunde mal eben ein paar Millionen Datensätze von einer Tabelle (Partition) in eine andere? BULK COPY vielleicht?
Das „Zauberwort“ heißt ALTER TABLE … SWITCH

 ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name
SWITCH [ PARTITION source_partition_number_expression ]
TO target_table
[ PARTITION target_partition_number_expression ]

 

Stellen wir uns mal folgendes Szenario vor:

Man hat ein Datawarehouse, lädt über einen ETL-Prozess Massendaten in eine Staging-Tabelle und modelliert die Daten so wie man sie dann final benötigt. Nun müssen „nur noch“ die Daten in die eigentliche Tabelle rüber.

Durch INSERT INTO … SELECT kann man sie rüber kopieren. Kopieren bedeutet dabei aber auch, dass man für einen Zeitraum die Daten doppelt hat und den entsprechenden doppelten Speicherplatz braucht, mit den LOG sogar das 3fache. Zudem beansprucht das je nach Menge entsprechend Zeit, in der es dann entsprechende Sperren auf der Tabelle gibt.

Dabei hat man die Daten doch schon in der Datenbank. Hier kommt die mit MS SQL Server 2005 eingeführte Option SWITCH PARTITION beim ALTER TABLE ins Spiel. Es biegt eigentlich nur die Zeiger der Datenseiten von der Quell- auf die Zieltabelle (Partition) um; verständlicherweise muss deshalb das Ziel leer sein.

Wie funktioniert das nun in der Praxis? Gehen wir es mal anhand eines Beispieles durch; Script siehe unten. Zunächst legen wir eine neue Datenbank (kann auch in einer vorhandene erfolgen), sowie zwei identische Tabellen und füllen eine mit ein paar Daten. Möchte man es wegen der Performance mit mehr Daten testen, einfach die Schleife entkommentieren und die Anzahl der Loops anpassen.

Funktioniert doch super … und das sogar in der Express Edition (natürlich nicht mit Partitionen). Auf meinem einfachen Rechner dauert der SWITCH mit 100k DS ganz 90 ms.

Denkbare Einsatzszenarien sind z.B.:

 Datawarehouse, wie oben beschrieben

-  Faktentabelle Aktuell / Archivtabelle mit Partitionen über Zeitdaten (z.B. Jahr/Monat). Möchte man den zuletzt abgeschlossen Monat ins Archiv verschieben => in der Tabelle neue Partition anlegen und SWITCH; fertig.

-  Tabelle in andere Dateigruppe verschieben: Tabelle mit identischem Design unter anderem Namen in der Zieldateigruppe anlegen, SWITCH, alte Tabelle löschen und neue Umbenennen.

 

 -- Demo für (Partition) SWITCH  
 SET NOCOUNT ON  
 USE [master]  
 GO  
 -- Leere DB mit Defaults anlegen  
 CREATE DATABASE [PartitionSwitch] 
 GO  
 USE [PartitionSwitch] 
 GO  
   
 --Zwei identische Tabellen mit PK+Index anlegen  
 CREATE TABLE SourceTable  
  (ID int NOT NULL PRIMARY KEY CLUSTERED,  
  Datum datetime NOT NULL,  
  String char(100) NOT NULL);  
 CREATE INDEX IX_SourceTable_ID ON SourceTable(String);  
   
 CREATE TABLE DestTable  
  (ID int NOT NULL PRIMARY KEY CLUSTERED,  
  Datum datetime NOT NULL,  
  String char(100) NOT NULL);  
 CREATE INDEX IX_DestTable_ID ON DestTable(String);  
 GO  
   
 -- Minibeispiel für schnellen Test  
 INSERT INTO SourceTable VALUES (1, GETDATE(), 'A') 
 INSERT INTO SourceTable VALUES (2, GETDATE(), 'B') 
 INSERT INTO SourceTable VALUES (3, GETDATE(), 'C') 
 -- Und/oder eine größere Anzahl Datensätze  
 -- Einfach nur entkommentieren 
 /*DECLARE @iLoop int; SET @iLoop = 4; 
 WHILE @iLoop < 100000 -- Hier Anzahl DS anpassen! 
 BEGIN  
  INSERT INTO SourceTable  
  VALUES (@iLoop, Getdate(), CONVERT(varchar, @iLoop));  
  SET @iLoop = @iLoop + 1  
 END 
 */ 
 GO  
 -- Einmal zur Kontrolle  
 SELECT 'SRC' AS T, COUNT(*) AS A FROM SourceTable UNION ALL  
 SELECT 'DST' AS T, COUNT(*) AS A FROM DestTable;  
   
 -- Nun der Switch; selbst mit 1 Mio Datensätze  
 -- geht es im Sekunden-Bruchteil, da nur die Seiterzeiger  
 -- umgebogen werden 
 SELECT GetDate(); -- Wie lange dauert es? 

ALTER TABLE SourceTable SWITCH TO DestTable;

 GO 
 SELECT GetDate(); 
 GO  
   
 -- Alles verschoben? Ja !!!  
 SELECT 'SRC' AS T, COUNT(*) AS A FROM SourceTable UNION ALL  
 SELECT 'DST' AS T, COUNT(*) AS A FROM DestTable;  
 GO  
 -- Index auch? Klaro (Ausführungsplan anschalten!) 
 SELECT String FROM DestTable WHERE String = '100';  
 GO  
   
 -- Aufräumen  
 DROP TABLE SourceTable;  
 DROP TABLE DestTable;  
 GO  
 USE [master];  
 GO  
   
 DROP DATABASE PartitionSwitch;  
 GO  

Diesen Post teilen

Repost 0

Kommentiere diesen Post