Performance und GUIDs

In einem Kunden-Projekt sollte ich die Performance der SQL-Zugriffe einer .NET-Applikation verbessern.

Hier beschreibe ich die Ergebnisse, da sie sich auch auf andere Szenarien verallgemeinern lassen.

Die erste Veränderung war, keine GUIDs als clustered primary keys zu verwenden. Stattdessen setzen wir nun ints (mit Identity) ein. Dies führt zu einer immensen Beschleunigung bei den INSERTs in die Datenbank. Dies ist sehr gut nachvollziehbar, da nun die INSERTs immer am Ende der Tabelle statttfinden, so dass kein zeitaufwändiges Umorganisieren der Seiten innerhalb der Tabelle notwendig wird.

Leider ging es aber in meiner Aufgabe um die SELECT-Performance und nicht um die INSERT-Performance. Aber auch diese verbesserte sich durch die Verwendung der integer-Werte deutlich (Abfragezeit ungefähr halbiert). Als Test verwendete ich die Abfrage von 500 Datensätzen, die ich zu Beginn zufällig ausgewählt hatte. Vor jeder Abfrage wurde natürlich der Cache geleert 🙂 Die Steigerung lässt sich dadurch erklären, dass alle Indizes nun vom Platzbedarf viel kleiner wurden (1 int = 4 byte, 1 guid = 16 byte –> ca. 4x so viele Daten gehen auf eine Index-Page [natürlich abhängig von den weiteren Feldern des Index]). Außerdem ist ein Zähler besser verteilt als eine Guid.

Als nächste Verbesserung verwendete ich in m:n-Tabellen als clustered primary key nicht einen Zähler, sondern einen zusammengesetzten Schlüssel aus den beiden referenzierten Tabellen (+ ein weiteres Feld, um die Eindeutigkeit sicherzustellen). Dabei verwendete ich als erstes Feld das Feld der beiden, das in den meisten Abfragen bekannt ist. (Auf der umgekehrten Reihenfolge lag natürlich auch ein Index). Dadurch muss beim Standard-Zugriff nicht mehr über einen non-clustered Index zugegriffen werden, wodurch ein Zugriff eingespart wird. Dies brachte eine weitere Halbierung der Zugriffszeit.

ALs letztes gab es spezielle Szenarien, in denen nach Texten gesucht werden musste – ein Beispiel: Man möchte alle Aufträge ermitteln, die in einer Position einen bestimmten Positionsfreitext enthalten. Dann wird folgendes SQL-Statement abgesetzt:

SELECT * FROM Auftrag a INNER JOIN Auftragsposition pos on pos.AuftragID = a.AuftragID WHERE pos.Positionsfreitext like ‚Test%‘

Natürlich war auf der Auftragspositions-Tabelle ein Index auf Positionsfreitext. Ich erweiterte diesen Index um die AuftragID. Dadurch kann der Join direkt über den Index abgewickelt werden und ein Zugriff auf die Tabelle wird eingespart, was für diesen Spezialfall ebenfalls eine deutliche Performance-Steigerung einbrachte.

SQL Server: zufällige Auswahl von n Zeilen einer Tabelle

Bei einer Aufgabe zur Performance-Steigerung einer SQL Server 2008-Applikation (über die ich später berichten werde), war die erste Aufgabe, ein Test-Szenario aufzubauen, anhand dessen die Abfragezeiten verglichen werden konnten.

Der erste Schritt war dazu die Auswahl 100 beliebiger Sätze aus einer Tabelle. Dabei zeigte mir ein Kollege folgende super einfache Möglichkeit:

select top 100 * from sysobjects order by newid()

[Natürlich muss sysobjects durch die entsprechende Applikations-Tabelle ersetzt werden 🙂 ]

Analysis Services: Zahlen-Formatierung mit Texten (z.B. „min“)

Im Analysis Services kann man natürlich die Formatierung der Measures und berechneten Measures vielfältig gestalten. Die Möglichkeiten gehen aber über die Anzahl der Nachkommastellen hinaus.

Im Beispiel will ich ein Measure, das Zeiten beinhaltet, so formatieren, dass die Zahlen als 17,5 min dargestellt werden. Dadurch ist dem Benutzer klar, dass es sich um Dauer in Minuten handelt.

Bei Measures gibt man als Formatstring an: #,##0.00 min oder #,##0.00″ min“

