Glorf IT

Als ich mit dem PIVOT-Konstrukt arbeitete und performante Ergebnisse haben wollte, muss ich erst verstehen, wie es intern arbeitet. Das beschrieb ich im Artikel "SQL-Server-2005: schnelles Pivot?". Diese Serien soll meine Erfahrungen weitergeben und bei Verstehen der Zusammenhänge helfen. Mit ein paar Kniffen kann man dann die Ausführung erheblich beschleunigen. Das kommt im nächsten Artikel. Der Quellcode zu diesem Artikel steht übrigens hier.

• Artikel 1: Wie wird Pivot abgearbeitet?
• Artikel 2: Störer beim PIVOT eliminieren
• Artikel 3: Pivot-Performance
• Artikel 4: Pivot-Performance mit Joins

Eliminierung von Störern

Wenn in der Tabelle weitere Spalten enthalten sind, dann kann das mit PIVOT zu unerwarteten Ergebnissen führen. Wenn die Tabelle OpenSchema bspw. auch noch die Spalte „Stoerer“ enthält:

Wenn man nun einfach das PIVOT aus dem letzten Artikel einfach verwendet, dann führt das zu komischen Ergebnissen.

SELECT ID,
attr1 as Typ,
attr2 as Datum,
attr3 as Anzahl,
attr4 as Dings,
attr5 as Bums
FROM OpenSchema
PIVOT (
MAX("Value")
FOR Attribute IN ("attr1", "attr2", "attr3", "attr4", "attr5")
) AS pvt
ORDER BY ID

Das liegt an der impliziten Gruppierung aller Felder aus der Basistabelle. Es kann also passieren, dass durch eine unbedachte Tabellenerweiterung einige SELECTs ein völlig anderes Ergebnis liefern. Das ist nicht im Geiste von SQL, ist unangenehm und soll vermieden werden. Daher rate ich dazu PIVOT-Statements immer so zu formulieren, dass sie nicht auf der Basistabelle arbeiten, sondern einer Derived-Table:

SELECT ID,
attr1 as Typ,
attr2 as Datum,
attr3 as Anzahl,
attr4 as Dings,
attr5 as Bums
FROM (
SELECT ID, Attribute, Value
FROM OpenSchema) as dt
PIVOT (
MAX("Value")
FOR Attribute IN ("attr1", "attr2", "attr3", "attr4", "attr5")
) AS pvt
ORDER BY ID

Das liefert nicht nur in diesem Fall das erwartete Ergebnis, sondern auch nach weiteren Tabellenänderungen. Der Zugriffsplan ist entspricht dem ersten Artikel.

Wer lange genug im Geschäft ist, der kennt noch die guten, alten Gummi-Tasten der ersten Mini-Computer. Ich denke da an den ZX-Sinclair mit seiner Folientastatur und Ähnliche deren Name mir gerade nicht einfällt. Wer jetzt eine ganz besonders robuste oder einfach nur flexible Tastatur benötigt, z.B. für den "Outdoor"-Einsatz, der findet diese Gummi-Matte bestimmt richtig gut.

Details bei Gizoo (13 Dollar)

Nachdem ich neulich schon auf die von IBM gesponserte Studie hinwies, die erbrachte, dass Computerspiele gut für die Karriere sind, geht die Studie von Lawrence Kutner und Cheryl K. Olson noch einen Schritt weiter. Die Studie "Grand Theft Childhood: The Surprising Truth About Violent Video Games and What Parents Can Do" fragt kritisch nach, ob Kinder vielleicht sogar Videospiele brauchen, um sich sozialkompetent zu entwickeln.

Das klingt auf den ersten Blick völlig absurd. Warum sollte Kindern, die nicht Computerspiele machen dürfen, etwas fehlen? Meiner Ansicht nach muss man bedenken, das es sich um eine amerikanische Studie handelt. Früher spielten Kinder immer in Rudeln, Einzelkinder gab es nur sehr selten. Sie waren nicht beliebt und hatten es in der Gruppe schwer. Sogar in Deutschland überwiegen mittlerweile die Einzelkinder, in den USA kommen sie zudem häufig aus gescheiterten Familien. Die Kinder dort spielen häufig nicht mehr mit anderen zusamen, sondern eher alleine zuhause für sich. In dieser -eigentlich unnatürlichen – Situation bieten Online-Computerspiele eine Möglichkeit das soziale Miteinander wenigstens ersatzweise zu üben. OK, da kommt jetzt meine persönliche Meinung ziemlich deutlich raus:

