[Advantage] Komplexere Abfrage dauert ewig

[Nuclear-Ping]

Hallo,

folgende Abfrage dauert bei untenstehender Datenmenge "ewig":

SQL-Code:

  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;

Die Datenmenge ist:

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

Die Datenbanken haben meiner Meinung nach alle ordentliche Indexe. Die Abfrage dauert bei mir 64sek (und bei anderen, zB den betroffenen Kunden).
Eine Abfrage á la "SELECT * FROM SendingSchedule" dauert allerdings nur ein paar Millisekunden.

Weiß jemand warum und wie man das schneller kriegt?

Die Strukturen sind wie folgt:

SQL-Code:

/* 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 );

/* 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 );

/* 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 );

/* 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 );

[marabu]

Hallo Mario,

ich habe keine Erfahrung im Umgang mit ALS, aber ich fürchte da fehlen noch ein paar Indexe. Wenn das Handbuch nichts genaueres hergibt, dann würde ich probeweise jede Spalte indizieren, die in WHERE und ORDER BY Klauseln auftaucht.

Musst du mit gemischter JOIN Syntax arbeiten oder geht auch das hier?

SQL-Code:

/* getippt und nicht getestet */
SELECT TOP 20
    C.ID,
    C.Name,
    H.Title,
    H.ObjectID,
    O.Name,
    O.ID,
    S.*
FROM
    SendingSchedule S
    JOIN Clients C ON C.ID = S.ClientID
    JOIN HealingsheetsIndex H ON H.ID = S.HealingsheetIndexID
    LEFT OUTER JOIN Objects O ON O.ID = H.ObjectID
WHERE
    H.Active = TRUE
    AND S.Active = TRUE
ORDER BY
    S.SendingTimestamp ASC

Grüße vom marabu

[Nuclear-Ping]

Hallo marabu,

danke für die Tipps. Hab mal Indexe über die anderen relevanten Spalten der ganzen Tabellen gelegt. Die letzte Abfrage war 44sek, statt 61sek - was zwar schonmal eine Verbesserung ist, aber für unsere Zwecke immernoch zu lang. 5sek oder weniger wären super, 10sek oberste Schmerzgrenze. ;)

Die Query nimmt der Parser so nicht an. Er meint "Unexpected token: JOIN -- Expecting semicolon. -- Location of error in the SQL statement is: 127 (line: 11 column: 5)".

Wenn ich da ein Komma oder Semikolon setze, kommen weitere andere Fehler. Das Semikolon erkennt er wohl als Abschluss des Statements, da er dann meint "Expected lexical element not found: CREATE, DROP, ...".

Bei einem Komma meint er "Expected lexical element not found: <identifier> -- Missing table name. There was a problem parsing the table names after the FROM keyword in your SELECT statement. -- Location of error in the SQL statement is: 128 (line: 11 column: 5)".

Leider kenne ich mich mit der JOIN-Syntax nicht aus, um das zu fixen. Die eine Query oben hat ein Kollege geschustert (der grad nicht erreichbar ist). Den hab ich heute auch schon gefragt, ob er 'ne Ahnung hat, warum das so lange dauert. Er aber auch nichts "schlimmes" an der Query feststellen können und nur gemeint, ich solle mal die "LEFT JOIN"-Klausel rausnehmen, hat aber auch nichts gebracht. :gruebel:

[DeddyH]

Ersetz das SQL mal probehalber dort, wo nur JOIN steht, durch INNER JOIN.

[Leonard]

Halle Nuclear-Ping,

setz einfach vor die JOIN, die er nicht möchte, ein INNER davor.

SQL-Code:

FROM
    SendingSchedule S
    INNER JOIN Clients C ON C.ID = S.ClientID
    INNER JOIN HealingsheetsIndex H ON H.ID = S.HealingsheetIndexID
    LEFT OUTER JOIN Objects O ON O.ID = H.ObjectID

Dann könntest du dir noch im DataArchitect den execution plan für die Query anschauen.

mfg Leonard

[Nuclear-Ping]

Danke. Mit INNER davor gehts.

SQL-Code:

SELECT TOP 20
    C.ID,
    C.Name,
    H.Title,
    H.ObjectID,
    O.Name,
    O.ID,
    S.*
