SQL Abfrage über mehrere Tabellen beschleunigen

[hirnstroem]

'loha Folks,

untenstehende Abfrage führt ab einer gewissen Anzahl Datensätzen in den verschiedenen Tabellen zu relativ grossen Geschwindigkeitseinbussen:

SQL-Code:

SELECT *
FROM
(Device_Key RIGHT JOIN
  (Room INNER JOIN 
    (Floor INNER JOIN
      (DeviceType INNER JOIN     
        (Building INNER JOIN 
          (Application INNER JOIN
            Device 
          ON [Application].[ApplicationID] =[Device].[ApplicationID]) 
        ON [Building].[BuildingID] =[Device].[BuildingID]) 
      ON [DeviceType].[DeviceTypeID] =[Device].[DeviceTypeID]) 
    ON [Floor].[FloorID] =[Device].[FloorID]) 
  ON [Room].[RoomID] =[Device].[RoomID])
ON [Device_Key].[DeviceID] = [Device].[DeviceID])
WHERE
DeviceType = :DeviceType
AND
[Device_Key].[KeyID] = :KeyID
ORDER BY Address;

Dass die Abfrage mit ansteigender Anzahl der davon betroffenen Datensätzen immer langsamer wird leuchtet mir ein. Die Situation ist aber die folgende:

- Die Tabelle "Device" enthält bis zu max. 512 Geräte
- Die Tabelle "Key" enthält bis zu max. 2000 Schlüssel
- Die Tabelle "Device_Key" enthält folglich bis zu 1'024'000 Einträge (welche dann auch das Problem zu sein scheinen)

Obige Abfrage soll sämtliche Geräte (sowie zusätzliche Infos wie die Angabe in welchem Raum sich das Gerät befindet, etc.) auflisten, welchen ein Schlüssel zugeordnet ist. Dies dauert im Extremfall (512 Geräte mit jeweils 2000 Schlüsseln) weit über zehn Sekunden auf meinem Computer.

Nun fehlt es mir etwas an Erfahrung bezüglich dem Umgang mit Datenbanken und ich wollte mich erkundigen, mit welcher Geschwindigkeit ihr unter den genannten Voraussetzungen in etwa rechnen würdet, beziehungsweise, wo man ansetzen könnte, um die Geschwindigkeit der Abfrage etwas zu beschleunigen.

Meine bisherigen Versuche, die Indizierung verschiedener Felder zu aktivieren / deaktivieren, hat bisher nichts gebracht.

Grüsse
hrinstroem

[shmia]

Zitat:

Zitat von hirnstroem

Meine bisherigen Versuche, die Indizierung verschiedener Felder zu aktivieren / deaktivieren, hat bisher nichts gebracht.

Schau dir mal die ON - Verknüpfungen an:

SQL-Code:

          ON [Application].[ApplicationID] =[Device].[ApplicationID]) 
        ON [Building].[BuildingID] =[Device].[BuildingID]) 
      ON [DeviceType].[DeviceTypeID] =[Device].[DeviceTypeID]) 
    ON [Floor].[FloorID] =[Device].[FloorID]) 
  ON [Room].[RoomID] =[Device].[RoomID])
ON [Device].[DeviceID] = [Device_Key].[DeviceID])

Die Felder auf der linken Seite (also [Application].[ApplicationID], usw.) sind ja alles Primärschlüsselkandidaten.
Ich hoffe du hast auf allen 6 Tabellen einen Primärschlüssel gesetzt.
Die Felder auf der rechten Seite ([Device].[ApplicationID], ...) sind alles Fremdschlüssel.
Alle diese Felder sollten einen Index haben.
Dabei hast du die Wahl zwischen je einem Index pro Fremdschlüsselfeld und der Kombination mehrerer Fremdschlüsselfelder zu einem Index.
Wenn ein Index mehrere Felder umfasst, hat das gewisse Performancevorteile; aber nur dann wenn alle beteiligten Felder auch in der Abfrage benützt werden. (es gibt noch Ausnahmen von dieser Regel, aber das führt im Moment zu weit)

[hirnstroem]

Zitat:

Zitat von shmia

Ich hoffe du hast auf allen 6 Tabellen einen Primärschlüssel gesetzt.

Ja, dem ist so. Mittlerweile habe ich auch herausgefunden, dass wenn die Fremdschlüssel keinen Index haben, die Abfrage noch viel langsamer Ausgeführt wird.

Aber ob nun zehn Sekunden, für das herausfiltern von 512 Datensätzen aus einer Menge von 1'024'000 Datensätzen, eine gute Zeit sind oder nicht, kann ich noch immer nicht so richtig beurteilen.

