Geschwindigkeitsproblem bei Abfragen

[chico]

Hallo,
für einen Bericht habe ich folgende Abfrage erstellt:

Code: Alles auswählen

SELECT "Ausweis", "Nachname", "Vorname", "BisDatum", ( SELECT COUNT( * ) FROM "Kunden" WHERE "Archiviert" = FALSE AND DATEDIFF( 'dd', "BisDatum", CURDATE( ) ) > :abgelaufeneTage ) AS "Abgelaufene" FROM "Kunden" WHERE "Archiviert" = FALSE AND DATEDIFF( 'dd', "BisDatum", CURDATE( ) ) > :abgelaufeneTage

Es werden alle Kunden auflistet, deren Ausweis vor X-Tagen (Parameter abgelaufeneTage) oder früher abgelaufen ist und deren Anzahl (Abgelaufene) angezeigt. Das funktioniert auch, wobei die Abfrage bei 300 Datensätzen 12 Minuten dauert. Die beiden verschachtelten SELECT-Anweisungen:

Code: Alles auswählen

 ( SELECT COUNT( * ) FROM "Kunden" WHERE "Archiviert" = FALSE AND DATEDIFF( 'dd', "BisDatum", CURDATE( ) ) > :abgelaufeneTage )

und

Code: Alles auswählen

SELECT "Ausweis", "Nachname", "Vorname", "BisDatum" FROM "Kunden" WHERE "Archiviert" = FALSE AND DATEDIFF( 'dd', "BisDatum", CURDATE( ) ) > :abgelaufeneTage

brauchen jeweils einzeln nur 10 Sekunden.
Wie kann man die Geschwindigkeit insgesamt steigern ?
Vielleicht hat jemand eine gute Idee.
Mit Dank im voraus
chico

[Freischreiber]

Hallo chico,

geht nicht auch einfach:

Code: Alles auswählen

SELECT "Kunden"."Ausweis", "Kunden"."Nachname", "Kunden.""Vorname", "Kunden"."BisDatum", 
SELECT COUNT( * ) FROM "Kunden" AS "Abgelaufene" 
WHERE "Archiviert" = FALSE AND DATEDIFF( 'dd', "BisDatum", CURDATE( ) ) > :abgelaufeneTage ) 

Denn die Where-Überprüfung ist bei dir zweimal die gleiche, und eine Count-Abfrage kann man mit einer normalen Select-Anweisung in eine Reihe stellen.

Irgendwo hab ich auch gelesen, daß man die Spalten immer mit "Tabellenname"."Spaltenname" bezeichnen soll, weil der Zugriff dann schneller sei.

Zeitraubend sei auch ein SELECT *, schneller ist, nur die Felder explizit auszuwählen, die man auch wirklich braucht. Für eine COUNT-Abfrage müßte

Code: Alles auswählen

SELECT COUNT("Kunden"."ID") AS "Abgelaufene"

auch reichen, Anzahl der Primärschlüsselzeilen, die gibt es immer.

Gruß
Freischreiber

PS: Aber irgendwie ist die Abfrage immer noch komisch, zu jedem Treffer "abgelaufener Kunde" wird die Gesamtzahl abgelaufener immer wieder neu bestimmt und drangehängt, oder? Die Anzahl "abgelaufener Kunden" siehst du am Ergebnis der Abfrage ohne Count: Anzahl der Trefferzeilen.

[chico]

Hallo Freischreiber,
vielen Dank für Deine Antwort.
Die Abfrage soll Grundlage für einen Bericht sein. In der Überschrift des Berichtes steht: Bei xy Kunden ist der Ausweis abgelaufen, darunter werden die betroffenen Kunden aufgelistet. Der Bericht wird von einer Schaltfläche in einem Formular gestartet. Der Programmbediener kann das Formular nicht verlassen, die Zeilen zählen und den Wert eintragen. Das muss automatisch gehen. Es geht ja auch, aber viel zu langsam.

SELECT "Kunden"."Ausweis", "Kunden"."Nachname", "Kunden.""Vorname", "Kunden"."BisDatum",
SELECT COUNT( * ) FROM "Kunden" AS "Abgelaufene"
WHERE "Archiviert" = FALSE AND DATEDIFF( 'dd', "BisDatum", CURDATE( ) ) > :abgelaufeneTage )

Eine SELECT-Anweisung als 'Unterabfrage' muss immer vollständig sein, daher geht das o.a. Beispiel nicht.
COUNT(*) zählt Anzahl der Zeilen und ist etwas schneller als COUNT("ID"), das ist aber nicht das Problem.

