Datenbanken und mehr

11. Dezember 2007

Neu in Katmaii – GROUPING SETS

Filed under: Katmai, T-SQL — Urs Gehrig @ 08:54

Hast du schon mal probiert, mit SQL Server 2005 in ein und demselben Query mehrere GROUP BY Kriterien miteinander zu verbinden? Mit CUBE und ROLLUP kommst du bestimmt schon weit, mit der in Katmaii neuen GROUPING SETS Klausel sind dir nun aber praktisch keine Grenzen mehr gesetzt. Warum die ganzen Kriterien in bloss einem SQL Statement und nicht mit mehren, welche du mit UNION zusammen fügst? Naja, geht bestimmt, aber wir wollen ja ein effizientes Query. Nun aber zu GROUPING SETS. Fangen wir mit einem Beispiel an:

USE AdventureWorks;
GO

SELECT CustomerID
    
, SalesPersonID
    
, YEAR(OrderDate) AS OrderYear
    
, MONTH(OrderDate) AS OrderMonth
    
, SUM(SubTotal) AS Total
FROM Sales.SalesOrderHeader
WHERE CustomerID < 10
GROUP BY
GROUPING SETS
(
 
(CustomerID, SalesPersonID),
 
(CustomerID),
 
(SalesPersonID)
),
GROUPING SETS
(
 
(YEAR(OrderDate), MONTH(OrderDate)),
 (YEAR(OrderDate))
)
ORDER BY CustomerID, SalesPersonID, OrderYear, OrderMonth

Was macht dieses Query? Nichts anderes als insgesamt sechs verschiedene GROUP BY’s. Nämlich

  1. GROUP BY CustomerID, SalesPersonID, YEAR(OrderDate), MONTH(OrderDate)
  2. GROUP BY CustomerID, SalesPersonID, YEAR(OrderDate)
  3. GROUP BY CustomerID, YEAR(OrderDate), MONTH(OrderDate)
  4. GROUP BY CustomerID, YEAR(OrderDate)
  5. GROUP BY SalesPersonID, YEAR(OrderDate), MONTH(OrderDate)
  6. GROUP BY SalesPersonID, YEAR(OrderDate)

Kommt in einer GROUB BY Klausel ein einzelnes Feld nicht vor, erhält das Resultat an der entsprechenden Stelle den Wert NULL. Das Ergebnis aus dem obigen Query sieht in etwa wie folgt aus; damit denke ich, dass du die Funktionsweise von GROUPING SETS nun verstehst, auch ohne dass ich hierzu noch mehr Worte verliere:

