Rushmore-Optimierung

von Rainer Becker

Visual FoxPro besitzt einen Query-Optimizer (wie bei SQL-Server-Datenbanken) mit einem patentierter Algorithmus, welcher nach dem Mount Rushmore (Präsidentenhügel in den USA) benannt wurde. Dieser Optimizer optimiert alle Befehle mit WHERE- oder FOR-Klausel. Die Rushmore-Optimierung von Visual FoxPro ist aber leider ein sehr sensibles Werkzeug, bei dem diverse Punkte zu beachten sind, damit er auch brav seine Dienste verrichtet. Nur allzuleicht kann die gewünschte Performance-Steigerung durch eine Kleinigkeit verloren gehen. Und leider gerät diese Tatsache immer wieder in Vergessenheit. Deshalb hier mal wieder eine Checkliste für die richtige Ansteuerung mit erweiterten Kommentaren zu einigen Detailbereichen.

Indizierung in kompakter Mehrfachindexdatei

Die wichtigsten Felder werden mit einem normalen Index versehen, denn ohne Index ist per se keine Optimierung möglich. Indizes liegen natürlich ohnehin für alle Primärschlüssel- und Fremdschlüsselfelder vor, aber benötigt werden auch Indizes auf Suchfelder. Es sollten jetzt natürlich nicht einfach sämtliche Datenbankfelder indiziert werden (sogenannte Invertierung), sondern nur die Felder sollten einen Index erhalten, die bei FOR- und WHERE-Klauseln auch tatsächlich angesprochen werden, denn jeder Index kostet Verarbeitungszeit bei Neuanlage oder Änderung von Datensätzen. Alle diese Indizes werden in einer kompakten CDX-Datei (Mehrfachindexdatei) gespeichert und nicht als Einzelindizes (IDX), um die Anzahl der benötigten Filehandles niedrig zu halten.

Übereinstimmung von Indexdefinition und Suchbegriff

Bei der Abfrage auf Datensätze (z.B. mit LOCATE FOR) ist darauf zu achten, daß der Aufbau des Suchbegriffs mit der Indexdefinition völlig übereinstimmt. Bei einem zusammengesetzten Index "INDEX ON PLZ+ORT" muß also ein LOCATE FOR PLZ+ORT="61476Kronberg" gemacht werden wohingegeben bei Einzelnindizes wie "INDEX ON PLZ" und "INDEX ON ORT" stattdessen mit LOCATE FOR PLZ="61476" AND ORT="Kronberg" gesucht werden muß. Bei einem Index mit Funktionen "INDEX ON DTOC(DATUM)" muß also ein "LOCATE FOR DTOC(DATUM) = [29.10.1964]" verwendet werden und ein "LOCATE FOR DATUM = {29.10.1964}" wäre nicht optimierbar. Wenn Suchdefinition (links vom Gleichheitszeichen, nicht Suchbegriff!) und Indexdefinition nicht übereinstimmen, kann der Optimizer den passenden Index also nicht erkennen.

Keine Negierung

Der Optimizer kann Indizes mit Negierungen grundsätzlich nicht erkennen oder verwenden. Sofern also eine Defintion "INDEX ON NOT " vorliegt, muß für den Optimizer ein zusätzlicher Index ohne "NOT" angelegt werden, damit auf das entsprechende Feld optimiert zugegriffen werden kann.

Keine Einschränkungen

Der Optimizer kann Indizes mit Einschränkungen grundsätzlich nicht erkennen oder verwenden. Sofern also eine Defintion "INDEX ON FELD FOR" vorliegt, muß für den Optimizer ein zusätzlicher Index ohne FOR-Klausel angelegt werden, damit auf das entsprechende Feld optimiert zugegriffen werden kann. Häufig tritt dieses Problem bei Primärschlüsseln auf, bei denen man mit Hilfe der Einschränkung "FOR NOT DELETED()" die Wiederverwendung von Schlüsselwerten ermöglichen möchte. In einem solchen Fall muß dann einfach ein zweiter identischer Schlüssel ohne FOR-Klausel als "Normal" statt "Primary" oder "Candidate" angelegt werden.

Keine variablen Längen

Es dürfen kein Funktionen in einzelnen oder zusammengesetzten Feldern verwendet werden, die eine variable Länge erzeugen wie z.B. "INDEX ON ALLTRIM(FELD1)+ALLTRIM(FELD2)". Diese Definitionen können vom Queryoptimizer nicht verwendet werden (selbst wenn man nach Alltrim(Feld) = "Wert" sucht) und sparen auch kaum Platz, da der Index ohnehin komprimiert ist (rechtsbündige Leerzeichen werden also ohnehin nicht gespeichert).

Keine Reihenfolge

Sobald eine Reihenfolge auf der Tabelle vorgegeben ist, ist der Optimizer gezwungen, in dieser Reihenfolge die Datenbank zu durchsuchen statt auf seine interne Bitmap wahlfrei zugreifen zu können. Deshalb sollte man bei schnellen Suchen keine Order setzen (bei Filter wirkt dieses sogar noch verlangsamend) sondern diese z.B. durch ein SET ORDER TO entfernen. Bei SQL-Abfragen ist das zumindest temporäre Entfernen der Ordner nicht notwendig, da die Ursprungstabelle ohnehin intern mit einem USE AGAIN geöffnet wird.

Keine Befehlsklausel WHILE

Die Befehlsklausel WHILE führt zu dem gleichen Problem wie das Setzen einer Reihenfolge - siehe dort - und schaltet die Optimierung generell aus. Stattdessen sollte ein SCAN FOR-verwendet werden, welches den Bearbeitungsbereich ebenfalls eingrenzt. Dies heißt nicht, daß nicht in manchen Fällen dennoch ein SEEK und ein WHILE für einen bestimmten Einsatzzweck schneller sein könnte

