Firebird Leistung Index Max

[haentschman]

Guten Morgen alle...

Ich hätte da gern mal ein Verständnisproblem... 8-)

Gegebenheiten:

Tabelle mit 21 Mio Datensätzen

Delphi-Quellcode:

CREATE TABLE T_RECORD_DATA (
    F_PARAMETER_ID    ID /* ID = INTEGER NOT NULL */,
    F_TIMESTAMP_UNIX  INTEGER_NORMAL /* INTEGER_NORMAL = INTEGER NOT NULL */,
    F_POWER_STATE     INTEGER_NORMAL /* INTEGER_NORMAL = INTEGER NOT NULL */,
    F_VALUE           STRING10 /* STRING10 = VARCHAR(10) NOT NULL */

Indizies

Delphi-Quellcode:

ALTER TABLE T_RECORD_DATA ADD CONSTRAINT FK_T_RECORD_DATA_1 FOREIGN KEY (F_PARAMETER_ID) REFERENCES T_DEVICE_PARAMETERS (ID) ON DELETE CASCADE ON UPDATE CASCADE;

CREATE INDEX T_RECORD_DATA_IDX1 ON T_RECORD_DATA (F_TIMESTAMP_UNIX);

... alle Datensätze bei bestehendem Index eingefügt.

SQL

Delphi-Quellcode:

select first 10 * from T_RECORD_DATA where F_timestamp_unix = (select max(F_timestamp_unix) from t_record_data)

Problem:
Ausführungszeit = 36s 926ms ... absolut inakzeptabel :roll:

Leistungsanalyse:
siehe Bild und

Delphi-Quellcode:

------ Leistungsinformation ------
Prepare time = 31ms
Ausführungszeit = 36s 926ms
Avg fetch time = 3.692,60 ms
Current memory = 17.699.664
Max memory = 17.901.376
Memory buffers = 1.024
Reads from disk to cache = 82.953
Writes from cache to disk = 0
Fetches from cache = 43.527.070

Die Frage:
Warum greift der Index nicht? Wo ist der Denkfehler?

[mjustin]

Wie sieht der PLAN aus?

Wie ändert sich das Ergebnis wenn die Abfrage auf zwei Statements aufgeteilt wird?

Code:

1. select max(F_timestamp_unix) from t_record_data)
2. select first 10 from T_RECORD_DATA where F_timestamp_unix = :F_timestamp_unix_aus_1

[haentschman]

Danke... :wink:

Sooo...
SQL mit festem Wert

Delphi-Quellcode:

select first 10 * from T_RECORD_DATA where F_timestamp_unix = 1398999942

Delphi-Quellcode:

------ Leistungsinformation ------
Prepare time = 31ms
Ausführungszeit = 0ms
Avg fetch time = 0,00 ms
Current memory = 17.733.168
Max memory = 18.122.312
Memory buffers = 1.024
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 27

SQL Max

Delphi-Quellcode:

select max(F_timestamp_unix) from t_record_data

Delphi-Quellcode:

------ Leistungsinformation ------
Prepare time = 31ms
Ausführungszeit = 41s 559ms
Avg fetch time = 41.559,00 ms
Current memory = 17.726.240
Max memory = 18.122.312
Memory buffers = 1.024
Reads from disk to cache = 82.949
Writes from cache to disk = 0
Fetches from cache = 43.527.043

Also der Flaschenhals wäre gefunden. :shock: Da müßte aber der Index doch auch greifen oder?

[jobo]

Ich hab keine Ahnung wie/ob Firebird irgendwas Richtung Statistikanalyse macht.
Wenn ja, vielleicht nicht fertig geworden vor der ersten Abfrage, also im weitesten Sinne falsche, schlechte Statistiken.
Wenn nein, wenn die Werte in order of appearance in den Indexaufbau gehen ("bei bestehendem Index eingefügt.."), kann je nach internen Verfahren ein "schlechter " Index entstehen.
Unabhängig von allem zuvor genannten kann einfach der Inhalt des Feldes ungeeignet sein, viele gleiche Werte, sodass am Ende ein Fullscan daraus wird.
Da Du "select top 10" machst, scheint ja allein das Maximum schon relativ häufig vorhanden zu sein, so dass Du es einschränkst. Oder erhoffst Du Dir davon einfach nur den Server etwas zu schonen?

[jobo]

Zitat:

Zitat von haentschman (Beitrag 1257639)

Danke... :wink:

SQL Max

Delphi-Quellcode:

select max(F_timestamp_unix) from t_record_data

Delphi-Quellcode:

------ Leistungsinformation ------
Prepare time = 31ms
Ausführungszeit = 41s 559ms
Avg fetch time = 41.559,00 ms
Current memory = 17.726.240
Max memory = 18.122.312
Memory buffers = 1.024
Reads from disk to cache = 82.949
Writes from cache to disk = 0
Fetches from cache = 43.527.043

Also der Flaschenhals wäre gefunden. :shock: Da müßte aber der Index doch auch greifen oder?