Vielen Dank für deine Ausführungen Andreas.

Grüsse
hirnstroem

[shmia]

Es gibt noch weitere Optimierungsmöglichkeiten.
Deine Abfrage mit SELECT * FROM ... ist schlecht, da so alle Felder aus allen Tabellen gezogen werden.
Viele Felder sind dann doppelt vorhanden.
Besser so:

Delphi-Quellcode:

SELECT Device.*, Building.Adress, ... FROM

Das *-chen darf man nur einmal auf eine Tabelle anwenden.
Alle anderen Felder sollten voll qualifiziert angegeben werden.
Je weniger Daten du abrufst umso schneller die Abfrage.
Und bei Access ist CursorLocation = clUseServer schneller als clUseClient.

[hirnstroem]

Nun gut, in diesem Falle werden, bis auf die jeweiligen Hauptschlüssel, sämtliche Daten auf einem Webinterface dargestellt. Die CursorLocation ist aber in der Tat beim Client. Da maximal 512 Geräte respektive 2000 Schlüssel in der Datenbank vorkommen, sollte dies aber nicht übermässig ins Gewicht fallen. Zudem meine ich, dass ein serverseitiger Cursor noch andere Probleme nach sich zieht (aufgrund spezieller Filter oder Sortierungen, habe es einmal ausprobiert und bin schnell angestanden).

Werde das ganze noch auf einer nicht Access Datenbank durchspielen, wenn sich mir die Gelegenheit bietet, das könnte vielleicht noch aufschlussreich sein.

[hirnstroem]

SQL-Code:

SELECT *
from device_key, device, deviceType, Building, Floor, Room, Application
where device_key.keyID = :KeyID
and deviceType.deviceType = "Door Interface"
and device_key.deviceId = device.deviceId
and device.deviceTypeId = deviceType.deviceTypeId
and device.buildingID = Building.buildingID
and device.floorId = Floor.floorId
and device.RoomId =Room.RoomId
and device.ApplicationId = Application.ApplicationId

^^ liefert dieselben Ergebnisse wie jene Abfrage mit den komisch verschachtelten Joins, nur um ein vielfaches schneller. Nun dauert das ganze ein Paar Millisekunden, was, im Vergleich zu der halben Minunte bei der Abfrage zuvor, recht gut ist.

Grüsse
hirnstroem

[Bernhard Geyer]

Zitat:

Zitat von hirnstroem

dass ein serverseitiger Cursor noch andere Probleme nach sich zieht .

Normalerweise sollte man bei Access immer Serverseitigen Curser verwenden, um ein 1* Speicherkopieren zu vermeiden (Access ist ja kein Server und nötige DLL's sind ja im Client-Adressraum geladen.

Zitat:

Zitat von hirnstroem

^^ liefert dieselben Ergebnisse wie jene Abfrage mit den komisch verschachtelten Joins, nur um ein vielfaches schneller. Nun dauert das ganze ein Paar Millisekunden, was, im Vergleich zu der halben Minunte bei der Abfrage zuvor, recht gut ist

Da vermute ich mal das die vielen Joins einfach zu viel des guten für die Jet-Engine waren und er einfach intern auf stupide Full Table-Scan geschaltet hat.

[Jelly]

Du hattest ja in deiner ersten Abfrage (die mit den Joins) ja auch ein RIGHT OUTER JOIN stehen.... OUTER JOINS sind wesentlich langsamer als FULL JOINS.

[hirnstroem]

Zitat:

Zitat von Bernhard Geyer

Da vermute ich mal das die vielen Joins einfach zu viel des guten für die Jet-Engine waren und er einfach intern auf stupide Full Table-Scan geschaltet hat.

Wird wohl so etwas in dieser Art gewesen sein.

Zitat:

Zitat von Jelly

Du hattest ja in deiner ersten Abfrage (die mit den Joins) ja auch ein RIGHT OUTER JOIN stehen.... OUTER JOINS sind wesentlich langsamer als FULL JOINS.

Und ich bin davon ausgegangen, dass, wenn es schon so schöne Join-Konstrukte gibt, diese auch schneller sind als andere, herkömliche Schreibweisen.

[Jelly]

Zum Verständnis:
Bei einem INNER JOIN werden aus den beiden betroffenen Tabellen nur die Datensätze berücksichtigt, die übereinstimmen. Bei einem outer join werden bei einer Tabelle ALLE Datensätze herbeigezogen.