Millionen Datensätze SELECT per Index

[Gruber_Hans_12345]

Hallo

Ich habe eine Frage zur Optimierung von Indexen
In der Tabelle werden zu einzelnen Geräte Buchungen gespeichert ... die sind nach ID, sowolh START als auch ENDE sortiert

In der Tabelle sind ca 8 Millionen Einträge drinnen, für mein Gerät was ich Abfragen will sinds dann 900000 Einträge. Von diesen werden dann 10-100 Datensätze geladen

Nun starte ich eine Abfrage um die Daten eines Tages zu erhalten :

Code:

SELECT * FROM STEMPELUNGEN WHERE GERAETEID = 1 AND "START" > '10.1.2013' AND ENDE < '11.1.2013'

Das Ergebnis dauert nun immer so um die 500 ms .. ich möchte das nun optimieren, habe mal einen Haufen Indexe angelegt ...
1.) ENDE ASC
2.) ENDE DESC
3.) START DESC
4.) START ASC
5.) GERAETEID ASC

der Plan sieht auch aus als ob die verwendet werden, nur glaub ich das man dass noch optimieren kann oder?

Der Plan :

Code:

PLAN (STEMPELUNGEN INDEX (STEMPELUNGEN_ENDE, STEMPELUNGEN_START, STEMPELUNGEN_GERAETEID))

Hat wer eine Idee, was ich da noch probieren kann?
Ich möchte das ganze dann eben dynamisch jedesmal nachladen, und da stört diese 500ms doch ...

besten dank im vorhinein

[Gruber_Hans_12345]

Hmmm was mir gerade aufgefallen ist

wenn ich NUR das Feld START oder NUR das Felde ENDE verwende dann, gehts schnell (Also unter 40 ms)
ALSO

Code:

SELECT * FROM STEMPELUNGEN WHERE GERAETEID = 1 AND "START" > '10.1.2013' AND "START" < '11.1.2013'

Code:

SELECT * FROM STEMPELUNGEN WHERE GERAETEID = 1 AND "ENDE" > '10.1.2013' AND "ENDE" < '11.1.2013'

nur eben die Mischung zwischen Start und Ende dauert lange (was ich aber fast brauche, um sicherzustellen das ich den Zeitbereich und die Stempelungen die auf der einen oder anderen Seite drübergehen auch erwische)

[Sir Rufo]

Hast du auch schon einen kombinierten Index mit START und ENDE versucht?

[Bernhard Geyer]

Wie wäre es mit einen Index der GeräteId, Start und Ende beinhaltet?

Und viele Index ist nicht in allen Fällen besser. Ein Index ist dann besonders schnell wenn er komplett in den Speicher geladen werden kann.
Hast du viele und der Server nicht genügend RAM werden die Indexe immer wieder aus dem RAM entfernt und müssen neu geladen werden.

[Gruber_Hans_12345]

Hmmm

hab nun mal getestet einen Index mit ON STEMPELUNGEN(GERAETEID,START,ENDE)

nun verwendet er diesen neuen Index und den ENDE Index... dauert aber trotzdem noch > 500 ms

hab dann auch einen Index mit (START,ENDE), den verwendet er gar nicht

[himitsu]

Gibt es im Firebird auch verschiedene Arten von Indize?

Einige Arten sind eventuell für größer/kleiner-Vergleiche nicht so gut zu verwenden, vorallen bei den zusammengesetzten Varianten.

[Gruber_Hans_12345]

Nein,es gibt nur eine Art von Index

Das was ich wundertist einfach das das es ein problem gibt wenn ich START und ENDE mische
jeweils nur Start oder nur Ende geht schnell ...

[Dejan Vu]

Überlege Dir einfach mal, wie Du die Datensätze schnell finden kannst und wie nicht.
Drei Index jeweils über eines der drei Felder wird zu folgendem Suchalgorithmus führen:
1. Suche den nächsten Eintrag mit der ID (durch den Index das geht sehr schnell).
2. Lade den Record
3. Prüfe, ob START und ENDE passen (einen anderen Index zu verwenden bringt ja nichts, denn der Record ist ja in #2 schon ausgewählt)
4. Wenn START und ENDE passen, kopiere den Record in die Ergebnismenge
5. GOTO 1

Je nach Statistik kann es sein, das die Felder getauscht werden, d.h. in #1 könnte auch ein Record mit passendem START-Wert gesucht werden und anschließend auf ID und ENDE abgefragt.

Bei kombinierten Abfragen wirst Du auch kombinierte Indexe nehmen müssen. Da Du jedoch nicht auf Eindeutigkeit prüfst, kannst Du höchstens 2 Felder kombinieren. Rein theoretisch bringt auch ein Index über drei Werte etwas, aber hier scheint FB nicht schlau genug zu sein. Der Geschwindigkeitsvorteil wäre der, das z.B. sehr schnell ein Record mit passender ID und passendem START-Wert gefunden wird und für die Prüfung, ob ENDE passt, der Record nicht extra geladen werden muss.

Wenn Du deine Überlegung auf die gesamte Datenmenge ausweitest, könnte man 'sofort' alle Kandidaten mit passender ID aus dem Index herauslesen. Weil alle Kandidaten im ID-Index ja direkt nebeneinander liegen.

Auch bei einem kombinierten Index über ID und START (oder ENDE) liegen alle Kandidaten mit passender ID und START direkt nebeneinander und daher geht das sehr schnell. Bei einem kombinierten Index über START und ENDE wirst Du das so nicht finden. Dieser Index bringt nur bei einer Abfrage auf START=<Zeit> etwas.

Bei deiner Abfrage ist es nicht möglich, einen Index zu verwenden, der alle drei Felder kombiniert, weil die Kandidaten ja niemals direkt nebeneinander liegen.

[Gruber_Hans_12345]

So habs nun

Eine Index auf GERAETEID,START
und einen auf GERAETEID,ENDE

nun läufts

wobei komischer weise der PLAN nur anzeigt das er den ersten Index (GERAETEID, START) verwendet

wenn ich den zwiten Index dann aber lösche, dann dauerts wieder ewig ...

aber zumindest passt es nun