CustomerID SalesPersonID OrderYear OrderMonth Total
NULL 268 2002 7 3569.4338
NULL 275 2003 1 97037.566
NULL 275 2003 4 94133.8413
NULL 275 2003 7 73368.0931
NULL 275 2003 10 73455.3404
NULL 275 2004 NULL 176878.6938
NULL 275 2004 3 15431.5196
NULL 275 2004 6 31559.7518
NULL 276 2002 10 2449.6033
NULL 276 2003 1 1769.1174
NULL 276 2003 9 59.9326
NULL 276 2004 NULL 2696.2418
NULL 276 2004 6 2137.231
NULL 277 2001 9 18555.8149
NULL 277 2002 NULL 390424.4465
NULL 277 2002 6 11796.1804
NULL 277 2002 9 71112.1316
NULL 277 2002 12 51198.5404
NULL 279 2002 11 290.1614
NULL 279 2003 2 4903.775
NULL 279 2003 8 10309.0561
NULL 279 2004 NULL 14645.1583
NULL 279 2004 5 8441.6436
NULL 280 2001 8 13216.0537
NULL 280 2002 NULL 65489.709
NULL 280 2002 5 31423.5209
NULL 281 2002 9 20720.6175
NULL 281 2003 NULL 54485.9727
NULL 281 2003 6 9134.6608
NULL 281 2003 12 18431.5666
NULL 281 2004 3 7950.2303
NULL 283 2002 NULL 14166.2185
NULL 283 2002 11 4949.8589
NULL 283 2003 2 1574.1247
NULL 283 2003 8 3534.1671
NULL 283 2004 NULL 4490.7426
NULL 283 2004 5 822.009
1 NULL 2001 8 13216.0537
1 NULL 2002 NULL 65489.709
1 NULL 2002 5 31423.5209
1 280 2001 8 13216.0537
1 280 2002 NULL 65489.709
1 280 2002 5 31423.5209
2 NULL 2002 8 9216.3596
2 NULL 2003 NULL 10966.5406
2 NULL 2003 5 1751.5987
2 NULL 2003 11 4106.6501
2 NULL 2004 2 3668.7336
2 283 2002 NULL 14166.2185
2 283 2002 11 4949.8589
2 283 2003 2 1574.1247
2 283 2003 8 3534.1671
2 283 2004 NULL 4490.7426
2 283 2004 5 822.009
3 NULL 2001 9 18555.8149
3 NULL 2002 NULL 152392.4906
3 NULL 2002 6 11796.1804
3 NULL 2002 12 51198.5404
3 NULL 2003 3 27899.9579
3 NULL 2003 9 83436.1754
3 NULL 2004 NULL 46991.2714
3 NULL 2004 6 31559.7518
3 275 2003 3 27899.9579
3 275 2003 9 83436.1754
3 275 2004 NULL 46991.2714
3 275 2004 6 31559.7518
3 277 2001 9 18555.8149
3 277 2002 NULL 152392.4906
3 277 2002 6 11796.1804
3 277 2002 12 51198.5404
4 NULL 2002 7 118394.1258
4 NULL 2003 NULL 337994.8408
4 NULL 2003 4 94133.8413
4 NULL 2003 10 73455.3404
4 NULL 2004 1 67414.7924
4 275 2003 NULL 337994.8408
4 275 2003 4 94133.8413
4 275 2003 10 73455.3404
4 275 2004 1 67414.7924
4 277 2002 NULL 238031.9559
4 277 2002 10 119637.8301
5 NULL 2002 9 20720.6175
5 NULL 2003 NULL 54485.9727
5 NULL 2003 6 9134.6608
5 NULL 2003 12 18431.5666
5 NULL 2004 3 7950.2303
5 281 2002 NULL 30199.7195
5 281 2002 12 9479.102
5 281 2003 3 8456.292
5 281 2003 9 18463.4533
5 281 2004 NULL 18679.7381
5 281 2004 6 10729.5078
6 NULL 2003 9 59.9326
6 NULL 2004 NULL 2696.2418
6 NULL 2004 6 2137.231
6 276 2003 9 59.9326
6 276 2004 NULL 2696.2418
6 276 2004 6 2137.231
7 NULL 2002 7 3569.4338
7 NULL 2003 NULL 3365.4121
7 NULL 2003 4 1596.2947
7 268 2002 7 3569.4338
7 276 2002 10 2449.6033
7 276 2003 1 1769.1174
8 NULL 2003 NULL 17592.0783
8 NULL 2003 11 8888.6076
8 NULL 2004 2 5571.0288
8 279 2003 NULL 17592.0783
8 279 2003 11 8888.6076
8 279 2004 2 5571.0288
9 NULL 2002 NULL 290.1614
9 NULL 2003 NULL 10318.1878
9 NULL 2003 5 586.989
9 NULL 2003 11 3221.8384
9 NULL 2004 2 632.4859
9 279 2002 NULL 290.1614
9 279 2003 NULL 10318.1878
9 279 2003 5 586.989
9 279 2003 11 3221.8384
9 279 2004 2 632.4859
Advertisements

23. November 2007

Data Change Capture

Filed under: Katmai — Urs Gehrig @ 21:24

