Abfrage optimieren

[Nuclear-Ping]

Hallo,

ich habe hier eine Abfrage, die ~44sek dauert. Für den Einsatzbereich ist das aber viel zu lang. 5-10sek wären hier optimal.

SELECT TOP 20
    Clients.ID,
    Clients.Name,
    HealingsheetsIndex.Title,
    HealingsheetsIndex.ObjectID,
    Objects.Name,
    Objects.ID,
    SendingSchedule.*
  FROM
    SendingSchedule,
    Clients,
    HealingsheetsIndex
      LEFT OUTER JOIN
        Objects ON Objects.ID = HealingsheetsIndex.ObjectID
  WHERE
    (Clients.ID=SendingSchedule.ClientID) AND
    (HealingsheetsIndex.ID=SendingSchedule.HealingsheetIndexID) AND
    ((HealingsheetsIndex.ID=SendingSchedule.HealingsheetIndexID) AND
    (HealingsheetsIndex.Active=TRUE)) AND
    (SendingSchedule.Active=TRUE)
  ORDER BY
    SendingSchedule.SendingTimestamp ASC;

Folgende Datenmengen sind in den Tabellen:

• SendingSchedule hat 358.270 Einträge
• Clients hat 14 Einträge
• HealingsheetsIndex hat 59 Einträge
• Objects hat 5 Einträge

Das sind die Tabellenstrukturen:

/* Table Type of Clients.adt is ADT*/
Create Table Clients(
   ID AutoInc,
   Title Char( 32 ),
   Name Char( 64 ),
   Address Memo,
   UseBirthData Logical,
   BirthDate Date,
   BirthTime Time,
   BirthPlace Char( 32 ),
   BirthCountry Char( 32 ),
   Gender Integer,
   UseManualGeoData Logical,
   Longitude Double,
   Latitude Double,
   Zone Time,
   DST Time,
   Image Blob,
   IsActive Short );

Create Unique Index "ID" on Clients( ID );
Create Index "NAME" on Clients( NAME );

/* Table Type of HealingsheetsIndex.adt is ADT*/
Create Table HealingsheetsIndex(
   ID AutoInc,
   DateAdded TimeStamp,
   ClientID Integer,
   ObjectID Integer,
   Title Char( 64 ),
   FirstSending TimeStamp,
   LastSending TimeStamp,
   LastSent TimeStamp,
   Frequency Double,
   Duration Double,
   SendingType Short,
   Nightrest Logical,
   NightrestFrom TimeStamp,
   NightrestTo TimeStamp,
   VibrationMs Integer,
   Active Logical,
   FocusType Short,
   FocusText Char( 128 ),
   Flags Integer );

Create Index "CLIENTID" on HealingsheetsIndex( CLIENTID );
Create Unique Index "ID" on HealingsheetsIndex( ID );
Create Index "OBJECTID" on HealingsheetsIndex( OBJECTID );
Create Index "TITLE" on HealingsheetsIndex( TITLE );
Create Index "SENDINGTYPE" on HealingsheetsIndex( SENDINGTYPE );
Create Index "ACTIVE" on HealingsheetsIndex( ACTIVE );
Create Index "FLAGS" on HealingsheetsIndex( FLAGS );
Create Index "FIRSTSENDING" on HealingsheetsIndex( FIRSTSENDING );
Create Index "LASTSENDING" on HealingsheetsIndex( LASTSENDING );
Create Index "LASTSENT" on HealingsheetsIndex( LASTSENT );

/* Table Type of SendingSchedule.adt is ADT*/
Create Table SendingSchedule(
   ID AutoInc,
   HealingsheetIndexID Integer,
   ClientID Integer,
   Duration Integer,
   Frequency Integer,
   SendingTimestamp TimeStamp,
   EndingTimestamp TimeStamp,
   SendingType Short,
   Active Logical );

Create Index "HEALINGSHEETINDEXID" on SendingSchedule( HEALINGSHEETINDEXID );
Create Index "CLIENTID" on SendingSchedule( CLIENTID );
Create Unique Index "ID" on SendingSchedule( ID );
Create Index "SENDINGTIMESTAMP" on SendingSchedule( SENDINGTIMESTAMP );
Create Index "ENDINGTIMESTAMP" on SendingSchedule( ENDINGTIMESTAMP );
Create Index "SENDINGTYPE" on SendingSchedule( SENDINGTYPE );
Create Index "ACTIVE" on SendingSchedule( ACTIVE );

/* Table Type of Objects.adt is ADT*/
Create Table Objects(
   Id AutoInc,
   ClientId Integer,
   Title Char( 32 ),
   Name Char( 64 ),
   Address Memo,
   HasPicture Logical,
   Picture Blob,
   Notes Memo );

Create Unique Index "ID" on Objects( ID );
Create Index "CLIENTID" on Objects( CLIENTID );
Create Index "NAME" on Objects( NAME );

Hier ist der Query-Execute-Plan.

Created: 09/11/2007 09:52:09

Item NodeID     ParentID   RowType                        Statement- Statement- Operator                       Arguments  Estimated- Warning   
-----------------------------------------------------------------------------------------------------------------------------------------------
   1          1          0 SELECT                          SELECT T-          3                                                    1           
   2          2          1 PLAN                           ORDER BY            3 SORT                           [SendingS-                      
   3          3          2 PLAN                                               3 EVALUATE                       WHERE cla-                      
   4          4          3 PLAN                                               3 LEFT OUTER JOIN                                                
   5          5          4 PLAN                           Table Nam-          3 TABLE SCAN                     AOF<Activ-          1           
   6          6          4 PLAN                                               3 JOIN                                                           
   7          7          6 PLAN                           Table Nam-          3 INDEX SCAN                     Join Cond-         50           
   8          8          6 PLAN                                               3 JOIN                                                           
   9          9          8 PLAN                           Table Nam-          3 INDEX SCAN                     AOF<Activ-         50           
  10         10          8 PLAN                           Table Nam-          3 INDEX SCAN                     Join Cond-         50

Bisher haben wir (auch durch die Hilfe des DP-Forums) herausgefunden, dass "ORDER BY", "TOP 20" in Verknüpfung mit den anderen Tabellen die Sorgenkinder sind. Entfernt man aus der Abfrage "ORDER BY" sind die Ergebnisse sofort da.

Hat hier jemand noch eine Idee, wie man die Abfrage beschleunigen kann?

[Joachim Dürr]

ich würde vorschlagen, das ganze mal auf dem Advantage Newsserver zu posten (news:devzone.advantagedatabase.com). Dort lesen auch die Entwickler des ADS mit und Alex Wong (der SQL Experte) kann Dir eventuell das Statement umstellen .