FROM
    SendingSchedule S
    INNER JOIN Clients C ON C.ID = S.ClientID
    INNER JOIN HealingsheetsIndex H ON H.ID = S.HealingsheetIndexID
    LEFT OUTER JOIN Objects O ON O.ID = H.ObjectID
WHERE
    H.Active = TRUE
    AND S.Active = TRUE
ORDER BY
    S.SendingTimestamp ASC

Dauert aber immernoch lange. Beim Mitzählen 21, 22, ... komme ich bis 74.

Den Execution-Plan habe ich angehangen. Kann man daraus schlau werden? ^^

[alzaimar]

Das Problem dürfte die Sortierung sein. Obwohl man nur die ersten 20 Einträge sehen will, muss die Engine ja erst mal das gesamte Resultset erzeugen.

Folgendes kann u.U. funktionieren:

Ich kenn ALS nicht, aber irgendwie wirst Du eine temporäre Tabelle erstellen können. Bei MSSQL (meinem System) geht das so:

SQL-Code:

Select * into TempTabelle from Tabelle Where Foo=Bar

Nehmen wir mal an, das ginge so, dann würde ich erstmal eine minitabelle mit den ersten 20 Einträgen aus der SendingSchedule erstellen und das SELECT dann mit dieser Minitabelle (anstelle von SendingSchedule) machen.

Gibt es bei ALS besondere Indextypen? Bei MSSQL bringt ein Index bei einem ORDER BY nur dann etwas, wenn dieser 'CLUSTERED' ist (eine Microsoft-Eigenheit)..

Versuche es erstmal ohne das ORDER BY. Wenn die Performance dann ok ist, taste dich weiter ran.


[edit] hab eben den Query-Plan gesehen. Sieht fast so aus wie bei MSSQL. Der TABLESCAN und das SORT sind deine Sorgenkinder[/edit]

[f.siebler]

manchmal kann es auch Sinn machen die Joins richtig zu sortieren... ich weiß nicht wie gut der Advantage Query Optimizer ist... aber nen Versuch könnte es wert sein...
Dabei ist zu beachten, das, je früher man die Datenmenge einschränken kann, um so schneller läuft es...

[Leonard]

Man kann im ADS/ALS auch Temptabellen anlegen.

SQL-Code:

Select * into #TempTabelle from Tabelle Where Foo=Bar

Das # markiert die Tabelle als Temptabelle.


Es geht aber auch soetwas:

SQL-Code:

FROM
    (select top 20 * from SendingSchedule) S
    INNER JOIN Clients C ON C.ID = S.ClientID
    INNER JOIN HealingsheetsIndex H ON H.ID = S.HealingsheetIndexID
    LEFT OUTER JOIN Objects O ON O.ID = H.ObjectID

@Nuclear-Ping
Hast du mal geschaut, dass du wirklich auf allen Feldern, die in den JOIN Anweisungen vorkommen, einen Index hast? Mein execution plan (mit ADS8.1 erstellt) sieht ein wenig anders aus.

EDIT: Ich habe mir das ganze nocheinmal angeschaut, nachdem ich ein paar Daten in die Tabellen eingegeben habe und da entspricht der execution plan deinem Bild.

[hoika]

Hallo,

ersetze mal das left outer join testweise durch inner join.
left joins sind sehr aufwendig.

Ich würde das anders machen.

der left join ...
left outer join Objects ON Objects.ID = HealingsheetsIndex.ObjectID

Sorge jetzt dafür, dass du keinen left join brauchst.
HealingsheetsIndex.ObjectID enthält entweder eine der Objects.ID
oder auch nicht, deshalb ja dein left join.

Lege jetzt ein Objects-Eintrag mit der ID = 0 an.
Trage in alle HealingsheetsIndex-records, wo HealingsheetsIndex.ObjectID NULL ist,
die 0 ein.

Du musst natürlich jetzt dafür Sorgen, dass dein Programm
dass beim insert/update/delete auch macht.

Jetzt kannst du einen inner join bentzen
inner join Objects ON Objects.ID = HealingsheetsIndex.ObjectID

Durch den inner join entfällt der full table scan.


Heiko