Indizierung von Felder, die in Abfragen verwendet werden

Nach der Analyse der Performance vieler Ninox-Lösungen zeigt sich, dass zahlreiche Abfragen wiederholt ganze Tabellen durchsuchen, obwohl sie nur eine kleine Anzahl von Ergebnissen zurückgeben.

Typisches Muster:

• Große Tabelle (z. B. 20.000+ Datensätze)
• Abfrage wird hunderte oder tausende Male ausgeführt
• Jede Ausführung durchsucht jede einzelne Zeile
   

Dies tritt am häufigsten auf, wenn kein Index auf dem gefilterten Feld vorhanden ist. Es kann aber auch passieren, wenn Abfragen so geschrieben werden, dass die Verwendung eines Indexes verhindert wird. Dies wirkt sich negativ auf die Performance von Skripten aus, die auf solchen Scans basieren, und kann auch die Performance der gesamten Lösung und Cloud-Umgebung beeinträchtigen.

Die Auswirkungen auf die Performance nehmen zu, wenn viele solcher Abfragen in einer Datenbank vorhanden sind, und werden zusätzlich verstärkt, wenn mehrere Benutzer sie gleichzeitig ausführen.

So wenden Sie Indizierung auf ein Feld an

So indizieren Sie ein Feld:

• Öffnen Sie die Tabelle, die Sie abfragen
• Gehen Sie in die Feldeinstellungen
• Aktivieren Sie die Option „Index“

Dies erzeugt eine Lookup-Struktur, mit der die Datenbank Datensätze effizient finden kann.

 

Wann eine Indizierung sinnvoll ist

Eine Indizierung sollte verwendet werden, wenn die folgenden Bedingungen erfüllt sind:

1. Die Tabelle enthält eine erhebliche Anzahl von Datensätzen (Tausende oder mehr).
2. Das Feld wird in WHERE-Bedingungen (Filtern) innerhalb von select-Anweisungen verwendet.
3. Die Abfrage wird häufig ausgeführt (z. B. in Ansichten, Dashboards, Skripten oder Automatisierungen).

 

Beispielsweise ist die Tabelle „Aufgaben“ groß und wird häufig für Dashboards abgefragt. Angenommen, jede Aufgabe besitzt einen eindeutigen Referenzcode, der im Textfeld „Referenz“ gespeichert ist, und ein Suchfeld „Suchreferenz“ enthält den zu suchenden Wert:

let meineSuche := Suchreferenz;
select Aufgaben where Referenz = meineSuche

Eine Indizierung des Feldes „Referenz“ würde die Performance dieser Abfrage verbessern. Ein Textfeld mit Referenzcodes eignet sich gut für eine Indizierung, da die Werte innerhalb der Tabelle unterschiedlich sind. Ein Auswahlfeld enthält dagegen häufig dieselben Werte in vielen Datensätzen, wodurch der Nutzen eines Indexes geringer ausfällt.

Um die Performance von Dashboards in großen Lösungen mit mehreren gleichzeitig arbeitenden Benutzern zusätzlich aufrechtzuerhalten, empfiehlt es sich außerdem, Best Practices wie clientseitige selects zu verwenden. Weitere Informationen dazu finden Sie in unserer Performance-Dokumentation.

 

Wann Indizierung vermieden werden sollte

Indizes sollten nicht standardmäßig gesetzt werden, sondern gezielt nur dann, wenn es sinnvoll ist. Sie benötigen zusätzlichen Speicher und erhöhen den Aufwand bei Schreiboperationen, da neue Datensätze ebenfalls indiziert werden müssen.

In folgenden Fällen sollten Sie keine Indizierung anwenden:

• die Tabelle ist klein
• das Feld wird selten gefiltert
• die Abfrage wird selten ausgeführt
• fast alle Datensätze haben denselben Wert
• es gibt nur eine geringe Wertevielfalt (z. B. Ja/Nein-Felder)

Wie Indizierung die Performance verbessert