Bei berechneten Measures gibt man als Formatstring an: „#,##0.00 min“ oder „#,##0.00″“ min“““

[Wegen der Anführungszeichen muss man bei den berechneten Measures aufpassen, da bei einfachen Anführungszeichen ein Syntax Fehler erscheint. Aber wie die Beispiele zeigen, kann man die Anführungszeichen um „min“ herum auch weglassen]

Das Ergebnis in Excel 2007:

Formatierung mit Minuten in Excel 2007

Wie man sieht, stimmt die Formatierung. Und es ist wirklich nur eine Formatierung und kein String, so dass in Excel mit der Zahl auch weiter gerechnet werden könnte.

Kalenderwoche im Analysis Services

Ich hatte ja gestern in meinem Blog geschrieben, wie man die deutsche KW im SQL Server berechnen kann. Wenn man darauf basierend eine Datumsdimension aufbaut, bieten sich natürlich zwei kanonische Hierarchien an:

  • Jahr > Quartal > Monat > Tag
  • Jahr > KW > Tag

Allerdings muss man dabei beachten, dass nur die erste eine echte Hierarchie ist. Die KW-Hierarchie ist nicht echt, da zwei Tage aus der selben KW in unterschiedlichen Jahren liegen können (z.B. 31.12.2008 und 1.1.2009 liegen beide in der KW 2009/01).

Deswegen darf man nicht einstellen, dass eine direkte Beziehung zwischen KW und Jahr besteht:

Also statt

Dimension Datum falsche Attributbeziehung

muss es so aussehen (man beachte die Attributbeziehungen der KW):

Dimension Datum KW richtige Attributbeziehungen

Deswegen kann man natürlich trotzdem beide Hierarchien angeben:

Gierarchien der Datumsdimension

(Das gelbe Dreieck gibt an, dass die Hierarchie nicht echt ist und deshalb nicht für Aggregationen verwendet werden kann)

Eine andere Alternative wäre, dass man unterschiedliche IDs und Namen für die KWs spendiert, die in einem anderen Jahr liegen, also beispielsweise:

  • 31.12.2008 liegt in der KW 2009 / 01 (in 2008)
  • 1.1.2009 liegt in der KW 2009 / 01

Damit wäre die KW-Hierarchie auch eine echte Hierarchie. Allerdings kann man dann die KW 2009 / 01 nicht mehr (so einfach) über Jahresgrenzen hinweg auswerten.

Berechnung der deutschen Kalenderwoche in SQL

Die datepart-Funktion des SQL-Servers liefert eine Woche zurück, wenn man wk als Parameter wählt. Dies ist allerdings nicht die Kalenderwoche. Die deutsche Kalenderwoche funktioniert nämlich so, dass die erste Woche des Jahres mit mindestens 4 Tagen in diesem Jahr die KW 1 dieses Jahres ist. Somit kann der 1.1. eines Jahres noch in der letzten KW des letzten Jahres liegen (nicht so im neuen Jahr 2009 – aber bis 2010 wollte ich nicht mit diesem Blog-Eintrag warten 🙂 )

Offensichtlich hängt die Berechnung davon ab, mit welchem Wochentag die Woche beginnt – bei uns in Deutschland mit dem Montag. Der SQL-Server verfügt über eine Einstellung @@DateFirst, die genau das angibt. Mit SET DATEFIRST 1 kann angegeben werden, dass der Monatg der 1. Tag der Woche ist. Diese Anweisung ist gültig für die Session.

In meinen hier beigefügten Skripten habe ich mich unabhängig von den Einstellungen mit DATEFIRST gemacht, so dass sie unabhängig von allen Einstellungen funktionieren und immer die deutsche Kalenderwoche zurückgeben:

select convert(nvarchar(10), getdate(), 104) Tag, dbo.getKW_Woche(getdate()) KW_Woche, dbo.getKW_Jahr(getdate()) KW_Jahr

liefert als Ergebnis

Tag KW_Woche KW_Jahr
01.01.2009 1 2009

Das bedeutet, dass der heutige Tag in der KW 1/2009 liegt, wie übrigens der 31.12.2008 auch.

In diesem Sinne, ein gutes, gesundes und erfolgreiches Neues Jahr

Nettes Projekt: Im InfoView von BO XI R2 die Prompts anpassen

