SQL Server 2012 – Columnstore Indizes updaten

In Columnstore Indexex habe ich bereits den neuen spaltenorientierten Index beschrieben.  Ein wichtige Tatsache muss allerdings noch beachtet werden: Tabellen mit Columnstored Indizes sind nicht update fähig. Wer denkt, dass dies ein Mag-ich-nicht Feature ist, hat zwar Recht, aber dafür gibt es 2 nette Workarounds:

 

  1. Workaround 1: Drop table … create table

kein weitere Kommentar dazu

2.  Workaround 2: Partitionswechsel

Die Idee, die hier dahinter steckt, ist die Verwendung der Partitionierung. Daten werden in eine partitionierte Tabelle (mal Tab A genannt) geschrieben, auf die der Columnstore Index gelegt wird. Der Trick ist nun eine weitere Tabelle (Tab B) anzulegen, die ebenfalls auf das selbe Partitionierungsschema gelegt wird, wie Tab A. Mittels eines Partitionswechsels (switch) kann Tab B zu Tab A hinzugefügt werden.  Verwaltungsaufwand dafür so gut wie, da keine Datenverschiebung stattfindet, sondern lediglich die “Zeiger” auf die Tabellen geändert werden.

Kompliziert? Nicht wirklich…Sehen wir uns das mal an:

BEISPIEL

Zunächst wird eine Tabelle Umsatz erstellt mit Clustered Index und Columnstore Index. Ca 1. Mio Daten, die sich auf einen Datumsbereich von 1.9.2011 bis inkl. 4.9.2011 bezieht. Die Tabellen bzw der Clustered Index werden pro Tag partitioiert.

 

   1: --Partitionierungsfunktion

   2: create partition function pf_datum (date) as range left for values

   3:   ('20110901', '20110902', '20110903', '20110904','20110905');

   4: go

   5:  

   6: --Partitionierungsschema

   7: create partition scheme ps_datumPrim as  partition pf_datum all to ([PRIMARY]);

   8: go

   9:  

  10: --Demotabelle

  11: create table Umsatz (

  12:     uid int not null identity (1,1),

  13:     UDatum date not null,

  14:     PrID smallint not null,

  15:     KundenID int not null,

  16:     Summe money)

  17:     on ps_datumPrim(Udatum);

  18: go

  19:  

  20: --Clustered index

  21: create unique clustered index 

  22:     IX_Udatum on Umsatz (Udatum, PrId, KundenID, summe) on ps_datumPrim(Udatum);

  23:  

  24:  

  25: -- 1 mio Datensätze von 12.7.2011 bis 15.7.2011

  26: set nocount on;

  27: go

  28: declare @i int = 0;

  29: begin transaction;

  30: while @i < 1000000

  31: begin

  32:     declare @Udatum date = dateadd(day, @i /250000.00, '20110901');

  33:     insert into Umsatz (Udatum, PrID, KundenID, summe)

  34:         values (@Udatum, rand()*10000, rand()*100 + 100, rand()* 10.000+1);

  35:     set @i += 1;

  36:     if @i % 10000 = 0

  37:     begin

  38:         raiserror (N'Inserted %d', 0, 1, @i);

  39:         commit;

  40:         begin tran;

  41:     end

  42: end

  43: commit;

  44: go

  45:  

  46: --Wir sehen 250000 Datensätze pro Zeile

  47: --Der Index wird natürlich pro Partition abgebildet

  48: select * from sys.system_internals_partitions p

  49:     where p.object_id = object_id('umsatz');

  50:  

  51: select au.* from sys.system_internals_allocation_units au

  52:     join sys.system_internals_partitions p

  53:         on p.partition_id = au.container_id

  54:     where p.object_id = object_id('umsatz');

  55: go

Ein Blick auf die partitionierte Daten vor dem Columnstored Index

image

Ein Blick nach dem Anlegen des Columnstored Index

image