Löschkennzeichen indizieren

Das Setzen von SET DELETED ON erstellt intern einen Filter auf das Löschkennzeichen der Datensätze. Solange SET DELETE auf ON steht und kein "INDEX ON DELETED() TAG DELETED" erstellt wurde (wobei der tatsächliche Tagname unerheblich ist), wird die Abfrage immer als nur teilweise optimiert bezeichnet werden. Dies ist beim Feststellen, ob eine Abfrage nun tatsächlich optimiert ist, stark hinderlich. Desweiteren ist bei größeren gefundenen Datenmengen ein leichter Zeitunterschied ohne diesen Index feststellbar, da alle gefundenen Sätze nochmals auf das Gelöscht-Flag geprüft werden müssen.

Keine Sortiersequenz GENERAL

Die Sortiersequenz GENERAL wird zwar als Default vorgeschlagen, sollte aber wegen der dadurch entstehenden Vergleichsproblematik (bei COLLATE=GENERAL werden in allen Vergleichen Groß- und Kleinbuchstaben nicht mehr unterschieden!) und dem erhöhten Platzbedarf (Indexdatei doppelt so groß weil zwei Byte pro Zeichen gespeichert werden) nicht verwendet werden. Die Sortiersequenz GENERAL verursacht außerdem Suchfehler bei den neuen Feldarten (Integer und Double). Stattdessen kann die Sortiersequenz UNIQWT verwendet werden, die ebenfalls Umlaute erwartungsgemäß einsortiert. Ggf. kann auch die Sortiersequenz MACHINE Verwendung finden, wenn es nicht auf die korrekte Umlautsortierung sondern nur auf Geschwindigkeit ankommt. Dabei bitte beachten, daß auch bei einem REINDEX die Sortiersequenz eines Index nicht geändert wird. Wenn ein Index erstellt wurde übernimmt er die zu diesem Zeitpunkt eingestellte Sortiersequenz. Wenn dies die falsche Sortiersequenz gewesen sein sollte, muß man die Indexdefinition löschen (CLEAR TAG) und nach einem SET COLLATE den Index wieder mit INDEX ON neu erstellen. Überprüfen kann man die Sortierfolge eines Index ansonsten mit der Funktion IDXCOLLATE().

Codepage-Übereinstimmung

Die eingebaute Codepagekonvertierung benötigt natürlich Rechenzeit. Deshalb sollte sichergestellt sein, daß alle Tabellen mit der CODEPAGE = 1252 markiert sind. Falls Tabellen abweichen kann man diese mit EXCL Öffnen, mit CPDBF() prüfen und mit DO CPZERO.PRG (Hilfsprogramm von Visual FoxPro) zurücksetzen und dann die korrekte Codepage eintragen. Dabei aber darauf achten, daß bestehende Daten vorher gesichert werden, den Umlaute werden durch dieses Vorgehen nicht korrigiert (von ASCI nach ANSI umgesetzt)!

Kein exakter Vergleich

Ein exakter Vergleich macht eine Optimierung unmöglich. Deshalb sollte mit den Einstellungen SET EXACT OFF und SET ANSI OFF gearbeitet werden, die in jeder Private Datasession eigentlich schon im BeforeOpenTables-Ereignis des Dataenvironments gesetzt werden müßten (oder man lädt Views mit NODATAONLOAD). Dabei muß man allerdings darauf achten, das in Visual FoxPro zwar Code immer von links nach rechts abgearbeitet wird, aber bei Vergleichen dieser Art nur auf die Länge des rechten Ausdrucks verglichen wird und sofern der linke Ausdruck auf dieser Länge gleich ist, ist der Vergleich erfolgreich. Dies kann ein unerwünschter Effekt sein, denn ein Vergleich "AA" = "A" liefert ein .T. zurück (unter der Collatesequenz "General" wäre sogar "aa" = "A" identisch). Deshalb sollte zumindest für Schlüsselfelder gelten, daß diese immer auf die gesamte Länge von links aufgefüllt werden. Falls doch ein exakter Vergleich benötigt wird, kann man ein doppeltes Gleichheitszeichen verwenden (A == B), was innerhalb der Anwendung übrigens deutlich schneller als ein normaler Vergleich ist.

Sonstige Hinweise

Da nur Befehle mit FOR- oder WHERE-Klausel optimierbar sind, sollten alle Konstruktionen wie "DO WHILE NOT EOF, SKIP, ENDDO" durch "SCAN FOR, ENDSCAN" ersetzt werden. Um Zeilen und Abprüfungen zu sparen kann man bei der Gelegenheit dann gleich auch noch solche Klassiker wie "SEEK + IF FOUND" durch IF SEEK() ersetzt.

Sofern nur die Existenz eines Datensatzes festgestellt aber nicht in der Tabelle positioniert werden soll, kann man stattdessen ein "KEYMATCH" (VFP 5.0) bzw. ein "INDEXSEEK" (VFP 6.0) verwenden. Dabei muß man dann allerdings unbedingt darauf achten, daß diese Funktionen keine Rücksicht auf das Löschkennzeichen nehmen - also auch gelöschte Datensätze finden unabhängig von der Einstellung von SET DELETED. Für solche Fälle sollte man dann doch wieder einen Index mit einer Einschränkung "FOR NOT DELETED()" verwenden, siehe auch Abschnitt 4.

Mit Hilfe der SYS-Funktion 3054 können Sie sich ansonsten die erreichte Optimierungsstufe ausgeben lassen. Die Funktion unterscheidet zwischen (Parameter=1) Optimierung der Abfrage an sich und (Parameter=11) der Optimierung der Verknüpfungen (Joins/Relationen).