Sorry, über rot gefahren.
Das ist schon mal was, aber kein Ausführungsplan.

[haentschman]

Danke an alle erst mal. :wink:

Gerade gefunden:
http://www.firebirdfaq.org/faq205/

Zitat:

Why doesn't MAX(Primary key field) use index?

It's because primary keys are enforced using ascending index and MAX only uses descending index. For example, if you try to get MIN(PK) it would use the index. If you often have queries using MAX, it is a good idea to create another, DESCENDING index on PK column.

Toll zu wissen...:roll:

Nach der Änderung und Neuberechnung des Index:
SQL

Delphi-Quellcode:

select max(F_timestamp_unix) from t_record_data

Delphi-Quellcode:

------ Leistungsinformation ------
Prepare time = 31ms
Ausführungszeit = 0ms
Avg fetch time = 0,00 ms
Current memory = 17.897.528
Max memory = 70.260.896
Memory buffers = 1.024
Reads from disk to cache = 5
Writes from cache to disk = 0
Fetches from cache = 9

:shock: Das hat mich einen ganzen Tag gekostet.

Danke für die Tipps zum Brille putzen. :thumb:

Zitat:

Da Du "select top 10" machst, scheint ja allein das Maximum schon relativ häufig vorhanden zu sein, so dass Du es einschränkst. Oder erhoffst Du Dir davon einfach nur den Server etwas zu schonen?

... in diesem Falle existieren je Timestamp zwischen 13 und 30 Parameter. War eher auch zum Testen.

[Union]

Ginge es nicht auch so (ob das dann schneller wäre ist eine andere Frage):

Code:

select first 1 skip ((select count(*)-1 from T_RECORD_DATA)) F_timestamp_unix from T_RECORD_DATA order by F_timestamp_unix

[haentschman]

Zitat:

------ Leistungsinformation ------
Prepare time = 31ms
Ausführungszeit = 1m 34s 630ms
Avg fetch time = 94.630,00 ms
Current memory = 17.634.080
Max memory = 74.215.232
Memory buffers = 1.024
Reads from disk to cache = 165.900
Writes from cache to disk = 0
Fetches from cache = 87.054.086

:P doch wohl nicht schnell. Macht nix.

[tsteinmaurer]

Firebird hat keine bi-direktionalen Indizes, darum brauchst du einen DESCENDING Index um ein MAX daraus bedienen zu können.

[haentschman]

Das hab ich auch nun schmerzlich festgestellt. :P
Für MIN gilt natürlich das ganze umgekehrt. Wie sieht die Konstellation für COUNT aus? Welche Variante greift da am besten? Der IBExpert zählt sich immer einen Wolf wenn ich mal zählen lasse. :roll:

[tsteinmaurer]

Mit COUNT(*) fragst du nach der Anzahl aller Datensätze. Warum soll da Firebird über einen Index gehen, wenn er sowieso alle Datensätze besuchen muss?

[haentschman]

:lol: Prinzipiell schon logisch. Hätte ja sein können daß da was integriert ist um, sagen wir mal, Blöcke zählen statt Datensätzen oder ein Counter je Tabelle mitgeführt welcher im Hintergrund aktualisiert wird.

[Dejan Vu]

Es wäre sinnvoll in *jedem* RDMBS einige kleine Optimierungen einzubauen.

Code:

select max(IndexSpalte1),min(IndexSpalte2), count(*) from ... <ohne where>

Diese drei Aggregatfunktionen könnten hochoptimiert ein Ergebnis liefern, solange in der Klausel keine Einschränkung angegeben ist. Ich finde es schade, das FB hier eine derartige Einschränkung hat. Aber selbst der SQL-Server ist zu blöd, bei einem Count(*) über eine Tabelle ohne WHERE in seiner Master-DB nachzuschauen. Bisher muss man sich in jedem RDMBS individuell einen abbrechen, um die Tabellengröße herauszubekommen. :wall:

Ach: Und soo selten sind diese Abfragen ja nu nich.

[Union]

Das kann allerdings beliebig komplex werden. Der Alias kann sich ja auch auf ein VIEW beziehen. Und falls es auch noch berechtigungsbasierte Sichtbarkeiten gibt, funktioniert das auch nicht mehr. Des weiteren müsste dieser Table-Recordcount ja auch noch in Transaktionen berücksichtigt werden. Trotzdem giebt es DB die das unterstützen, weil die Anzahl der Datensätze intern auch in den Metadaten gespeichert wird.

Dass der Index in Firebird allerdings nicht einfach "umgedreht" werden kann, empfinde ich schon als überflüssige Einschränkung (genau so wie auch die globale Sichrbarkeit von Indexnamen, aber das ist ein anderes Thema).

[Dejan Vu]

Zitat:

Zitat von Union (Beitrag 1257724)

