Datenbank Performance und Indexstatistiken

Eine SQL Datenbank braucht nicht nur Indizes auf wichtigen Feldern, damit eine SQL Query performant ablaufen kann, sondern auch eine Information, wie selektiv ein gewisser Index ist, damit vor der Ausführung eines SQL Statements die beste Taktik gewählt werden kann.

So ist zum Beispiel die Selektivität bei einem Boolean-Feld (Ja/Nein) und einer flachen Verteilung 50%. Bei einem Index auf der PLZ einer Adresse ist die viel höher. Fragt man also gleichzeitig ein Boolean-Feld und eine PLZ ab, so sagt die Selektivität dem Query Analyzer, dass er besser zuerst nach der PLZ sucht und erst nachher nach dem Boolean-Feld.

Dafür werden sogenannte Indexstatistiken angelegt, welche Informationen über die Werteverteilung der indizierten Spalten speichern. Indexstatistiken müssen – genau wie die Indizes selbst – regelmässig gewartet werden. Veraltete oder gar nicht erst erstellte Indexstatistiken können dazu führen, dass die dazu gehörenden Indizes gar nicht verwendet werden, was einen messbaren und zum Teil sehr grossen Effekt auf die Performance gewisser SQL Statements hat.

Generell gilt:

• Der Vertec DB Konvert versucht nach der Übertragung der Daten in die neue Datenbank die Indexstatistiken neu zu berechnen. Bei Firebird gelingt das immer, bei SQL Server nicht, siehe dazu den Artikel über den Konvert mit Microsoft SQL Server.
• Bei einem Restore eines Backups berechnen beide Datenbanksysteme die Indexstatistiken neu.

Kundenspezifische Indizes

In Vertec Datenbanken können nach sorgfältiger Analyse und Abwägung kundenspezifische Indizes angelegt werden. Damit Indizes nach einem Datenbank Konvert erhalten bleiben, müssen Sie mit dem Präfix CIX_beginnen.

Kundenspezifische Indizes werden wie folgt erstellt:

CREATE [UNIQUE] INDEX indexname ON tablename (column1, column2, column3)

Die Syntax für Firebird und SQL für die Erstellung einfacher Indizes ist ähnlich, wenn weitere Angaben hinzukommen gibt es jedoch Unterschiede.

• Weitere Informationen zur Erstellung von Indizes bei Firebird Datenbanken finden Sie unter https://firebirdsql.org/refdocs/langrefupd20-create-index.html
• Weitere Informationen zur Erstellung von Indizes bei SQL Server Datenbanken finden Sie unter https://docs.microsoft.com/de-de/sql/t-sql/statements/create-index-transact-sql?view=sql-server-ver15

Beispiel:

CREATE INDEX CIX_INVOICENUMBER ON RECHNUNG (NUMMER);

Das Erstellen von Indizes kann Performance-Verbesserungen bewirken, allerdings gibt es auch Risiken. So kostet das Einfügen oder Updaten eines Records mehr Zeit, wenn auch noch ein Index zusätzlich bearbeitet werden muss, und auch bei einem SELECT führt ein Index nicht immer zu einem besseren Resultat: so kann es sein, dass bei einem Feld mit eigentlich guter Selektivität aber gleichzeitig vielen Werten wie «» oder NULL die Performance sogar schlechter wird. Auch wirkt ein Index nur bei einem = Vergleich, nicht bei einem LIKE.

Vor dem Entscheid, einen neuen Index einzufügen sollte man den Effekt die gesamte Applikation jedenfalls eingehend testen.

Microsoft SQL Server

Der SQL Server berechnet die Indexstatistiken standardmässig selbständig neu, wenn sich eine Tabelle "wesentlich" ändert, also zu mindestens 10%.

Das genügt nicht immer. Wir empfehlen hier, die Indexstatistiken mit einem Maintenance Job regelmässig nachzuführen. Dafür gibt es die Stored Procedure:

EXEC sp_updatestats;

die dabei gescheduled wird oder auch einfach von Hand angestossen werden kann.

Bei Migrationen - also wenn mit einer leeren Vertec Datenbank gestartet und viele Daten reinmigriert werden - berechnet der SQL Server aufgrund der 10% Regel die Indexstatistiken selbständig.

Firebird

Firebird hat keine Methode zur manuellen Neuberechnung der Indexstatistiken wie der Microsoft SQL Server (siehe oben).

Bei einem Vertec Datenbank Konvert werden die Statistiken neu berechnet. Das geschieht jeweils bei den Vertec Updates.

Bei Migrationen jedoch - also wenn mit einer leeren Vertec Datenbank gestartet und viele Daten reinmigriert werden - werden die Statistiken nicht berechnet. In diesem Fall muss die Indexstatistik noch erstellt werden. Es gibt folgende Möglichkeiten:

• Backup - Restore mit gbak: Das gbak (siehe Artikel Backup der Vertec Daten) erstellt am Schluss, nachdem die Daten in der Datenbank sind, die Indizes.
• Im von Vertec mitgelieferten Firebird Administrationsprogramm (Firebird Editor Pro):
  • Die Vertec Firebird Datenbanken enthalten eine Stored Procedure namens SP_VTC_UPDATESTATS.
  • Ein Klick auf den Button mit der Tabelle öffnet den SQL Editor.
  • Der Befehl lautet EXECUTE PROCEDURE SP_VTC_UPDATESTATS und wird durch Klick auf den grünen Pfeil ausgeführt:

    

  • Wichtig: Erst der Commit führt die eigentliche Aktion durch:

    

• Die Prozedur kann auch wie folgt gescheduled werden:

"C:\Program Files\Firebird_5_0\isql.exe" -user sysdba -password xyz localhost:C:\Program Files\Vertec\DB\Vertec.FDB -input C:\Program Files\Vertec\DB\indexstats.txt

Die Datei Indexstats.txt muss am angegebenen Pfad erstellt werden und folgende Zeilen enthalten:

EXECUTE PROCEDURE SP_VTC_UPDATESTATS;
COMMIT;

• Ist die Stored Procedure nicht da, kann sie wie folgt erstellt werden:

set term ^;

execute block
as
declare variable ix varchar(31);
begin
  for select rdb$indices.rdb$index_name
  from rdb$indices
  into ix
  do
  execute statement 'SET STATISTICS INDEX '||ix ;
end^

set term ;^

Der Code kann einfach in den SQL Editor kopiert und wie oben auf den Button Execute und dann auf Commit geklickt werden.