Data Change Capture ist ein cooles neues Feature von SQL Server 2008; Änderungen an Datensätzen (INSERT, UPDATE und DELETE) können automatisch in einer Logtabelle protokolliert werden. Beim Einrichten von Data Change Capture spezifizierst du, von welchen Tabellen, welche Spalten beobachtet, respektive protokolliert werden sollen. Ab dann schreibt der SQL Server jede Mutation in eine Logtabelle; die Daten hierfür holt er dabei direkt aus dem Transaktions Logfile. Mit Hilfe speziellen UDF’s kannst du auf diese Daten elegant zugegriffen werden. Dabei legt der Server für jede zu beobachtende Tabelle einen eigenen Satz von UDF’s an.

Die Logtabellen können zum Beispiel für das Aktualisieren eines Datawarehouse nützlich zu sein. Anstelle immer die ganzen riesigen Tabellen nach Mutationen zu durchstöbern, genügt es so sich auf diese Logtabellen zu konzentrieren. SSIS kennt hierfür sogar einen eigenen Task.

Bei grossen Datenbeständen mit häufigen Mutationen können diese Logtabellen sehr schnell riesig werden. SQL Server schmeisst daher automatisch alte Datensätze fort; per Default behält er jeden Datensatz drei Tage lang; aber auch dies kannst du nach Belieben abändern. Für dieses Aufräumen legt der SQL Server eigens einen SQL Server Agent Job an.

So, genug geschrieben; erst mache ich mal mit einem Beispiel weiter:

USE AdventureWorks;
GO
 
— determine whether a database is enabled
SELECT name, is_cdc_enabled
FROM sys.databases;

— enable the database for change data capture
EXECUTE sys.sp_cdc_enable_db_change_data_capture;

— determine whether a source table has been enabled for change data capture
SELECT name, is_tracked_by_cdc
FROM sys.tables;

— create a capture instance for individual source tables
— (maximum 2 instances per table)
EXECUTE sys.sp_cdc_enable_table_change_data_capture
    @source_schema = ‚Person‘,
    @source_name = ‚Contact‘,
    @role_name = ‚cdc_admin‘,
    @supports_net_changes = 1,
    @captured_column_list = N‚ContactID, LastName, EmailAddress‘;

— make some changes
UPDATE Person.Contact
SET LastName = N‚Gehrig‘
FROM Person.Contact
WHERE ContactID = 1;

UPDATE
Person.Contact
SET LastName = N‚Meier‘
FROM Person.Contact
WHERE ContactID = 1;

UPDATE
Person.Contact
SET LastName = N‚Achong‘
FROM Person.Contact
WHERE ContactID = 1;

— querying change data
DECLARE @from_lsn binary(10), @to_lsn binary(10);
SET @from_lsn = sys.fn_cdc_get_min_lsn(‚person_contact‘);
SET @to_lsn = sys.fn_cdc_get_max_lsn();
SELECT *
FROM cdc.fn_cdc_get_all_changes_person_contact(@from_lsn, @to_lsn, ‚all‘);
SELECT *
FROM cdc.fn_cdc_get_net_changes_person_contact(@from_lsn, @to_lsn, ‚all‘);

— disable the database for change data capture
EXECUTE sys.sp_cdc_disable_db_change_data_capture

Die beiden letzten SELECT Statements rufen mit Hilfe der speziellen UDF’s die mutierten Daten ab. Das erste Statement (all changes) liefert:

__$start_lsn           __$seqval              __$operation __$update_mask ContactID LastName EmailAddress
0x0000002D000016E80016 0x0000002D000016E80014 4            0x02           1         Gehrig   gustavo0@adventure-works.com
0x0000002D000016F80004 0x0000002D000016F80002 4            0x02           1         Meier    gustavo0@adventure-works.com
0x0000002D000017080004 0x0000002D000017080002 4            0x02           1         Achong   gustavo0@adventure-works.com