Wie Du schreibst, finde ich auch die Abfrage komisch, da sie so oft mit dem gleichen Ergebnis ausgeführt wird, wie es Treffer in der "Oberabfrage" gibt.
Ich habe versucht mit der "Oberabfrage" eine Tabellenabfrage zu machen und dann diese Tabelle mit der "Unterabfrage" zu filtern. Das ergibt immer keinen Treffer.
Das müsste SQL-mäßig doch eine einfache Aufgabe sein, eine Tabelle zu filtern und die Zeilen des Ergebnisses zu zählen.
Ich habe auch versucht die Zeilen im Bericht zu zählen, komme aber damit bisher nicht weiter.
Ich hoffe, dass F3K Total oder RobertG vielleicht eine Antwort weiss.
Gruß
chico

[Freischreiber]

Hallo chico,

sorry, ich meinte so:

Code: Alles auswählen

SELECT "Kunden"."Ausweis", "Kunden"."Nachname", "Kunden.""Vorname", "Kunden"."BisDatum", COUNT( "Kunden"."ID" ) FROM "Kunden" AS "Abgelaufene"
WHERE "Archiviert" = FALSE AND DATEDIFF( 'dd', "BisDatum", CURRENT_DATE( ) ) > :abgelaufeneTage GROUP BY "Kunden"."Ausweis"

, also nur mit einem SELECT, sowas läuft bei mir, aber ich glaube weil COUNT eine Aggregatfunktion ist, braucht es das GROUP BY.

Gruß
Freischreiber

[Freischreiber]

Hallo chico,

langsam kapier ich das Problem, so liefert count immer nur 1... also wirklich 2x where...

Code: Alles auswählen

SELECT "Kunden"."Ausweis", "Kunden"."Nachname", "Kunden"."Vorname", "Kunden"."BisDatum", 
( SELECT COUNT( "Kunden"."ID" ) AS "Abgelaufene" FROM "Kunden" WHERE "Archiviert" = FALSE AND DATEDIFF( 'dd', "BisDatum", CURRENT_DATE( ) ) > :abgelaufeneTage ) 
FROM "Kunden" WHERE "Archiviert" = FALSE AND DATEDIFF( 'dd', "BisDatum", CURRENT_DATE( ) ) > :abgelaufeneTage

Aber das da oben läuft bei mir und dauert für 1200 Datensätze keine ganze Sekunde.

Gruß
Freischreiber

[RobertG]

Hallo *,

grundsätzlich zur Steigerung der Geschwindigkeit:
Nicht Abfragen mit Unterabfragen nutzen, vor allem nicht Unterabfragen, die für jede Zeile in vollem Umfang neu laufen müssen.
Besser Ansichten erstellen und diese aufeinander aufbauen lassen.
Parameter in dem Moment durch die Eingabe in eine Filtertabelle ersetzen. Aus der wird dann der Wert ausgelesen.
Die Parameter für die Abfrage können dann vom Formular aus direkt in die Tabelle geschrieben werden.
Die folgende gleichlautende Abfrage führt Base in jeder Zeile neu aus:

Code: Alles auswählen

SELECT COUNT( * ) FROM "Kunden" WHERE "Archiviert" = FALSE AND DATEDIFF( 'dd', "BisDatum", CURDATE( ) ) > :abgelaufeneTage

... und da die Abfrage bei Dir auch schon 10 Sekunden dauert ist das für mich erstaunlich, dass Base damit so schnell fertig ist.

Gruß

Robert

[chico]

Hallo,
vielen Dank Robert für Deine Antwort.
Ich habe versucht eine Ansicht der Tabelle mit der Grundabfrage zu erstellen um diesen View dann als Bezugs-Tabelle für die Unterabfrage zu benutzen. Das geht nicht, weil die Abfrage für die Ansicht sich zwar einmal erstellen lässt, aber die Abfrage des Parameters nicht erfolgt. Das Ergbnis ist dann ein leere Tabelle mit den Feldern der Abfrage.
Mit einer Filtertabelle funktioniert es, aber ich möchte kein zusätzliches Filter-Feld in meinem Formular anzeigen, (das Formular ist wie ein Menü mit Schaltflächen für verschiedene Berichte aufgebaut).
Ich habe nun eine Lösung mit Hilfe einer benutzerdefinierten Formel im Bericht-Designer gefunden. Der Bericht hat als Datenquelle die Grundabfrage

