WHERE IN endet in einem Timeout

Forum für neue Firebird-Anwender.

Moderator: thorben.braun

Antworten
Oskar
Beiträge: 3
Registriert: Do 11. Feb 2021, 20:30

Hallo,

ich stehe gerade ein wenig auf dem Schlauch. Ich habe lange nicht mehr mit SQL gearbeitet und bin jetzt ganz neu auf Firebird gestoßen. Ich habe eine Datenbank mit 7,5 Millionen Zeilen. Auf der Festplatte ist die Datei 8 GB groß. Also erwarte ich, dass Abfragen vielleicht nicht immer die schnellsten sind. Die Spalten, welche ich abfrage, haben alle einen Index bekommen

Folgendes Query braucht ca. 5 Sekunden für eine Abfrage. Es liefert genau ein Ergebnis. So weit OK. Gibt es Möglichkeiten das noch zu beschleunigen? Oder ist die Zeit am technischen Limit?

Code: Alles auswählen

SELECT FIRST 1 ID FROM MYDATA ORDER BY ID DESC
Folgendes Query braucht 0,03 Sekunden. Ich war erstaunt, dass es doch so schnell geht bei der Datenmenge:

Code: Alles auswählen

SELECT * FROM MYDATA 
WHERE ID = ( 7530563 )
Wenn ich jetzt beide Query zusammen vereine sieht es so aus, dass die Abfrage wieder ca. 5 Sekunden dauert. Was verständlich ist.

Code: Alles auswählen

SELECT * FROM MYDATA 
WHERE ID = ( SELECT FIRST 1 ID FROM MYDATA ORDER BY ID DESC)
Jetzt möchte ich zwei Datensätze haben. Funktioniert wie erwartet.

Code: Alles auswählen

SELECT FIRST 2 ID FROM MYDATA ORDER BY ID DESC
Die nächste Abfrage ist auch OK. Funktioniert wie erwartet.

Code: Alles auswählen

SELECT * FROM MYDATA 
WHERE ID = ( 7530563, 7530773 )
Wenn ich jetzt aber beide Abfrage Verschachtel, geht es nicht mehr. Die CPU-Last geht hoch, aber es passiert nichts. Auch kein Timeout. Wobei ich nach ca. 60 Sekunden immer manuell abgebrochen habe. Warum geht das folgende Query nicht?

Code: Alles auswählen

SELECT * FROM MYDATA 
WHERE ID IN ( SELECT FIRST 2 ID FROM MYDATA ORDER BY ID DESC)
bfuerchau
Beiträge: 484
Registriert: Mo 7. Mai 2018, 18:09
Kontaktdaten:

Das liegt in der Natur der Sache, dass Subselects und Joins je Zeile ausgeführt werden.
Nur warum schachtelst du 2 Abfragen ineinander wenn das Ergebnis auch direkt mit 1 Select erreichbar ist?
Ist das nur ein rumprobieren?
Auch wenn vieles möglich ist, sollte man bei Abfragen generell den performantesten nehmen. U.U. kannst du das besser machen, wenn dein Index auch absteigend ist.
Denn ein "Order by X Desc" kann durch einen "create descending index" beschleunigt werden.
Oskar
Beiträge: 3
Registriert: Do 11. Feb 2021, 20:30

>> Denn ein "Order by X Desc" kann durch einen "create descending index" beschleunigt werden.
Danke, das ist eine gute Idee, ich werde es ausprobieren. 99% meiner Abfragen sind immer DESC, da ist ein Index in DESC wirklich besser.

>> Auch wenn vieles möglich ist, sollte man bei Abfragen generell den performantesten nehmen.
Ja. Das wird natürlich auch gemacht.

>> Nur warum schachtelst du 2 Abfragen ineinander wenn das Ergebnis auch direkt mit 1 Select erreichbar ist?
>> Ist das nur ein rumprobieren?
Ja.

>> Das liegt in der Natur der Sache, dass Subselects und Joins je Zeile ausgeführt werden.
Tun sie eben nicht. Das Query lässt Firebird ins Timeout Crashen.

Die Frage ist: Warum crasht Firebird ins Timeout und was kann ich dagegen machen.
bfuerchau
Beiträge: 484
Registriert: Mo 7. Mai 2018, 18:09
Kontaktdaten:

Du musst einfach mal rechnen:
Für 7,5 Millionen Sätze wird jedes mal der "... in (select ....)" mit 7,5 Millionen Sätzen durchgeführt.
Ein First 1 scheint in soweit optimiert zu sein, dass ein ASC-Index von hinten verwendet wird, das habe ich auch schon erlebt. Bei einem First 2 scheint die Optimierung bzgl. Indexnutzung dahin zu sein.
Und was meinst du wie lange wohl "7,5 Mio * 7,5 Mio" Zugriffe dauern?
Dass die FB alledings crasht verstehe ich nicht, es könnte allerdings sein, dass der TEMP-Speicher (Einstellung in der firebird.config) für Sortierungen u.U. zu klein ist.
Lege mal ein anderes Verzeichnis mit ganz viel Platz an und beobachte das Anwachsen der Temp-Dateien während der Ausführung.