Für jede Mutation gibt es also genau einen Eintrag. Die ersten beiden Spalten beschreiben den zeitlichen Ablauf der Updates und kommen aus dem Transaktionslog raus. Eine $operation von 4 beschreibt einen Update Befehl und die $update_mask 0x2 gibt an, dass nur das zweite Feld, welches beobachtet wird (also LastName) mutiert wurde. Am Schluss werden dann noch die aktuellen, d.h. nach dem Commit der Transaktion gültigen, Werte aller Felder aufgeführt. Cool oder? Wenn du in deiner Tabelle auch noch Spalten für Benutzer, PC-Name etc. einfügst, dann hast du ein vollständiges Security-Log.

Das zweite Statement (net changes) liefert:

__$start_lsn           __$operation __$update_mask ContactID LastName EmailAddress
0x0000002D000017080004 4            NULL           1         Achong   gustavo0@adventure-works.com

Jetzt bekommst du nur noch eine Zeile; nämlich den Zusammenzug aller Änderungen pro Datensatz. Die $operation ist wiederum ein Update Befehl, die $update_mask aber NULL. Warum? Ganz einfach. Der LastName vom letzten Update (Achong) ist nämlich der Wert, der zu Begin schon drin stand. Somit hat der Datensatz unter dem Strich keine Änderung erfahren; daher der Wert NULL.

So, das war’s für heute. Viel Spass beim ausprobieren.

10. September 2007

Katmai zum Vierten

Filed under: Katmai — Urs Gehrig @ 10:20

Seit dem 31. July ist der CTP 4 (oder auch CTP July genannt) von Katmai allgemein verfügbar. Du bekommst ihn unter http://connect.microsoft.com/sqlserver/, benötigst hierfür aber eine Windows Live ID. Sowohl Beispielcodes als auch die Beispiel-DB AdventureWorks ist nicht Bestandteil des Downloads; du kriegst dies aber ebenfalls online unter http://codeplex.com/SqlServerSamples.

Bereits in einem meiner früheren Blogs habe ich dir empfohlen, Katmai in einer Virtual PC/Server Umgebung zu installieren. Irgendwie scheint Microsoft meinen Beitrag gelesen zu haben ;-). Seit Ende August kannst du bei Microsoft ein solches Image runterladen (http://www.microsoft.com/downloads/details.aspx?FamilyID=6a39affa-db6e-48a9-82e4-4efd6705f4a6&DisplayLang=en). Der Download ist in vier Dateien von Total ca. 2.3 GB aufgeteilt. Entgegen den von Microsoft beschriebenen Requirements benötigst du nicht zwingend einen Virtual Server 2005 R2; bei mir läuft das ganze auch auf einem Virtual PC 2007 (DELL Laptop mit Vista).

Ach ja: Suchst du das Passwort für den Windows Server 2003? Das ist im Readme der Evaluation Edition versteckt: Evaluation1.

Viel Spass beim entdecken der neuen Features! Viellichts kommst du wieder mal hierhin zurück und postest deine Erfahrungen als Kommentar zu diesem Beitrag?

3. September 2007

Gregorianischer Kalender zum zweiten

Filed under: Katmai, T-SQL — Urs Gehrig @ 21:56

Bereits in einem früheren Blog habe ich über das Speichern eines Datums in der DB gesprochen. Mit dem Erscheinen der nächsten SQL Server Version (Codename Katmai) möchte ich dieses Thema nochmals aufnehmen, hat sich diesbezüglich doch einiges getan. Konkret, der SQL Server kennt neue Datentypen rund um Datum und Uhrzeit:

Data type

Format

Range

Accuracy

Storage size

(bytes)

User-defined fractional second precision

Time zone offset

time

hh:mm:ss[.nnnnnnn]

00:00:00.0000000 through 23:59:59.9999999

100 nanoseconds

3 to 5

yes

no

date

YYYY-MM-DD

00001-01-01 through 9999-12-31

1 day

3

no

no

smalldatetime

YYYY-MM-DD hh:mm:ss

1900-01-01 through 2079-06-06

1 minute

4

no

no

datetime

YYYY-MM-DD hh:mm:ss[.nnn]

1753-01-01 through 9999-12-31

0.333 second

8

no

no

datetime2

YYYY-MM-DD hh:mm:ss[.nnnnnnn]

0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999

100 nanoseconds

6 to 8

yes

no

datetimeoffset

YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm

00001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC)