MultiBase hat einen Kunden, der schon seit langem mit Business Objects arbeitet. Da es ein weltweit tätiges Unternehmen ist, wollte der Kunde die Namen der Berichte, Kategorien und Prompts in mehreren Sprachen anzeigen. Außerdem sollten die Prompts schöner + benutzerfreundlicher gestaltet werden unter Verwendung von Listen, Komboboxen, Checkboxen, abhängigen Prompts (ausgewähltes Land bestimmt den Inhalt der Bundesland-Kombobox) und allem, was das Herz begehrt.

In einem früheren Projekt hatten wir bereits diese Anforderung in BO 6.5 umgesetzt. Dazu hatten wir die ASP-WebI-Version verwendet und die ASP-Seiten modifiziert.

Jetzt beim Umstieg auf XI R2 sollten dieselben Anforderungen auch dort umgesetzt werden. Deswegen verwendeten wir dort das .NET InfoView. Da dazu alle Sourcen mitgeliefert werden, war es möglich, sich an einer Stelle in den bestehenden Code zu hängen und über eine Art Weiche (wenn in unserer Meta-Datenbank zu diesem Bericht Einstellungen gesetzt waren) auf eigenen aspx-Code umzuleiten. Dort erstellen wir in HTML die Prompts wie in einer Meta-Datenbank definiert. Letzlich definiert „unsere“ Seite dann die gleichen hidden fields, die auch die Originalseite definiert und somit können wir dann wieder in den Standard-Code zurückkehren.

Dieses Projekt ist nicht typisch für meine Projekte (es hat ja auch gar nichts mit Microsoft zu tun), aber es war trotzdem recht nett, weil es gelang, (trotz sehr schlechter Dokumentation) eine sehr schöne Erweiterung für InfoView zu erstellen.

Dabei musste man eine ausgewogene Lösung finden, die folgenden Anforderungen genügt:

  • Umsetzung der fachlichen Anforderung (Mehrsprachigkeit, Prompts)
  • Geringe Kosten: Kein eigenes Frontend bauen, sondern nur an wenigen Stellen das vorhandene verbessern
  • Nur minimale Veränderungen an dem bestehenden BO-Code, da jede Veränderung Probleme nach sich zieht (Beim Einspielen von Service Packs etc.).

Jetzt sind wir richtig stolz, was wir aus BO XI gezaubert haben.

Ich war in diesem Projekt für die technische Konzeption und für die Erarbeitung der grundsätzlichen Logik, wie wir uns an welchen Stellen in InfoView einhängen können.

SQL Server: Versteckte Einstellung für Copy & Paste mit Spaltenüberschriften

Es kommt ziemlich oft vor, dass man das Ergebnis einer SQL-Abfrage (aus dem SQL Server Management Studio) in Excel weiterbearbeiten will (z.B. Filtern, Sortieren etc.). Dabei ist extrem störend, dass die Spaltenüberschriften nicht mit kopiert werden. Glücklicherweise kann man das unter Tools > Options einstellen:

Einstellungen, um die Spaltenüberschriften bei Copy&Paste mitzunehmen

PS: Danke, Stefan, für den Tip.

Konfigurationsdateien im SSIS – Nutzen und Fallen

In SSIS-Paketen können alle möglichen Einstellungen in Konfigurationsdateien ausgelagert werden. Insbesondere können damit

  • die Eigenschaften von Verbindungen wie ConnectionString, User, Passwort
  • oder Variablen wie Name des zu durchsuchenden Datei-Ordners oder ähnliches

extern konfiguriert werden – ganz analog zur web.config in ASP.NET oder app.config in Windows-Programmen. Theoretisch ist es möglich, viel mehr Attribute als oben genannte in der Konfigurationsdatei zu pflegen – aus Übersichtlichkeitsgründen ist das aber nicht empfohlen.

Dies hat den Sinn, dass das Paket in der Entwicklungs-, Test- und Produktionsumgebung immer gleich ist und nur die Konfigurationsdateien bei einer Installation angepasst werden. Dadurch wird sichergestellt, dass wirklich das getestete Paket in Produktion geht und nicht aus Versehen beim Öffnen im Visual Studio irgendwelche Einstellungen angepasst wurden. Dies ist in meinen Augen – neben vielen anderen Features – ein wesentlicher Vorteil von SSIS gegenüber seinem Vorgänger DTS.

Konfigurationsdateien werden erstellt über SSIS > Package Configurations… :