Das kann allerdings beliebig komplex werden. Der Alias kann sich ja auch auf ein VIEW beziehen. Und falls es auch noch berechtigungsbasierte Sichtbarkeiten gibt, funktioniert das auch nicht mehr. Des weiteren müsste dieser Table-Recordcount ja auch noch in Transaktionen berücksichtigt werden. Trotzdem giebt es DB die das unterstützen, weil die Anzahl der Datensätze intern auch in den Metadaten gespeichert wird.

Profilabhängige Sichtbarkeit einzelner Records ist mir neu, es geht nur um Views und machbar ist das ja (wie man beim MAX sieht). Wieso beim Count(*) nicht?

Beim Optimieren geht es ja auch darum, bestimmte Sonderfälle optimiert auszuführen. Und die Anzahl der Datenstätze einer Tabelle sollte ein RDBMS schon kennen. Aber ist eh Wunschdenken, das das mal immer und überall schön schnell ist.

PS: Welches RDMBS optimiert das Count(*)?

[mkinzler]

Zitat:

Profilabhängige Sichtbarkeit einzelner Records ist mir neu

Es hängt aber vom Transaktionskontext ab.

[Union]

Zitat:

Zitat von Dejan Vu (Beitrag 1257726)

PS: Welches RDMBS optimiert das Count(*)?

z.B. ADS.

[jobo]

Zitat:

Zitat von Dejan Vu (Beitrag 1257726)

Und die Anzahl der Datenstätze einer Tabelle sollte ein RDBMS schon kennen. Aber ist eh Wunschdenken

Tja, also in einer Single User DB ist das ja sehr übersichtlich. Aber auch da geht es schon los, je nach isolation level...
Wie lange läuft die Transaktion schon?
Wieviel/welche insert, (Update bei max/min), delete wurden innerhalb der Transaktion bereits gefahren?
Wieviele waren es vorher?
Ergibt zusammen?
Commit oder Rollback?

Dann im Mehrbenutzer System ..

Wünschen kann man sich natürlich viel, besonders wenn man für eine Software Geld bezahlt. Das ist aber bei FB nicht so.
Aber vielleicht nehmen die ja Spenden und kleine Wunschlisten .. ;)

[Dejan Vu]

[QUOTE=jobo;1257731Wünschen kann man sich natürlich viel, besonders wenn man für eine Software Geld bezahlt. Das ist aber bei FB nicht so.[/QUOTE]
Ich wünschte mir das nicht bei Firebird (weil open soße und eh nicht mein Tool) sondern bei den angeblich so perfekten teuren RDMBS. Und wenn ADS das kann... geht es also, egal ob mit oder ohne Transaktion oder wie auch immer. Wenn man 'select max(indexSpalte) from Tabelle' optimieren kann, dann ja wohl auch 'select count(*) from Tabelle'. Aber wir schweifen ab.

FB hat hier einen kleinen Schwachpunkt. Muss man wissen, dann krepelt man sich einen Extra-Index dafür und hat die Sache vergessen.

[tsteinmaurer]

Code:

Aber vielleicht nehmen die ja Spenden und kleine Wunschlisten ..

Die Firebird Foundation nimmt auch grosse Spenden und auch grosse Wunschlisten. Das SELECT COUNT(*) wirst du vermutlich aber trotzdem nicht schneller bekommen. ;-)

Mich würde interessieren, für was du ein COUNT(*) brauchst? Um zu überprüfen, ob eine Tabelle leer ist? Oder doch die exakte Anzahl? Oder darf es eine ungefähre Anzahl sein?

[Dejan Vu]

Zitat:

Zitat von tsteinmaurer (Beitrag 1257736)

Mich würde interessieren, für was du ein COUNT(*) brauchst?

DevExpress Paging Mode (oder wie das heißt). Die Komponente muss wissen, wie viele Datensätze in der Tabelle/View sind. In die Komponente kann ich eine beliebige Query stecken (bzw. eine View) ergo schmeißt die Kompo ein 'select count(*) from <View>' raus.

Das eine Paging-Komponente aber eigentlich gar nicht wissen muss, wie viele Datensätze es insgesammt zu 'pagen' gibt, ist klar, aber dieses Teil macht es nun mal.

Wobei.. Wenn die die einzigen sind (Die DevExpress-Leute), dann warte ich eben ;-)

[tsteinmaurer]

Code:

DevExpress Paging Mode

Habe DevExpress nicht im Einsatz, aber dann ist das für sehr große Tabellen nicht geeignet. NextDBGrid braucht auch eine akkurate (und nicht nur die bereits gefetchten) Datensatzanzahl, um z.b. die Scrollbars eines Grids entsprechend anzuzeigen bzw. zu positionieren.

[Union]

Der Servermode von DevExpress ist sowieso nicht so ganz ernst zu nehmen. Sie versuchen zwar, je nach Zieldatenbank eine möglichst optimale Methode zur Ermittlung der Datensatzanzahl zu verwenden, aber das Ganze basiert komplett auf ADO. Man muss also auch wenn man ansonsten native Treiber verwendet immer parallel eine ADO Connection für das Grid aufmachen.