4. DISTINCT Abfrage beschleunigen

DISTINCT Abfrage beschleunigen

  • G

    Hallo,

    ich habe folgendes Szenario:

    sqlite-Datenbank:

    Tabelle "Entries"

    CREATE TABLE Entries (
        id INTEGER PRIMARY KEY ASC AUTOINCREMENT,
        name TEXT,
        value TEXT);
CREATE INDEX EntriesIdIdx ON Entries (id);

    Des Weiteren existiert eine Tabelle "Filters".
    Diese speichert einen hierachichen Baum von Filtern, die der User auswählen soll:

    CREATE TABLE Filters (
        id INTEGER PRIMARY KEY ASC AUTOINCREMENT,
        name TEXT,
        lft INTEGER,
        rgt INTEGER);
CREATE INDEX FiltersIdIdx ON Filters (id);
CREATE INDEX FiltersLftIdx ON Filters (lft);
CREATE INDEX FiltersRgtIdx ON Filters (rgt);

    Schlussendlich ein letzte Tabelle, die die beiden Informationen verknüpft.
    (Leider kann ein Entries-Datensatz mehreren Filters-Datensätzen zugeordnet werden.)

    CREATE TABLE FilterList (
        id INTEGER PRIMARY KEY ASC AUTOINCREMENT,
        filterId INTEGER,
        entryId INTEGER,
        FOREIGN KEY(filterId) REFERENCES Filters(id),
        FOREIGN KEY(entryId) REFERENCES Entries(id));
CREATE INDEX FilterListFilterIdIdx ON FilterList (filterId);
CREATE INDEX FilterListIdIdx ON FilterList (id);
CREATE INDEX FilterListQuestionIdIdx ON FilterList (entryId);

    Ein typische Filterbaum wird in der GUI wie folgt angezeigt:

    Filter 0
  + Filter 1
  |     |- Filter 1A
  |      - Filter 1B
  + Filter 2
        |- Filter 2A
         - Filter 2B

    Ist ein Filter gewählt (z.B. Filter 1), gibt mir die GUI eine Liste aller darunter befindlichen IDs (in diesem Bsp: die IDs von Filter 1, Filter 1A, Filter 1B).

    Nun generiere ich die Abfrage wie folgt:

    SELECT DISTINCT(entry.id), entry.name, entry.value FROM Entries AS entry WHERE
    entry.id IN (
        SELECT DISTINCT(list.entryId) FROM FilterList AS list WHERE
            list.filterId IN ("Filter 1 ID, Filter 1A ID, Filter 1B ID"))
    AND entry.id < 80000;

    (AND entry.id < 80000 ist nur ein Bsp. für eine weitere Filterung und soll hier nicht zur Debatte stehen, da es sich dabei nicht um den geschwindigkeitsbeschränkenden Schritt handelt.)

    Leider dauert die Abfrage bei ca. 1500 Filtern sowie 100 000 Einträgen in der FilterList einige Sekunden (die sich in der GUI störend bemerkbar machen). Gibt es eine Möglichkeit (meinetwegen auch durch Redesign der Datenbank) die ganze Sache zu beschleunigen?

    0

  • Das äussere DISTINCT ist unnötig: entry.id ist der Primary-Key, also kann es da keine Duplikate geben.

    Ansonsten...
    Modifiziere FilterListFilterIdIdx wie folgt:

    CREATE INDEX FilterListFilterIdIdx ON FilterList (filterId, entryId);

    ps: SQLite ist nicht gerade schnell wenn die Abfragen anfangen komplizierter zu werden. SQL Server CE ist da z.B. wesentlich potenter. Falls es dein Projekt zulässt, wäre es vielleicht nicht verkehrt, sich diesen mal anzusehen.

    ps2: "kompliziert" ist nicht ganz richtig bzw. nicht vollständig. SQLite kann nur "nested loops" Joins. Wenn also der "linke" Input aller JOINs (oder "IN" oder "WHERE EXISTS(...)") nur aus wenigen Zeilen besteht, dann ist SQLite hübsch flott, auch bei "komplizierten" Abfragen. Wenn der linke Input gross wird, bricht die Geschwindigkeit dementsprechend ein. Oder eben wenn kein passender Index existiert.

    0
  • G

    Vielen Dank, der richtige Index beschleunigt die Abfrage schon um einiges.
    Trotzdem werde ich mir SQL Server CE mal genauer ansehen.

    0
Anmelden zum Antworten