Erstellen von Konfigurationsdateien

In der Regel werden XML-Konfigurationsdateien verwendet. Dort kann man den Ort der Konfigurationsdatei und die enthaltenen Attribute angeben. Dadurch erzeugt das Visual Studio die entsprechende Konfigurationsdatei. In dieser Konfigurationsdatei sind alle Werte der Attribute gespeichert, so wie sie derzeit (im Verbindungsmanager, Variablenfenster, etc.) definiert sind. Einzige Ausnahme: Falls im Verbindungsmanager ein Passwort verwendet wird, wird in der Konfigurationsdatei zwar dieses Attribut angelegt, aber nicht der Wert übernommen. (erste kleine Falle)

Nun ist die Idee, bei der Installation in der Produktiv-Umgebung das dtsx-Paket unverändert zu lassen und nur die Konfigurationsdatei anzupassen. Beim Starten des Pakets kann man dann die zu verwendende Konfigurationsdatei angeben (z.B. im SQL Server Agent oder beim direkten Aufruf von DTExec) und diese wird beim Ausführen des Pakets verwendet.

Beispiel für die Verwendung einer Konfigurationsdatei im SQL Server Agent

Allerdings gibt es ein paar Fallen, die man umschiffen sollte:

Falle 1: Änderungen an Variablen während der Entwicklung gehen beim Starten des Pakets im Visual Studio verloren:

Wenn man im Visual Studio Eigenschaften ändert, die bereits in die Konfigurationsdatei exportiert wurden, ist diese Änderung wirkungslos. Beim Start des Pakets wird ja die Konfigurationsdatei geladen und dessen Wert verwendet. Eine automatische Aktualisierung der Konfigurationsdatei bei Änderungen im Visual Studio findet (glücklicherweise) nicht statt.

Falle 2: Das Paket versucht beim Start die im Paket definierte Konfigurationsdatei zu laden

Im produktiven Umfeld liegt die Konfigurationsdatei wahrscheinlich nicht am selben Ort (im Dateisystem) wie in der Entwicklungsumgebung. Wenn man nun das Paket (wie unter Idee beschrieben) in der Produktion unter Angabe der Produktions-Konfigurationsdatei startet, so versucht das Paket zunächst die Datei zu laden, deren Pfad im Paket (s. obiger Screenshot) definiert ist. Diese Datei wird in der Regel nicht gefunden werden. Dies wird als Fehler protokolliert – das Paket bricht aber nicht ab. Danach wird die beim Aufruf mitgegebene Konfigurationsdatei geladen. Somit läuft das Paket sauber durch – allerdings ist ein Fehler protokolliert, der Verwirrung stiften kann. Deswegen ist es am sinnvollsten, den Haken bei „Enable package configurations“ (s. Screenshot) wieder zu entfernen. Durch diesen Haken hat man sich somit nur auf einfache Art und Weise eine Konfigurationsdatei erstellen lassen (anstelle sie komplett selbst manuell zu erstellen).

Nun könnte man sich wundern, ob dann trotzdem die beim Aufruf mitgegebene Konfigurationsdatei geladen wird. Die Bedeutung des Hakens ist in meinen Augen nämlich etwas verwirrend. Es besagt nämlich nicht „Dieses Paket hat eine Kofnigurationsdatei mit den dort eingestellten Attributen“. Egal ob der Haken gesetzt ist oder nicht, kann eine Konfigurationsdatei beim Start angegeben werden, die beliebige Attribute verändert, also auch Attribute, die nicht im Visual Studio zum Export in die Konfigurationsdatei markiert worden waren. Der Haken besagt somit, dass „Dieses Paket lädt beim Start automatisch die angegebene Konfigurationsdatei – danach werden die beim Start übergebenen Konfigurationsdateien geladen“.

Dieses Verhalten ist in meinen Augen wenig intuitiv – aber so ist es halt. Dadurch kann man sein eigenes Paket also auch nicht schützen, so dass bestimmte Attribute nicht von außen durch Konfigurationsdateien überschrieben werden können.

Verschlüsselungs-Fehlermeldung beim Ausführen von SSIS-Paketen

Ein gängiges Szenario für das produktive Ausführen von SSIS-Paketen ist die Ausführung im SQL Server Agent.