Code: Alles auswählen

SELECT "Ausweis", "Nachname", "Vorname", "BisDatum" FROM "Kunden" WHERE "Archiviert" = FALSE AND DATEDIFF( 'dd', "BisDatum", CURDATE( ) ) > :abgelaufeneTage

Im Bericht habe ich im Seitenkopf die Anzahl der abgelaufenen Ausweise mit einem Textfeld angezeigt, für das als Datenquelle der Datenfeld-Typ "Zähler" und Geltungsbereich "Bericht" ausgewählt wurde. Im Bericht-Navigator erscheint dann unter Funktionen eine Funktion "ZählerBericht" in dessen Eigenschaften nur noch die Vorausberechnung auf Ja gestellt werden muss, alles andere ist schon wie folgt eingetragen :
Name : ZählerBericht
Formel : [ZählerBericht] + 1
Anfangswert : 1
Unterberichte einbinden : Nein
Vorausberechnung : Ja
Das ist im Handbuch unter "Die Benutzeroberfläche des Report-Designers" - "Dateneigenschaften von Feldern" angesprochen aber nicht genau erklärt.
Die Geschwindigkeit ist jetzt wie die Dauer der Grundabfrage 10 Sekunden, der Zähler im Bericht benötigt fast keine Zeit.
Gruß
chico

[RobertG]

Hallo chico,

Im Bericht habe ich im Seitenkopf die Anzahl der abgelaufenen Ausweise mit einem Textfeld angezeigt, für das als Datenquelle der Datenfeld-Typ "Zähler" und Geltungsbereich "Bericht" ausgewählt wurde. Im Bericht-Navigator erscheint dann unter Funktionen eine Funktion "ZählerBericht" in dessen Eigenschaften nur noch die Vorausberechnung auf Ja gestellt werden muss, alles andere ist schon wie folgt eingetragen :
Name : ZählerBericht
Formel : [ZählerBericht] + 1
Anfangswert : 1
Unterberichte einbinden : Nein
Vorausberechnung : Ja
Das ist im Handbuch unter "Die Benutzeroberfläche des Report-Designers" - "Dateneigenschaften von Feldern" angesprochen aber nicht genau erklärt.

Wenn Du etwas genauer schreibst, wo Du Erklärungsbedarf siehst, dann will ich das wohl ergänzen. Die nächste Version des Handbuches soll ja Anfang Februar raus kommen ...
------------
Ich habe erst einmal in dem von Duir genannten Kapitel einen Verweis auf die "Benutzerdefinierten Funktionen" gemacht, in dem zumindest ein bisschen war dazu erklärt wird, was denn die Vorausberechnung erledigen soll.
-----------

Gruß

Robert

[chico]

Hallo RobertG,
beim Umgang mit dem Datenfeld-Typ "Zähler" erfährt man aus aus dem Handbuch, dass damit Datensätze gezählen werden können. Stellt man ein Textfeld in den Seitenkopf und wählt "Zähler" als Daten-Typ, wird zusätzlich der Geltungsbereich "Bericht" automatisch ausgewählt und das Datenfeld heißt [ZählerBericht]. Das ist logisch. Im Bericht erscheint aber in diesem Datenfeld eine 1, obwohl man dachte, es werden die Datensätze gezählt. Jetzt sucht man den Grund dafür und findet im Bericht-Navigator unter Funktionen eine neue Funktion, die sich automatisch eingerichtet hat. Wenn man schon mal eine Bentzerdefinierte Funktion eingerichtet hat, findet man diese Einstellungsmöglichkeit leichter. In den Eigenschaften dieser Funktion findet man dann u.a. eine Einstellung "Vorausberechnung" die auf "Nein" steht. Wenn man diese auf "Ja" stellt, werden im Seitenkopf alle Datensätze angezeigt.
Es wäre gut, wenn im Handbuch auf diesen Zusammenhang zwischen Textfeld-Eigenschaften und Funktionseigenschaften hingewiesen wird.
Ich habe zusätzlich versucht im Detailbereich die Datensätze zu nummerieren, kann aber nur entweder im Seitenkopf und im Detailbereich die Anzahl der Datensätze anzeigen oder die Nummerierung einschalten, wobei dann im Seitenkopf "1" steht.
Insgesamt ist mir bei diesem Probieren LO öfter abgestürzt.
Ansonsten bin ich sehr froh, dass es das Handbuch gibt !

Gruß
Jens