100 nanoseconds

8 to 10

yes

yes

Die Datentypen datetime und smalldatetime kennen wir bereits vom SQL Server 2005 her; hier hat sich nichts geändert. Diese Datentypen sind 100%-ig Kompatibel zur Implementation im SQL Server 2005. Die vier weiteren Datentypen time, date, datetime2 und datetimeoffset sind neu hinzugekommen. User-defined fractional second precision gibt an, ob du die Anzahl Nachkommastellen der Sekunde selber definieren kannst (im Bereich von 0 – 7). Time zone offset gibt an, ob in diesem Datentyp Information zur Zeitzone (inklusive Sommerzeit) abgelegt werden kann.

Wie bis anhin so folgen auch weiterhin alle Datumsangaben dem Gregorianischen Kalender. Eins irritiert mich aber schon: Der Gregorianische Kalender wurde erst im 16. Jahrhundert von Papst Gregor XIII eingeführt. Was passiert also mit den Daten vor dieser Zeit? Immerhin gehen die neuen Datentypen bis zum Jahr 1 zurück. Naja, sobald ich dahinter gekommen bin wie das funktioniert, schreibe ich einen kleinen Update zu diesem Blog J. Vielleicht hast du mir ein eine Erklärung oder auch nur einen kleinen Tipp? Merci!

Mein neuer Favorit ist ganz klar datetimeoffset. Damit kann ich praktisch alles machen: Zeitstempel für Logeinträge einer global verteilten Applikation verwalten (hierfür benötige ich die Zeitzonen), ein Geschichtsbuch ab Jesu Geburt schreiben (hierfür benötige ich Jahrzahlen ab 1) oder auch (vielleicht) die nächste Rangliste für den 100m Sprint an der Weltklasse Zürich (bis die auch die Tausendstelsekunde auswerten geht es bestimmt nicht mehr lange J und dann brauche ich die höhere Genauigkeit bei der Zeit). Soviel fürs erste. Viel Spass beim weiter entdecken der neuen Features von Katmai.

ACHTUNG: Alle hier gemachten Angaben basieren auf SQL Server 2008 CTP July und können bis zum Erscheinen der endgültigen Version noch ändern.

17. Juni 2007

MERGE Statement – ein cooles Feature von Katmai

Filed under: Katmai, Performance, SQL Server, T-SQL — Urs Gehrig @ 22:35

Beim Durchsehen von Katmai – dem Nachfolger von SQL Server 2005 – bin ich auf eine coole Erweiterung in der DB-Engine gestossen; dem MERGE Statement.

Das MERGE Statement vereint die drei Statements INSERT, UPDATE und DELETE. Neu kann mit nur einem einzigen Statement ein ganzes Datenset in einem Rutsch in die DB geschrieben werden, unabhängig davon, ob einzelne Records bereits erfasst (und demnach ein UPDATE benötigen) , neu sind (und somit ein INSERT benötigen) oder aber aus der DB gelöscht werden müssen (und somit ein DELETE benötigen). Natürlich erwarte ich von so einem neuen MERGE Statement auch eine erhebliche Performance Verbesserung gegenüber der alt hergebrachten Methode mit zwei oder drei separaten Statements (INSERT, UPDATE und DELETE). Ob dem so ist, werde ich dir gleich nachfolgend versuchen zu beweisen. Zuerst aber mal ein Beispiel zum MERGE Statement (dies habe ich mir aus dem BOL von Katmai ausgelehnt).