Betrachten wir ein Beispiel, bei dem das Feld „Status“ nicht indiziert ist, sowie eine weitere Abfrage basierend auf dem zugewiesenen Benutzer:

select Tasks where Status = 1

oder

select Tasks where 'Erstellt von' = user()

Da kein Index vorhanden ist, wird die Tabelle folgendermaßen abgefragt:

Datensatz 1 laden → Bedingung prüfen
Datensatz 2 laden → Bedingung prüfen
Wiederholung für jeden Datensatz der Tabelle

Bei 20.000 Datensätzen: → 20.000 Auswertungen pro Abfrage
Bei 5.000 Ausführungen: → 100 Millionen Auswertungen
 

Wenn ein Index vorhanden ist, läuft die Abfrage so ab:

• Suche Status 1 „Open“ im Index
• Abrufen der passenden Datensatz-IDs
• Laden nur dieser Datensätze

Dadurch wird direkt zu den Ergebnissen gesprungen und die Ausführungszeit reduziert. Die Anzahl der notwendigen Scans sinkt von potenziell sehr vielen auf eine deutlich überschaubarere Menge.

Voraussetzungen für die Nutzung des Indexes

Ein Index wird nur wirksam, wenn die Abfrage auf eine bestimmte Weise geschrieben ist. Das Erstellen des Indexes allein reicht nicht aus. Alle drei folgenden Bedingungen müssen erfüllt sein, damit der Index verwendet wird:

1. Das indizierte Feld wird direkt verwendet. Es darf nicht in eine Funktion eingebettet sein und darf nicht über ein Formelfeld referenziert werden, das lediglich seinen Wert anzeigt.
2. Der Operator ist einer der folgenden: =, >, >=, <, <=. Kein anderer Operator verwendet den Index. Insbesondere verwenden contains() und like den Index nicht, da like einen von der Groß- und Kleinschreibung unabhängigen Teilstringvergleich durchführt und der Optimierer nur die oben aufgeführten Operatoren akzeptiert.
3. Die andere Seite des Vergleichs ist ein Literal oder eine let-Variable. Sie darf kein Feldverweis sein und darf kein berechneter Wert sein. Ein berechneter Wert muss zuerst einer Variablen zugewiesen werden. Beispiele dazu finden Sie weiter unten.

Punkt 3 ist die praktische Stolperfalle und sollte in bestehenden Skripten überprüft werden. Der Indexscan benötigt den Suchwert einmal vor Beginn des Scans, um direkt zur richtigen Position im Index springen zu können. Eine Variable wird vorher einmal ausgewertet. Ein Feldverweis könnte dagegen ein Feld des gerade gelesenen Datensatzes sein, weshalb die Engine den Index nicht verwendet.

Verwendet den Index: Der Suchwert wird zuerst einer Variablen zugewiesen

let suchwert := Suchreferenz;
select Aufgaben where Referenz = suchwert

Verwendet den Index NICHT: direkter Vergleich mit einem Feld, vollständiger Scan

let zz := this;
select Aufgaben where Referenz = zz.Suchreferenz

Daraus ergibt sich eine strukturelle Regel: Die indizierte Bedingung muss der erste Parameter des select sein und eigenständig stehen. Weitere Bedingungen sollten anschließend auf das Ergebnis dieses select angewendet werden. Beispiele dazu finden Sie weiter unten.

Weitere Beispiele

Eine Funktion auf dem Feld verhindert die Verwendung des Indexes

Wenn Sie eine Funktion auf ein indiziertes Feld anwenden, kann der Index nicht verwendet werden:

select Aufgaben where upper(Status) = "OFFEN";
select Aufgaben where eineFunktion(Status)

Hier ist ein vollständiger Scan erforderlich, da das Feld nicht mehr direkt verwendet wird.

Die „or“-Bedingung verhindert die Verwendung des Indexes: Verwenden Sie getrennte if-Zweige

Eine „or“-Bedingung führt dazu, dass kein Index verwendet wird. Getrennte if-Zweige sind daher besser als ein kombinierter Ausdruck.