• Soziale Kompetenzen lernen Kinder am besten im Rudel. Kleine Egoisten mag ich nicht.
• Wenn also keine Kinder in der Nähe wohnen, dann sollte man sich für seine Kids nach Schmuddelgruppen umsehen, damit meine ich Gruppen in denen sie unbeschwert Kinder sein dürfen, z.B. Pfadfinder oder sowas.
• Wenn das nicht geht, dann kann das maßvolle Computerspielen einen gewissen Ersatz bieten.

Lesenswert ist das (leider sehr kurze) Interview von Olsen mit dem Spiegel allemal.

Als ich mit dem PIVOT-Konstrukt arbeitete und performante Ergebnisse haben wollte, muss ich erst verstehen, wie es intern arbeitet. Mit ein paar Kniffen kann man dann die Ausführung erheblich beschleunigen. Diese Serien soll meine Erfahrungen weitergeben und bei Verstehen der Zusammenhänge helfen. Der Quellcode zu diesem Artikel steht übrigens hier.

• Artikel 1: Wie wird Pivot abgearbeitet?
• Artikel 2: Störer beim PIVOT eliminieren
• Artikel 3: Pivot-Performance
• Artikel 4: Pivot-Performance mit Joins

Wie wird PIVOT ausgeführt?

Angenommen die Tabelle „OpenSchema“ besteht aus drei Spalten: einer ID, einem Attributbezeichner und einer Wert-Spalte. Das sähe etwa so aus:

Um die Ergebnisse dieser "Kennziffer-Wert-Tabelle" in "relationaler" Form zu präsentieren, müssen die Ergebnisse pivotiert werden:

Vor dem SQL-Server-2005 war dazu ein recht kompliziert aussehendes Konstrukt notwendig: Man musste pro Ergebnis eine Aggregatfunktion auswerten, dass intern mittels CASE nur die gewünschten Ergebnisse auswertet:

SELECT ID,
MAX(CASE WHEN "Attribute" = 'attr1' THEN "VALUE" END) as "Typ",
MAX(CASE WHEN "Attribute" = 'attr2' THEN "VALUE" END) as "Datum",
MAX(CASE WHEN "Attribute" = 'attr3' THEN "VALUE" END) as "Anzahl",
MAX(CASE WHEN "Attribute" = 'attr4' THEN "VALUE" END) as "Dings",
MAX(CASE WHEN "Attribute" = 'attr5' THEN "VALUE" END) as "Bums"
FROM OpenSchema
GROUP by ID
ORDER BY ID

In dieser Form stellten es Rozenshtein, Abranovich und Birger schon 1992 im "SQL Server Forum 12/1992" vor. Damals gab es nur das CASE-Konstrukt noch nicht, deswegen verwendeten sie verschiedene Tricks, um ein CASE auf mathematische Weise zu erreichen. Der Vorteil bei dieser alten Schreibweise ist, dass man die Gruppierung explizit angeben kann.

Pivotieren mit Pivot

Im SQL-Server-2005 führte Microsoft bekanntlich das PIVOT-Konstrukt ein, um das Ganze zu vereinfachen. Im PIVOT-Statement wird die Gruppierung hingegen implizit durchgeführt: Es wird nach allen Spalten der Tabelle gruppiert, die nicht in den Klammern des PIVOT genannt werden. Unten werden "Value" und "Attribute" genannt, also wird nach allen verbleibenden gruppiert: hier nur "ID".

SELECT ID,
attr1 as Typ,
attr2 as Datum,
attr3 as Anzahl,
attr4 as Dings,
attr5 as Bums
FROM OpenSchema
PIVOT (
MAX("Value")
FOR Attribute IN ("attr1", "attr2", "attr3", "attr4", "attr5")
) AS pvt
ORDER BY ID

Das PIVOT-Konstrukt ist genauso implementiert, wie es bereits oben beschrieben wurde. Das schließe ich daraus, dass in beiden Fällen der gleiche Zugriffsplan verwendet wird:

Natürlich passiert es öfters, dass der Optimizer einen semantisch äquivalenten Zugriffsplan verwendet, wenn er effizienter ist. Das ist hier jedoch unwahrscheinlich.
Die "ältere" Form hilft mir zu verstehen, wann ein PIVOT schnell ausgeführt werden kann und wann er einfach langsam sein muss. Sie hat zudem den Vorteil, dass man pro Spalte entscheiden kann, welche Aggregatfunktion verwendet werden soll. Beim neuen PIVOT wird für alle die gleiche genutzt.

Georg machte mich heute auf die netten Werbebilder bei jobsintown.de aufmerksam. Besonders nett finde ich ich die Berufe Softeis-Hersteller und Kiddie-Rider-Antreiber…