Ich nutze für Analysen noch immer die alte noch kostenlose "IBExpert-Personal Edition", nicht mehr als Download zu haben, die mir nach der Ausführung die verwendeten Indizes anzeigt.
Der Windows-Net-Treiber hat i.Ü. ein ähnliches Feature, die verwendeten Indizes abzufragen.
Oskar
Beiträge: 3
Registriert: Do 11. Feb 2021, 20:30

>> Und was meinst du wie lange wohl "7,5 Mio * 7,5 Mio" Zugriffe dauern?

Warum 7,5 Millionen MAL 7,5 Millionen?

Das innere Select in den Klammern muss doch nur 1 mal ausgeführt werden. Es liefert als Ergebnis 2 Zeilen, bzw. zwei Zahle. Diese 2 Zahlen werden wird für jetzt für das äußere Select verwendet.

Nun ja, wenn Firebird da zu schlecht für ist, mache ich zwei Querys nacheinander. Schade, aber geht natürlich problemlos. Das erste Query schreibt sein Ergebnis in einen String und der String wird in das 2. Query eingebaut. Fertig.

Es gibt da auch ein Limit. Auch schade. Wenn ich 1 unter Limit bin, funktioniert das Query sehr gut.


So sehen die Daten in der Datenbank aus:

Code: Alles auswählen

ID	Ort		Temperatur	Hash	DatumZeit
1	Hamburg		3		oxk	12.02.2020 13:00
2	Hannover	6		rdq	12.02.2020 11:10
3	Hannover	7		tfq	12.02.2020 11:20
4	Hamburg		3		irx	12.02.2020 13:30
5	Hamburg		1		vhw	12.02.2020 13:40
Und ich möchte als Ergebnis die Zeile Nummer 3 und 5 haben, weil es das jeweils letzte Ergebnis von Hamburg und Hannover ist.

Mit GROUP BY Ort und max(id) bekomme ich immer die größe ID von jedem Ort.
Sobald ich die IDs habe, kann ich die Zeile komplett auslesen mit WHERE ID IN (3,5)
Problem: Es sind mehr Orte als erlaubt sind in den Klammern.
bfuerchau
Beiträge: 484
Registriert: Mo 7. Mai 2018, 18:09
Kontaktdaten:

Zur ersten Frage:
So funktioniert SQL eben nicht.
Es wird immer Top-Down gearbeitet und es werden keine realen temporären Tabellen erstellt.
Der Hauptselect liest jede Datenzeile, da ja keine weitere Where-Bedingung enthalten ist. Je Zeile wird nun der Subselect gelesen. Mangels Indizierung wegen der absteigenden Sortierung werden nun alle 7,5Mio Zeilen gelesen um deine 2 größten zu ermitteln. Die Sortierung erfolgt in Temp-Tabellen.
Wenn dann die 2 größten ermittelt sind, wird gegen die äußere Id verglichen.
Somit müssen 7,5Mio mal die 2 größten Id's aus 7,5Mio Zeilen ermittelt werden.
Selbst bei einem Desc-Index müssen mindesten 7,5Mio * 2 Zugriffe erfolgen.

Aber du solltest dich mal intensiver mit SQL-Logik auseinander setzen:

Bei einem absteigenden Index geht ja auch folgendes:

Select first 2 b.*
from MyData a
inner join MyData b on a.ID = b.ID
order by ID desc

Hier hilft ein desc Index.

Ähnliches kannst du auch mit deinem 2. Problem machen:

select * from (
Select Ort, Max(DatumZeit) DatumZeit
from MyTable
group by Ort
) a
inner join MyTable b on a.Ort = b.Ort and a.DatumZeit = b.DatumZeit

EIn Index über Ort, DatumZeit wäre da hilfreich.

Man kann sehr viel mit derived Tables machen.
Alternativ gibt's noch die CTE's (Common Table Expression):

with
MaxOrt as (
Select Ort, Max(DatumZeit) DatumZeit
from MyTable
group by Ort
)
Select a.*
from MyTable a
inner join MaxOrt b on a.Ort = b.Ort and a.DatumZeit = b.DatumZeit

Andere SQL-Datenbanken können das auch nicht sehr viel besser als die Firebird.
Der berühmte SQL-Server kann zwar durch Zusatzprodukte (SSAS/SSIS/PowerBI/...) erheblich mehr. Die Firebird ist aber in der Transaktionsverarbeitung beim Lesen und Einfügen um Faktoren schneller als der SQL-Server.

Besser sind da nur InMemory-DB's, da diese die Plattenzugriffe sparen und daher auch häufig mit Hashtables selten mehr als 1 Zugriff benötigen.
Antworten