[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

[Nuclear-Ping]

Zitat:

Zitat von 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]

Genial, das wars. Ohne "ORDER BY" ist das Ergebnis sofort da.

Bin allerdings grad zu müde, um dem noch weiter nachzugehen, mach ich morgen ... :D

[Nuclear-Ping]

Hab mir das jetzt mal näher angeschaut und 'ne Query über die Temp.tabelle gebastelt. Allerdings bekomme ich da garkein Ergebnis mehr. :gruebel:

SQL-Code:

/*
DROP
  TABLE 
    #TempTable;
*/
// ****
   
SELECT TOP 20 *
  INTO
    #TempTable
  FROM
    SendingSchedule
  ORDER BY
    SendingTimestamp ASC;
   
SELECT /* TOP 20 */
    C.ID,
    C.Name,
    H.Title,
    H.ObjectID,
    O.Name,
    O.ID,
    S.*
FROM
    #TempTable 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;
// ****

[Edit]
Ok, es liegt am WHERE. Wenn ich nur S.Active drin lasse, bekomme ich zwar Ergebnisse, aber die weichen von der alten Query ab, da es weniger als 20 sind.
S.Active bezieht sich auf den jeweiligen Sendeeintrag. Wenn S.Active = FALSE ist, bedeutet das, dass der Eintrag gesendet wurde. H.Active bezieht sich aber auf die ganze Liste. Er soll mir also die Listen gleich draussen lassen, die generell nicht (mehr) aktiv sind.
Ich denke das liegt daran, weil die erste Query ja nur die TOP 20 aus SendingSchedules zieht, ohne auf HealingsheetIndex.Active zu achten. Aber wenn ich damit bisschen rumspiele, wird das Ganze schonwieder ziemlich langsam.
:coder2:

SQL-Code:

SELECT TOP 20 *
  INTO
    #TempTable
  FROM
    SendingSchedule S
    INNER JOIN HealingsheetsIndex H ON H.ID = S.HealingsheetIndexID
  WHERE
    H.Active = TRUE AND
    S.Active = TRUE
  ORDER BY
    SendingTimestamp ASC;   
      
SELECT
    C.ID,
    C.Name,
    H.Title,
    H.ObjectID,
    O.Name,
    O.ID,
    S.*
FROM
    #TempTable 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;

Bringt das gewünschte Ergebnis, allerdings auch erst wieder nach ~50sek. Wenn ich hier "ORDER BY" aus der ersten Query rausnehme, kommt das Ergebnis sofort, allerdings nicht die 20 nächsten Listen sondern quer durch's Gemüsebeet, unsortiert eben.
[/Edit]

[Nochmal Edit]
So wie es ausschaut, bremst ORDER BY oder TOP 20 immer aus, sobald man sie mit anderen Tabellen verknüpft (bei dieser Datenmenge). Die Ursprungsquery ohne TOP 20 und ohne ORDER BY geht flott. Ich hab daher schon daran gedacht, die Ergebnisse in dem Programm dann sortieren zu lassen.

Was würde man für so eine Datenmenge für einen Algo nehmen, der mir das nach dem Datum im Nu sortieren kann?

Und nochmal ...
Grad versucht, QuickSort da reinzubauen. Problem ist nur, dass im Programm der erste Aufruf von .RecordCount ~30sek dauert, dann die Zuweisung in ein Array, welches ich an QuickSort übergeben kann nochmal solange. Der Algo selber braucht dann nur ~10sek ... :wall:
[/Edit]

[Nuclear-Ping]

Jemand noch 'ne Idee?

Mein letzter Teststand war der, dass ich die ganze Query ohne "TOP 20" und ohne "ORDER BY" ausführe und dafür dann aber auch ~300k Einträge aus der Query in meine Software bekomme.

Da der erste Aufruf von RecordCount hier immer lange gedauert hat (~30-40sek), hab ich vorher eine COUNT-Query auf SendingSchedules gemacht, damit ich ungefähr weiß, wieviel Einträge ich bekomme.

Die Zuweisung dieser 300k Einträge passiert im Programm über die Funktionen First, Next und EOF, wo ich auch die tatsächliche Anzahl an verarbeiteten Datensätzen ermitteln kann. Das dauert aber auch ewig, ~1min.

Kann man aus TQuery irgendwie einen Pointer auf die Daten bekommen, sodass man die in einem Rutsch mit MemCopy oder so in ein Array bekommt, damit ich das dann sortieren kann? Oder was gäbe es da noch für Möglichkeiten?

[Nuclear-Ping]

Hallo,

bin nun mal dazu gekommen, die Funktion zu überarbeiten. Dabei habe ich generell den ganze Sende-Manager mal ausgemistet und ziemlich viel wegrationalisiert und auch das Konzept umgebaut.

Vorher war der Sende-Manager neben der komplexeren Query auch im Programm über 3 Klassen verstreut, mit Wartelisten, die wie bei nem Drucker abgearbeitet wurden, usw. Dabei hat er auch zig mal auf die Datenbank zugegriffen, weil ich ursprünglich beim Programmieren nie auf die Idee gekommen bin, dass da jemand soviele Aufträge reinhaut und das Programm damit so in die Knie geht.

Ich mache es nun so, dass ich mir einmal am Anfang, wenn der Sende-Manager gestartet wird, den ganzen Sendeplan hole, aufsteigend sortiert nach Sendedatum und sonst nix dazu. Also mit keinen Tabellen verknüpfen oder so ...

SQL-Code:

SELECT * FROM
  SendingSchedule
 WHERE
   Active=TRUE
 ORDER BY
   SendingTimestamp;

Bei der Datenbank mit den ~300.000 Datensätzen dauert diese Ausführung im Programm ca. 20sek. Separat hole ich mir dann die Klienten und deren Sendeindex und speichere sie in Arrays, woraus ich bei Bedarf zu einem Sendeauftrag den Klienten und Sendeindex hole (das dauert nur ein paar Ticks).

Dann arbeitet er halt die Liste ab, aktualisiert jeweils den Active-Status in der Datenbank und gut. Sollte sie irgendwann leer werden, wird sie wieder befüllt, was im schlimmsten Fall nochmal max. ~20sek dauert. Aber dazu muss er halt erstmal die 300.000 Einträge abarbeiten, und das wird meistens in einer Sitzung nicht passieren.

Also zusammengefasst kann man sagen, dass aus den ~60-70sek. nach jeder Sendung jetzt einmal ~20sek am Anfang des ganzen Sende-Vorgangs geworden sind. Nach jeder einzelnen Sendung braucht er nun ~1-2sek, um den Eintrag zu aktualisieren und den nächsten zu starten. Auf jeden Fall ne große Verbesserung. :)

Herzlichen Dank auch nochmal an dieser Stelle an Leonard / Tobias, der mir mit PNs bei dem Problem geholfen hat. :thumb:

Grüße,

Mario


[edit]
Schreibfehler ...
[/edit]