„or“: kein Index, die gesamte Tabelle wird gelesen:

let suchwert := Suchbegriff;
select Aufgaben where Referenz = suchwert or Titel = suchwert

Besser: ein Zweig pro Kriterium, wobei jeder Zweig seinen jeweiligen Index verwendet

 

let suchwert := Suchbegriff;
if SucheNachReferenz then
select Aufgaben where Referenz = suchwertA
else if SucheNachTitel then
select Aufgaben where Titel = suchwert
else
null
end

Kombinierte selects mit mehreren Bedingungen werden als Skriptfilter kompiliert

Das folgende Muster ist korrekt und funktionsfähig. Es durchsucht jedoch immer die gesamte Tabelle und verwendet keinen Index, selbst wenn Feld1 indiziert ist, da die kombinierte Bedingung als Skriptfilter kompiliert wird:

let k1 := KONDITION1;
let k2 := KONDITION2;
let k3 := KONDITION3;
select TABELLE where (not k1 or k1 = FELD1) and (not k2 or k2 = FELD2) and (not k3 or k3 = FELD3)

Wenn eines der Kriterien ein indiziertes Schlüsselfeld ist, setzen Sie dieses allein in das select und wenden Sie die übrigen Bedingungen auf das Ergebnis an:

if k1 then
(select Aufgaben where Feld1 = k1)[(not k2 or Feld2 = k2) and (not k3 or Feld3 = k3)]
else
select Aufgaben where (not k2 or k2 = Feld2) and (not k3 or k3 = Feld3)
end

Hinweis: „not k1“ behandelt auch den Wert 0 als „keine Bedingung“. Für Textfelder spielt dies keine Rolle, sollte bei numerischen Feldern oder Auswahlkriterien jedoch berücksichtigt werden.

Präfix- und Bereichssuche
 

Zwei Bedingungen auf demselben indizierten Feld mit >= und <= werden zu einem einzigen Bereichsscan zusammengefasst. Dadurch ist eine Präfixsuche möglich. Die Obergrenze muss zuerst berechnet und einer eigenen Variablen zugewiesen werden:

let p := Suchreferenz
let pEnde := p + "zzzz";
select Aufgaben where Referenz >= p and Referenz <= pEnde

Die Suche innerhalb eines Feldwertes kann keinen Index verwenden

Der Index sortiert Einträge anhand des vollständigen Feldwertes, beginnend mit dem ersten Zeichen. Dadurch kann direkt zu einem Wert oder einem Präfix gesprungen werden. Er kann jedoch keinen Text finden, der sich in der Mitte eines Wertes befindet. Eine Teilstringsuche entspricht genau einer solchen Suche. Deshalb können contains() und like niemals den Index verwenden und führen immer einen vollständigen Scan durch.

Praktische Alternative

• Speichern Sie den Wert in einer bereinigten, normalisierten Form, beispielsweise nur als Ziffern, mit einem Wert pro Feld und indiziert, und suchen Sie mit =.

 

Ein select in einem Formelfeld wird bei jedem Lesen neu berechnet

Ein select in einem Formelfeld wird bei jedem Lesen neu berechnet und jedes Mal, wenn irgendein Datensatz in der abgefragten Tabelle geändert wird. do as server bestimmt lediglich, wo die Abfrage ausgeführt wird, nicht wie häufig. Das bedeutet, dass alle Datensätze erneut geladen und anschließend im Arbeitsspeicher gefiltert werden. Dieser Vorgang kann durch keinen Index beschleunigt werden.

Platzieren Sie die Bedingung daher direkt im select, anstatt das Ergebnis eines Formelfeldes nachträglich im Arbeitsspeicher zu filtern. cached() ist kein Ersatz für eine Live-Ansicht: Die Funktion behält das erste Ergebnis bei, bis der Bearbeitungsmodus geöffnet oder invalidate() aufgerufen wird. Eine Live-Suche würde dadurch keine neuen oder geänderten Datensätze mehr anzeigen.