Wollte man nun Abfragen gegen ein bestimmtes Datum testen, würde man durch den Columnstore INdex einen Vorteil von ca. dem 4-fachen haben. Statt knapp 1640 Seiten werden nur noch 360 Seiten gelesen.

   1: set statistics io on;

   2: select count(*), sum(summe) from umsatz where Udatum = '1.09.2011'

 

Im Ausführungsplan läßt sich der Index beobachten:

image

 

Nun kommen neue Daten dazu. Diese legen wir zunächst in einer neuen Tabellen ab, die später nicht mehr gebraucht wird. Tabelle und Indizes sind nicht partitioniert. Eines kleines Problem.. die ID in der Tabelle dürfen natürlich nicht doppelt vorkommen, da wir anschließend die Daten aus der Staging-Tabelle in die Umsatz Tabelle switchen wollen.

 

   1: ---UPDATE Workaround: Partitionswechsel oder löschen und neuerstellen

   2: create table Umsatz_Stagingtable (

   3:     uid int not null identity (1000000,1),

   4:     UDatum date not null,

   5:     PrID smallint not null,

   6:     KundenID int not null,

   7:     Summe money,

   8:     constraint check_datum check (udatum = '20110905'))

   9:     on [Primary];

  10: go

  11:  

  12:  

  13: create unique clustered index IX_UDatum_staging

  14:    on Umsatz_stagingtable (Udatum, PrID, Kundenid, Summe) on [Primary];

  15: go

  16:  

  17: --250.000 Datensätze für 5.9.2011

  18: set nocount on

  19: go

  20: declare @i int = 0;

  21: begin transaction;

  22: while @i < 250000

  23: begin

  24:     insert into Umsatz_Stagingtable(Udatum, prid, Kundenid, summe)

  25:         values ('20110905', rand()*10000, rand()*100 + 100, rand()* 10.000+1);

  26:     set @i += 1;

  27:     if @i % 10000 = 0

  28:     begin

  29:         raiserror (N'Inserted %d', 0, 1, @i);

  30:         commit;

  31:         begin tran;

  32:     end

  33: end

  34: commit;

  35: go

  36:  

  37: --Columnstore Index

  38: create columnstore index cs_Umsatz_staging

  39:           on Umsatz_stagingtable (Udatum, PrID, Kundenid, Summe) 

  40: go

In diese Stagingtabelle kommen also die neuen Werte rein. Die Tabellen liegt momentan nicht in der gleichen Partition auf dem Datenträger. Allersdings liegen sie tatsächlich auf dem gleichen Datenträger. Das wird gleich noch wichtig werden.

Das entscheidende geschieht nun hier:

   1: alter partition scheme ps_datumPrim next used [PRIMARY];

   2: go

   3: alter partition function pf_datum() split range ('6.9.2011');

   4: go

   5: alter table umsatz_stagingtable switch to umsatz partition $PARTITION.PF_datum('20110905');

   6: go

In 1: wird zunächst die notwendige Erweiterung der Dateigruppe bekannt gegeben. in 2: können wir den anschließenden Split in der partitionierten Tabelle Umsatz erreichen. Ab diesem Zeitpunkt haben wir eine freie Partition in der Tabelle Umsatz für Daten des 5.9.2011.  Der Trick besteht nun darin, dem SQL Server zu sagen, dass die freie Partition der Tabelle Umsatz_Staging entpricht. Oder anders gesagt: Wir ändern die Information in der Datenbank so, dass die freien Partition genau der Tabelle Umsatz_Staging entpricht.  (hier müßte eine gewisse andächtige Stille eintreten)

Das Ergebnis ist, dass die Stagingtabelle leer sein wird. Die Daten dieser Tabelle sind nun in der Umsatztabelle, allerdings wurden keine Daten verschoben. Wir haben schließlich nur die Ortsangaben geändert. Daher ist es wichtig die Stagingtabelle auf die gleichen Dateigruppe zu legen. Der Switch Vorgang selbst dauert daher nur Millisekunden.

Cool…

PS: Unter mehreren verborgenen Händen soll der Columnstore Index in der Final Version updatebar sein

Related posts:

Random Posts

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

*
*