Optimierung von Abfragen mit verschachtelten Where-Klauseln

Question

Ich arbeite an einer Anruf-/Servicedesk-Software mit dieser Art von Layout (Firebird 2.1):

1. Anrufe werden durch Auswahl eines Bereichs und einer Art von Anruf aus diesem Bereich geöffnet (Tabellen Anrufe , Bereiche und Arten)

2. Benutzern wird ein Profil zugewiesen, das angibt, in welchen Bereichen und Typen sie Anrufe anzeigen oder bearbeiten können (Tabellenbenutzer und Tabellenprofile)

3. Je nach Profil kann ein Benutzer ALLE Typen eines bestimmten Bereichs anzeigen oder bearbeiten (Tabelle profile_areas), oder nur ausgewählte Arten von Aufrufen anzeigen oder bearbeiten (Tabelle profile_types)

4. Nutzer können neben der Möglichkeit, die in ihrem Profil zugewiesenen Bereiche einzusehen, auch über besondere Rechte verfügen, auch alle von ihnen geöffneten Anrufe anzeigen zu können (boolesche Spalte in Tabellenprofilen)

Ich poste eine abgespeckte, in Spalten umbenannte Version der Hauptstruktur. Ich glaube nicht, dass ich in der Lage sein werde Ich glaube nicht, dass ich in der Lage sein werde, die wirklichen Tabellen zu posten, die über 300 Felder und viele weitere FKs und Tabellen umfassen.

Dies ist die Mindeststruktur in Bezug auf Anrufe und Benutzerberechtigungen.

CREATE TABLE CALLS (
    CALLID INTEGER, /* PK */
    AREAID INTEGER, /* FK  ON TABLE AREAS */
    TYPEID INTEGER, /* FK  ON TABLE TYPES */
    USERID_OPENED_BY, /* FK  ON TABLE USERS */
    STATUS CHAR(1)
);

CREATE TABLE AREAS (
    AREAID INTEGER, /* PK */
    AREA_NAME VARCHAR(50),
);

CREATE TABLE TYPES (
    TYPEID INTEGER, /* PK */
    AREAID INTEGER, /* FK  ON TABLE AREAS */
    TYPE_NAME VARCHAR(50),
);

CREATE TABLE USERS (
    USERID INTEGER, /* PK */
    PROFILEID INTEGER, /* FK  ON TABLE PROFILES */
    USER_NAME VARCHAR(50),
);

CREATE TABLE PROFILES (
    PROFILEID INTEGER, /* PK */
    PROFILE_NAME VARCHAR(50),
    VIEW_ALL_CALLS_OPENED CHAR(1) /* if true, user can always view any calls he opened, regardless of area or type */
);

CREATE TABLE PROFILES_AREAS (
    PAREA_ID INTEGER, /* PK */
    PROFILEID INTEGER, /* FK  ON TABLE PROFILES */
    AREAID INTEGER (FK),
    CAN_VIEW_AREA CHAR(1), /* can view any calls on this area, regardless of types */
    CAN_EDIT_AREA CHAR(1) /* can edit any calls on this area, regardless of types */
);

CREATE TABLE PROFILES_TYPES (
    PTYPE_ID INTEGER, /* PK */
    PROFILEID INTEGER, /* FK  ON TABLE PROFILES */
    TYPEID INTEGER, /* FK  ON TABLE TYPES */
    CAN_VIEW_TYPE CHAR(1), /* can view any calls of this type */
    CAN_EDIT_TYPE CHAR(1) /* can edit any calls of this type */
);

Unsere ersten Kunden erreichen die Marke von über 10 Millionen Anrufen, und die wichtigsten einfachen Abfragen werden langsam.

Bei der Analyse des Abfrageplans scheint alles ordnungsgemäß indiziert zu sein, aber die Anzahl der indizierten Lesevorgänge zeigt so ziemlich immer 10 Millionen oder so an, selbst wenn die Abfrage insgesamt 5 oder mehr Ergebnisse liefert.

Das Problem scheint darin zu bestehen, dass wir wegen der vielen verschiedenen Variationen, die ein Profil haben kann, keine erfolgreichen Joins zum Aufbau der Where-Klausel verwenden können, was wiederum zu einer Vielzahl unterschiedlicher OR-Klauseln führt, mit denen wir umgehen müssen.

Im schlimmsten Fall ist es so, dass :

1. der Benutzer kann alle von ihm geöffneten Anrufe einsehen

2. der Benutzer kann einige Bereiche sehen, aber nicht alle

3. der Benutzer kann einige Typen anzeigen, aber nicht alle

Damit ergibt sich etwa folgendes Bild (nehmen wir an, die Benutzerkennung ist "1"):

SELECT CALLID FROM CALLS 
WHERE
    CALLS.USERID_OPENED_BY = 1 /* .User can view all calls he opened */
    OR  (
         CALLS.AREAID IN (1,2,3) /* areas the user can view, in his profile. we tried using a subselect here and things just went from bad to much, much worse */
         OR
         CALLS.TYPEID IN (1,2,3) /* types the user can view, in his profile. we tried using a subselect here and things just went from bad to much, much worse */
         )

Und diese Art von "Where"-Klausel ist leistungsmindernd.

Jemand riet uns, zu versuchen, die OR's in verschiedene Abfragen aufzuteilen und mit Union zu addieren, aber eine Reihe anderer Faktoren macht das sehr problematisch.

Idealerweise versuchen wir, unsere Kunden davon abzuhalten, eine so große Vielfalt an Profilberechtigungen zu verwenden, aber stattdessen scheint der Trend zu neueren und obskureren Bedürfnissen der Arten der Profilerstellung zu sein (was der Grund dafür ist, dass z. B. "alle von ihm geöffneten Anrufe sehen" eingeführt wurde).

Gibt es eine bessere Strategie, die wir verfolgen sollten?

Answer 1

Dies scheint zu lang für einen Kommentar zu sein. Meine Vermutung ist, dass das "oder" die Leistung mit der aktuellen Indexstruktur zunichte macht.

Eine Möglichkeit besteht darin, die Abfrage in eine Union-Anweisung aufzubrechen:

SELECT CALLID FROM CALLS
WHERE CALLS.USERID_OPENED_BY = 1 
union
SELECT CALLID FROM CALLS
WHERE CALLS.AREAID IN (1,2,3)
union 
SELECT CALLID FROM CALLS
WHERE CALLS.TYPEID IN (1,2,3) 

Normalerweise verabscheue ich diese Art von Änderung, aber Sie können sehen, ob sie den Ausführungsplan verbessert. Beachten Sie, dass ich hier "union" und nicht "union all" verwendet habe, um Duplikate zu vermeiden.

Sie sagen jedoch, dass dies nicht möglich ist.

Eine andere Idee wäre, die Bereiche und Typen in einer einzigen Referenztabelle zusammenzufassen. Dadurch würde die Anzahl der Indizes in der WHERE-Klausel auf zwei reduziert, was die Verknüpfung vielleicht optimaler machen würde. Können Sie Ihren Benutzern sonst beibringen, nicht alle drei gleichzeitig auszuwählen? Ist diese Funktionalität für die Anwendung wirklich erforderlich?