SQL optimimieren notwendig Max()

[Jumpy]

Was bedeutet es denn, dass die Reads from disk to cache bei der Max Abfrage so hoch sind, bei der Min aber nicht?

[Blup]

Dein Index über das Feld1 ist aufsteigend.
Das heist bei einer min()-Abfrage findet die DB das richtige Ergebnis gleich am Anfang, bei der max()-Abfrage muss die DB bis zum Ende durchsuchen.
Dafür kann man dann einen absteigenden Index anlegen.
Für manche Anwendungsfälle sind auch zwei Index über ein Feld sinnvoll, einer aufsteigend und einer absteigend.

[IBExpert]

probier mal

Code:

select first 1 feld1,feld2,feld3
from
(select feld1, feld2,feld3 from tab where feld2=xxx)
where feld2 not in ('klein','')
order by feld1 desc

ich bin mir ziemlich sicher das da noch indizes benutzt werden, die man nicht benutzen sollte
und bei dem o.a. verfahren kann ein index nur in der inneren Abfrage benutzt werden , das
außenrum nutzt niemals indizes.

[Dumpfbacke]

Zitat von Blup:

Dein Index über das Feld1 ist aufsteigend.
Das heist bei einer min()-Abfrage findet die DB das richtige Ergebnis gleich am Anfang, bei der max()-Abfrage muss die DB bis zum Ende durchsuchen.
Dafür kann man dann einen absteigenden Index anlegen.
Für manche Anwendungsfälle sind auch zwei Index über ein Feld sinnvoll, einer aufsteigend und einer absteigend.

Das Feld also Feld1 hat zwei einen Auf und einer Absteigend.

[Dumpfbacke]

Zitat von IBExpert:

probier mal

Code:

select first 1 feld1,feld2,feld3
from
(select feld1, feld2,feld3 from tab where feld2=xxx)
where feld2 not in ('klein','')
order by feld1 desc

ich bin mir ziemlich sicher das da noch indizes benutzt werden, die man nicht benutzen sollte
und bei dem o.a. verfahren kann ein index nur in der inneren Abfrage benutzt werden , das
außenrum nutzt niemals indizes.

Ich habe es versucht. Es wird hier der Index von Feld2 benutzt und es dauert 6 Sekunden bis zum Ergebnis.

------ Performance info ------
Prepare time = 0ms
Execute time = 6s 718ms
Avg fetch time = 6.718,00 ms
Current memory = 18.151.488
Max memory = 18.236.568
Memory buffers = 2.048
Reads from disk to cache = 90.240
Writes from cache to disk = 0
Fetches from cache = 90.319

Das ganze ist schon etwas merkwürdig.

[Blup]

Du hast deine Bedingung für die Abfrage geändert.
Die zweite Bedingung der ursprünglichen Abfrage bezog sich auf Feld3.
("not in" oder mehrere "<>" führen zum selben Plan und sind somit auch gleich langsam)

SQL-Code:

/**/
Select Max(Feld1) From tabelle1 where Feld2 = 'Schraube' and Feld3 <> 'klein' and Feld 3 <> ''

Darauf bezogen sind auch die Optimierungsversuche und mein Vorschlag für einen Index über zwei Felder (den du als Plan für diese Abfrage direkt angeben könntest).

Deine neue Abfrage macht nicht viel Sinn, da sich die äußere Bedingung auch auf Feld2 bezieht.
Mit der Idee zweier geschachtelten Abfragen wurde versucht den SQL-Server auszutricksen und ein bestimmten Plan für die Abfrage zu erzwingen.
Das klappt aber scheinbar nicht, weil der die Abfrage vor der Ausführung optimiert und wieder umstellt.
Es ist auch sinnvoller den Ausführungsplan direkt anzugeben, als zu solchen Tricks zu greifen.
Andernfalls muss man sich den automatisch erzeugten Plan genau anschauen.
Gerade wenn mehere Index für ein Feld existieren, muss auch der Richtige und in der richtigen Reihenfolge genutzt werden.

[hoika]

Hallo,
ich würde mal ein Backup/Restore machen.

[Jasocul]

Folgender Vorschlag:

Code:

Select Max(Feld1)
from (select Feld1, Feld3
      from (Select Feld1, Feld2, Feld3
            from tabelle1
            where Feld2 = 'Schraube')
      where Feld3 <> 'klein' and Feld 3 <> '')

Das sollte dazu führen, dass im tiefsten Sub-Select der Index von Feld2 gezogen wird.
Dieser liefert nur 29 Datensätze.
Das Sub-Select in der darüber liegenden Ebene macht ein Full Table Scan über die 29 Datensätze. Dies darf nur ein paar ms dauern.
Selbst wenn dann immer noch 29 Datensätze übrig wären, kann das Max(Feld1) auf oberster Ebene ebenfalls nur ms dauern.

Sollte die gesamte Abfrage immer noch 6 Sekunden benötigen, ist entweder der Index für Feld2 nicht in Ordnung oder es liegt mMn nach eher ein technisches oder DB-Konfigurations Problem vor.

[Delphi.Narium]

Eventuell mal die Statistiken neu erstellen.

Und dann möchte ich bitte mal die Texte aus

SQL-Code:

select mon$sql_text, mon$explained_plan from MON$STATEMENTS
where mon$sql_Text like '%namedeinertabelle%'

sehen.

Das könnte dann z. B. so in der Art aussehen:

select max(id) from beipackzettel where kennungsart = 'Schraube' and kennung <> 'klein' and kennung <> ' '

SQL-Code:

Select Expression
    -> Aggregate
        -> Filter
            -> Table "BEIPACKZETTEL" Access By ID
                -> Bitmap
                    -> Index "IX_BEIPACKZETTEL_KENNUNGSART" Range Scan (full match)

Obwohl es hier einen Index für die Spalte "kennung" gibt, wird er nicht genutzt, ebensowenig wie der Index auf "id".

Eventuell den Inhalt von mon$explained_plan der unterschiedlichen Statements miteinander vergleichen. Führen sie überhaupt zu unterschiedlichen Ausführungsplänen?

[Dumpfbacke]

Zitat von Delphi.Narium:

Eventuell mal die Statistiken neu erstellen.

Und dann möchte ich bitte mal die Texte aus

SQL-Code:

select mon$sql_text, mon$explained_plan from MON$STATEMENTS
where mon$sql_Text like '%namedeinertabelle%'

sehen.

Das könnte dann z. B. so in der Art aussehen:

select max(id) from beipackzettel where kennungsart = 'Schraube' and kennung <> 'klein' and kennung <> ' '

SQL-Code:

Select Expression
    -> Aggregate
        -> Filter
            -> Table "BEIPACKZETTEL" Access By ID
                -> Bitmap
                    -> Index "IX_BEIPACKZETTEL_KENNUNGSART" Range Scan (full match)

Obwohl es hier einen Index für die Spalte "kennung" gibt, wird er nicht genutzt, ebensowenig wie der Index auf "id".

Eventuell den Inhalt von mon$explained_plan der unterschiedlichen Statements miteinander vergleichen. Führen sie überhaupt zu unterschiedlichen Ausführungsplänen?

Das funktioniert leider bei mir nicht. Ich bekomme diese Fehlermeldung.

SQL error code = -206.
Column unknown.
MON$EXPLAINED_PLAN.
At line 1, column 41.
Dynamic SQL Error.