{"id":1060,"date":"2008-05-09T18:50:37","date_gmt":"2008-05-09T16:50:37","guid":{"rendered":"http:\/\/www.glorf.it\/blog\/2008\/05\/09\/sql-talk\/performance\/sql-server-2005-pivot-auf-joins"},"modified":"2008-07-16T21:57:40","modified_gmt":"2008-07-16T19:57:40","slug":"sql-server-2005-pivot-auf-joins","status":"publish","type":"post","link":"http:\/\/www.glorf.it\/blog\/2008\/05\/09\/sql-talk\/performance\/sql-server-2005-pivot-auf-joins","title":{"rendered":"SQL-Server-2005: PIVOT auf JOINs"},"content":{"rendered":"<p>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 &quot;SQL-Server-2005: schnelles Pivot?&quot;. Diese Serie soll meine Erfahrungen weitergeben und bei Verstehen der Zusammenh&#228;nge helfen. Mit ein paar Kniffen kann man dann die Ausf&#252;hrung erheblich beschleunigen. Der Quellcode zu diesem letzten Artikel steht &#252;brigens <a href=\"http:\/\/www.glorf.it\/files\/200710_pivot\/08_pivot_join_performance.sql\">hier<\/a>.<\/p>\n<ul>\n<li><a href=\"http:\/\/www.glorf.it\/blog\/2008\/05\/03\/sql-talk\/performance\/sql-server-2005-schnelles-pivot\">Artikel 1: Wie wird Pivot abgearbeitet?<\/a><\/li>\n<li><a href=\"http:\/\/www.glorf.it\/blog\/2008\/05\/05\/sql-talk\/performance\/sql-server-2005-stoerer-beim-pivot-eliminieren\">Artikel 2: St&#246;rer beim PIVOT eliminieren<\/a><\/li>\n<li><a href=\"http:\/\/www.glorf.it\/blog\/2008\/05\/07\/sql-talk\/performance\/sql-server-2005-pivot-und-performance\">Artikel 3: Pivot-Performance<\/a><\/li>\n<li><a href=\"http:\/\/www.glorf.it\/blog\/2008\/05\/09\/sql-talk\/performance\/sql-server-2005-pivot-auf-joins\">Artikel 4: Pivot-Performance mit Joins<\/a><\/li>\n<\/ul>\n<p><strong>Performance von &quot;gejointen&quot; Daten<\/strong><\/p>\n<p>Wenn die Basistabelle des PIVOTs aus einer Derived-Table besteht, die mittels Join die Daten aus mehreren Tabellen sammelt, dann h&#228;ngt es von relativ vielen Faktoren ab, ob das PIVOT schnell ist. Das gleiche gilt nat&#252;rlich auch, wenn der PIVOT auf eine View oder eine Inline-Table-Valued-Function durchgef&#252;hrt wird. <\/p>\n<p>Man kann es aber auf den Nenner bringen: Kann der Optimizer das PIVOT so begrenzen, dass die Gruppierung allein auf den Werten einer Tabelle basiert, die mittels Clustered-Index gelesen werden k&#246;nnen, dann ist es schnell. In den anderen F&#228;llen muss man mit mehr oder weniger starken Performanceeinbu&#223;en rechnen. <\/p>\n<p>Als Beispiel m&#246;chte ich einen relativ g&#228;ngigen Sonderfall darstellen: Das Beispiel des <a href=\"http:\/\/www.glorf.it\/blog\/2008\/05\/03\/sql-talk\/performance\/sql-server-2005-schnelles-pivot\">ersten Artikels<\/a> wird so erweitert, dass die Spalten&#252;berschriften in einer Translation-Table TranTab stehen:<br \/>\n<img src='http:\/\/www.glorf.it\/blog\/wp-content\/uploads\/2008\/05\/translationtable.jpg' alt='Translation-Table' \/><\/p>\n<p>In der Tabelle OpenSchema steht dann der Verweis auf den Namen des Attributes:<br \/>\n<img src='http:\/\/www.glorf.it\/blog\/wp-content\/uploads\/2008\/05\/tabopenschema_mit_translation.jpg' alt='Tabelle Openschema mit &#220;bersetzungstabelle' \/><\/p>\n<p>Um das gew&#252;nschte Ergebnis zu bekommen, muss man PIVOT auf eine Derived-Table ausf&#252;hren:<\/p>\n<p><code lang=\"sql\">SELECT ID, \"Typ\", \"Datum\", \"Anzahl\", \"Dings\", \"Bums\"<br \/>\nFROM \t(SELECT os.ID, tt.Attribute, os.&quot;Value&quot;<br \/>\n\t\tFROM OpenSchema as os<br \/>\n\t\tJOIN TransTab as tt<br \/>\n\t\t\tON os.AttId=tt.AttId) as dt<br \/>\n\tPIVOT<br \/>\n\t(<br \/>\n\t\tMax(&quot;Value&quot;)<br \/>\n\t\tFOR Attribute IN (&quot;Typ&quot;, &quot;Datum&quot;, &quot;Anzahl&quot;, &quot;Dings&quot;, &quot;Bums&quot;)<br \/>\n\t) AS pvt<br \/>\nORDER BY ID<\/code><\/p>\n<p>Die Performance verschlechtert sich dadurch dramatisch. Im Vergleich zum reinen Lesen der Daten fast um Faktor 4. Das liegt am ung&#252;nstigen Zugriffsplan:<br \/>\n<img src='http:\/\/www.glorf.it\/blog\/wp-content\/uploads\/2008\/05\/zugriffsplan_mit_join.jpg' alt='Zugriffsplan' \/><\/p>\n<p>Wenn man einen PIVOT ohne den Join durchf&#252;hrt, dann erh&#228;lt man die gewohnte, gute Performance:<br \/>\n<code lang=\"sql\">SELECT ID, \"1\" as \"Typ\", \"2\" as \"Datum\", \"3\" as \"Anzahl\", \"4\" as \"Dings\", \"5\" as \"Bums\"<br \/>\nFROM \t(SELECT os.ID, os.AttId as Attribute, os.&quot;Value&quot;<br \/>\n\t\tFROM OpenSchema as os ) as dt<br \/>\n\tPIVOT<br \/>\n\t(<br \/>\n\t\tMax(&quot;Value&quot;)<br \/>\n\t\tFOR Attribute IN (&quot;1&quot;, &quot;2&quot;, &quot;3&quot;, &quot;4&quot;, &quot;5&quot;)<br \/>\n\t) AS pvt<br \/>\nORDER BY ID<\/code><\/p>\n<p>Leider muss man dazu die Spaltenamen &quot;hart&quot; codieren. Das ist aber genau das, was man vermeiden wollte als man die Translation-Table einf&#252;hrte. Um auch mit flexiblen Spaltennamen zu einer guten Performance zu kommen, muss man im Batch zun&#228;chst die Spalten ermitteln, das PIVOT-Statement dann dynamisch zusammensetzen und ausf&#252;hren:<\/p>\n<p><code lang=\"sql\">DECLARE @sqlcmd\t\tnvarchar(1000),<br \/>\n\t\t@selectList\tnvarchar(1000),<br \/>\n\t\t@pivotList\tnvarchar(1000);<\/p>\n<p>SELECT<br \/>\n\t&#8211; zun&#228;chst die Spalten aus der SELECT-Liste ermitteln<br \/>\n\t@selectList = STUFF(<br \/>\n\t\t\t(SELECT N&#x0027;, &#x0027;+QUOTENAME(AttId)+N&#x0027; AS &#x0027;<\/p>\n<p>\t\t\t\t\t+QUOTENAME(Attribute) AS [text()]<br \/>\n\t\t\t\tFROM TransTab<br \/>\n\t\t\t\tORDER BY AttId<br \/>\n\t\t\t\tFOR XML PATH('&#x0027;)), 1, 1, N'&#x0027;),<br \/>\n\t&#8211; dann die Liste der Spalten ermitteln<br \/>\n\t@pivotList = STUFF(<br \/>\n\t\t\t(SELECT N&#x0027;, &#x0027;+QUOTENAME(AttId) AS [text()]<br \/>\n\t\t\t\tFROM TransTab<br \/>\n\t\t\t\tOrder BY AttId<br \/>\n\t\t\t\tFOR XML PATH('&#x0027;)), 1, 1, N'&#x0027;);<\/p>\n<p>SET @sqlcmd = N&#x0027;SELECT ID, &#x0027;+@selectList +N&#x0027;<br \/>\n\tFROM (SELECT os.ID, os.AttId as Attribute, os.&quot;Value&quot;<br \/>\n\t\t\t\tFROM OpenSchema as os) as O<br \/>\n\t\tPIVOT (Max(&quot;Value&quot;) FOR Attribute IN (&#x0027;+@pivotList+N&#x0027;)) as P<br \/>\n\tORDER BY Id;&#x0027;<\/p>\n<p>EXEC sp_executesql @sqlcmd;<\/code><\/p>\n<p>Das ausgef&#252;hrte Statement sieht dann so aus:<br \/>\n<code lang=\"sql\">SELECT ID,  [1] AS [Typ], [2] AS [DATUM], [3] AS [Anzahl], [4] AS [Dings], [5] AS [Bums]<br \/>\n\tFROM (SELECT os.ID, os.AttId as Attribute, os.&quot;Value&quot;<br \/>\n\t\t\tFROM OpenSchema as os) as O<br \/>\n\tPIVOT (Max(&quot;Value&quot;) FOR Attribute IN ( [1], [2], [3], [4], [5])) as P<br \/>\n\tORDER BY Id;<br \/>\n<\/code><\/p>\n<p>Die Gesamtperformance dieses Beispiels ist wieder sehr gut.<br \/>\nInsgesamt ergeben sich mit Pivot eine Menge neuer M&#246;glichkeiten. Wenn dabei die Performance wichtig ist, dann muss man sich jedoch genau &#252;berlegen, wie man das Statement auch wirklich schnell ausf&#252;hren kann.<\/p>\n<div class=\"small\">Diese Reihe schrieb ich letzten Oktober als Reaktion auf den Aufruf im SQL-PASS-Newsletter. M&#246;glicherweise erscheint der Artikel noch dort. Ich ver&#246;ffentliche ihn jetzt aber lieber mal bevor er mit dem bald erscheinenden SQL-Server-2008 m&#246;glicherweise veraltet&#8230; \ud83d\ude09<\/div>\n","protected":false},"excerpt":{"rendered":"<p>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 &quot;SQL-Server-2005: schnelles Pivot?&quot;. Diese Serie soll meine Erfahrungen weitergeben und bei Verstehen der Zusammenh&#228;nge helfen. Mit ein paar Kniffen kann man dann die Ausf&#252;hrung erheblich beschleunigen. Der Quellcode zu diesem letzten [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[18],"tags":[930,51,929],"_links":{"self":[{"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/posts\/1060"}],"collection":[{"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/comments?post=1060"}],"version-history":[{"count":2,"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/posts\/1060\/revisions"}],"predecessor-version":[{"id":1217,"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/posts\/1060\/revisions\/1217"}],"wp:attachment":[{"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/media?parent=1060"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/categories?post=1060"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/tags?post=1060"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}