Für Verwirrung sorgt dabei häufig eine Fehlermeldung, dass der Knoten aufgrund eines Verschlüsselungsfehlers nicht geöffnet werden kann. Falls ein Paket nicht ausgeführt werden konnte, führt das leicht auf eine falsche Fährte.

kryptographischer Fehler

Dieser Fehler ist nämlich nicht Ursache dafür, dass das Paket auf einen Fehler gelaufen ist. Dieser Fehler tritt in folgender Situation auf:

  • Das Paket wird durch einen User geöffnet, der das Paket nicht gespeichert hat.
  • Das Paket enthält eine Verbindung, die ein Passwort gesetzt hat – auch wenn diese Verbindung gar nicht verwendet wird (weil die Verbindungsinformationen z.B. durch eine Konfigurationsdatei o.ä. überschrieben werden.)
  • Die Einstellungen des Pakets (ProtectionLevel) stehen auf „encrypt sensitive with user key“

Dann versucht Integration Services nämlich alle sensitiven Daten – das ist vor allem das Password in einer Verbindung – mit dem aktuellen User zu entschlüsseln – und scheitert und protokolliert den Fehler. Integration Services bricht dann aber nicht die Verarbeitung ab, sondern verwendet dann ein leeres Passwort. In meinem Beispiel-Screenshot war der tatsächliche Fehler eine (bewusst herbeigeführte) Division durch Null. Diesen Fehler sieht man ganz am Ende.

Im produktiven Umfeld ist das im Normalfall kein Problem, da normalerweise Integrierte Sicherheit (also Anmeldung mit dem Windows-User) verwendet wird oder die Verbindungsinformationen (Server, Datenbank und eben auch User und Passwort) extern verwaltet werden (also in einem Config-File oder im SQL Server Agent etc.). Letzteres hat den Grund, dass man Pakete auf dem Produktivsystem ohne Veränderung (d.h. ohne Öffnen im Visual Studio) aus dem Testsystem installieren will.

Somit kann man einfach die Einstellung auf „Do not save sensitive“ stellen. Dann tritt der oben beschriebene Fehler nicht auf und die verwirrende Fehlermeldung in der Task History des SQL Server Agent taucht nicht auf. In meinem Beispiel erkennt man die Division durch 0 jetzt viel besser:

Fehlermeldung Division durch 0 viel leichter erkennbar

 

Um allerdings eine gute Grundlage für die Fehlersuche zu haben, reicht auch die Anzeige im SQL Server Agent nicht aus. Man sollte besser Logging aktivieren – (dazu vielleicht später mehr 🙂 )

SSIS Pakete im SQL Server Agent unter einem eigenen User starten

Zur zeitzgesteuerten Verarbeitung von SSIS-Paketen bietet sich der beim SQL Server 2005 mitgelieferte SQL Server Agent an. Wie man im Screen Shot sieht, bietet der SQL Server Agent an, die SSIS-Pakete unter einem bestimmten User auszuführen. Diese Kombobox („Run as“) ist im Standard allerdings auf den Benutzer-Konto des SQL Server Agent-Dienstes beschränkt:

Als RunAs steht nur das Benutzerkonto des SQL Server Agents zur Verfügung

Hier möchte ich zeigen, wie man diese Kombobox um einen beliebigen User erweitern kann und somit auch ein Paket unter diesem User ausführen kann.

Dazu muss zunächst ein Credential (ein Windows-Konto samt Kennwort) angelegt werden und dieser dann für die Ausführung von SSIS-Paketen freigegeben werden.

Das Credential gibt man im SQL Server Management Studio (wenn man auf den relationalen Datenbank-Server verbunden ist) unter Security > Credentials ein, also z.B.:

Ein Credential wird angelegt

Unter SQL Server Agent > Proxies – also hier:

Eingabe Proxies– muss dieser Credential nun für die Ausführung von SSIS-Paketen definiert werden. Dazu erstellt man einen neuen Proxy unter einem beliebigen Namen, der das neu eingegebene Credential verwendet. Bei den erlaubten Subsystemen setzt man den Haken bei den SSIS-Paketen (und allen weiteren gewünschten Systemen):

Erstellen eines Proxies mit Erlaubnis für SSIS-Pakete

Und schon kann das Paket im SQL Server Agent unter diesem Proxy ausgeführt werden:

Ausführung des Jobs unter dem neu angelegten Proxy

Meine Erfahrungen in der Business Intelligence Welt