Szenario: In einem täglichen Prozess soll die ProductInventory Tabelle mit den Einträgen aus SalesOrderDetail aktualisiert werden. Die Spalte Quantity aus ProductInventory soll pro Produkt um die Anzahl der verkauften Produkte reduziert werden. Fällt ein Produkt auf eine Menge Null zurück, muss dieses Produkt aus ProductInventory gelöscht werden.

Lösung mit SQL Server 2005:

WITH Selling(ProductID, OrderQty) AS
(
    SELECT ProductID, SUM(OrderQty)
    FROM Sales.SalesOrderDetail sod
      JOIN Sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID
                                     AND soh.OrderDate = {d ‚2001-07-03‘}
    GROUP BY ProductID
)
DELETE Production.ProductInventory
FROM Production.ProductInventory pi
  JOIN Selling src ON (pi.ProductID = src.ProductID)
WHERE pi.Quantity = src.OrderQty;

WITH Selling(ProductID, OrderQty) AS
(
    SELECT ProductID, SUM(OrderQty)
    FROM Sales.SalesOrderDetail sod
      JOIN Sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID
                                     AND soh.OrderDate = {d ‚2001-07-03‘}
    GROUP BY ProductID
)
UPDATE Production.ProductInventory
SET Quantity = pi.Quantity src.OrderQty
FROM Production.ProductInventory pi
  JOIN Selling src ON (pi.ProductID = src.ProductID)
WHERE pi.Quantity > src.OrderQty;

Lösung mit Katmai:

MERGE Production.ProductInventory AS pi
USING (SELECT ProductID, SUM(OrderQty)
       FROM Sales.SalesOrderDetail sod

         JOIN Sales.SalesOrderHeader soh
ON sod.SalesOrderID = soh.SalesOrderID
                                        AND soh.OrderDate = {d ‚2001-07-03‘}

       GROUP BY ProductID) AS src (ProductID, OrderQty)
ON (pi.ProductID = src.ProductID)
WHEN MATCHED AND pi.Quantity src.OrderQty = 0
    THEN DELETE
WHEN MATCHED
    THEN UPDATE SET pi.Quantity = pi.Quantity src.OrderQty;

Also, eleganter ist die Lösung von Katmai allemal; wie sieht es aber mit der Performance aus? OK, ich weiss – Performance Tests mit CTP’s sind nicht gerade besonders fair. Ich bin aber auch nicht an absoluten Zahlen interessiert, sondern vielmehr an Trends. Daher habe ich die IO’s (SET STATISTICS IO ON) der beiden Lösungen miteinander verglichen:

mit SQL Server 2005:

Table ‚ProductInventory‘. Scan count 4, logical reads 8, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‚SalesOrderDetail‘. Scan count 5, logical reads 15, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‚SalesOrderHeader‘. Scan count 1, logical reads 703, physical reads 2, read-ahead reads 699, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(0 row(s) affected)

Table ‚ProductInventory‘. Scan count 4, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‚SalesOrderDetail‘. Scan count 5, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‚SalesOrderHeader‘. Scan count 1, logical reads 703, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‚ProductInventory‘. Scan count 0, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(8 row(s) affected)

mit Katmai:

Table ‚ProductInventory‘. Scan count 4, logical reads 8, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‚SalesOrderDetail‘. Scan count 5, logical reads 15, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‚SalesOrderHeader‘. Scan count 1, logical reads 703, physical reads 2, read-ahead reads 699, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‚ProductInventory‘. Scan count 0, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(8 row(s) affected)

Der Effekt des neuen Merge Statements scheint mir offensichtlich zu sein; rund die Hälfte der IO’s. Und das ist schlicht und einfach Super!

Older Posts »

Erstelle eine kostenlose Website oder Blog – auf WordPress.com.