SQL-Abfrage zu langsam - Tipps zur Verbesserung?

Klaphimin

Neuer Benutzer
Beiträge
3
Hallo an alle!

Ich habe eine größere Bibliotheksverwaltungsdatenbank (Firbird-basiert) mit folgenden - auf die wichtigsten Spalten reduzierte - Tabellen (mögliches Beispiel darunter):

Medien (ID, Autor, Titel, ISBN, Signatur)
38, Goethe, Faust, 12345, 05Y2-LM-DE.

MedienExemplare (ID, Medium, Status)
798, 38, 2 (=> Status 2 bedeutet "ausgeliehen")
799, 38, 0 (=> Status 0 bedeutet "nicht ausgeliehen")

Es gibt 2 Exemplare vom Medium 38, weil es 2x die gleiche Faust-Ausgabe in der Bibliothek gibt. Die Spalte "Medium" in der Tabelle "Medienexemplare" ist also ein Fremdschlüssel und verweist auf die ID in der Tabelle "Medien".

Ich möchte nun folgende Ausgabe haben:
Autor | Titel | ISBN | verfügbar | ausgeliehen
------------------------------------------------------------
Goethe | Faust | 12345 | 1 | 1


Meine Lösung
funktioniert, erscheint mir aber zu komplex und ist überaus langsam:

PHP:
$sql = 
"SELECT DISTINCT frei.Autor, frei.Titel, frei.ISBN, frei.verfuegbar, weg.verliehen
         FROM
               (  SELECT m.ID, m.Autor, m.Titel, m.ISBN, COUNT(m.ISBN) AS verfuegbar
                   FROM Medien m INNER JOIN MedienExemplare ex
                   ON m.ID = ex.Medium
                   WHERE ex.Status = 0
                   AND m.Signatur Like '05Y2-LM DE%'
                   OR m.Signatur Like '05Y2-LM-DE%'
                   GROUP BY m.ID, m.Autor, m.Titel, m.ISBN   ) frei

        FULL JOIN

               (  SELECT ex.Medium, COUNT(m.ISBN) AS verliehen
                   FROM Medien m INNER JOIN MedienExemplare ex
                   ON m.ID = ex.Medium
                   WHERE ex.Status = 2
                   AND m.Signatur Like '05Y2-LM DE%'
                   OR m.Signatur Like '05Y2-LM-DE%'
                   GROUP BY ex.Medium, m.ISBN                      ) weg
        ON frei.ID = weg.Medium
        ORDER BY frei.Titel
";

Die Signatur kann mal hinten "LM-DE" oder "LM DE" lauten, je nachdem, wie inkonsistent leider das Buch in die Datenbank aufgenommen wurde.

Es erscheint mir irgendwie unlogisch, 2x fast das gleiche abzufragen und einmal eine Tabelle zu kreieren, in der am Ende eine Spalte "verfügbar" ergänzt ist und einmal eine zweite neue Tabelle mit einer letzten Spalte "verliehen" und diese beiden neuen Tabellen noch einmal zu "full joinen".

Ich bin noch zu unerfahren mit effektiven SQL-Abfragen und freue mich sehr über Tipps und Hilfen!!

DANKE!
 
Werbung:
was sagt denn EXPLAIN dazu? Was hast Du für Indexe?

Die Counts() könntest Du evtl. via count(case when ... then ... else ... end) oder count() filter (where ...) conditionell in einer Abfrage machen. Aber das ist grad nur ein Schnellschuß. Was für Datenmengen liegen denn vor, und was ist "überaus langsam"?
 
ohne Daten:

Code:
test=*# \d medien
                                Tabelle »public.medien«
  Spalte  |   Typ   | Sortierfolge | NULL erlaubt? |            Vorgabewert             
----------+---------+--------------+---------------+------------------------------------
 id       | integer |              | not null      | nextval('medien_id_seq'::regclass)
 autor    | text    |              |               |
 titel    | text    |              |               |
 isbn     | isbn    |              |               |
 signatur | text    |              |               |
Indexe:
    "medien_pkey" PRIMARY KEY, btree (id)

test=*# \d medienexemplare
                                Tabelle »public.medienexemplare«
 Spalte |   Typ   | Sortierfolge | NULL erlaubt? |                 Vorgabewert                 
--------+---------+--------------+---------------+----------------------------------------------
 id     | integer |              | not null      | nextval('medien_exemplare_id_seq'::regclass)
 medium | integer |              |               |
 status | integer |              |               |
Indexe:
    "medien_exemplare_pkey" PRIMARY KEY, btree (id)

test=*# SELECT m.ID, m.Autor, m.Titel, m.ISBN, sum(case when status = 0 then 1 else 0 end) AS verfuegbar, sum(case when status = 2 then 1 else 0 end) as verliehen FROM Medien m INNER JOIN MedienExemplare ex ON m.ID = ex.Medium WHERE ex.Status in (0,2) AND m.Signatur Like '05Y2-LM DE%' OR m.Signatur Like '05Y2-LM-DE%' GROUP BY m.ID, m.Autor, m.Titel, m.ISBN;
 id | autor | titel | isbn | verfuegbar | verliehen
----+-------+-------+------+------------+-----------
(0 Zeilen)

test=*#


Ohne Daten, ohne Indexe.
 
Code:
test=*# \d medien
                                Tabelle »public.medien«
  Spalte  |   Typ   | Sortierfolge | NULL erlaubt? |            Vorgabewert             
----------+---------+--------------+---------------+------------------------------------
 id       | integer |              | not null      | nextval('medien_id_seq'::regclass)
 autor    | text    |              |               |
 titel    | text    |              |               |
 isbn     | isbn    |              |               |
 signatur | text    |              |               |
Indexe:
    "medien_pkey" PRIMARY KEY, btree (id)
    "idx_medien_signatur" btree (signatur)

test=*# \d medienexemplare
                                Tabelle »public.medienexemplare«
 Spalte |   Typ   | Sortierfolge | NULL erlaubt? |                 Vorgabewert                 
--------+---------+--------------+---------------+----------------------------------------------
 id     | integer |              | not null      | nextval('medien_exemplare_id_seq'::regclass)
 medium | integer |              |               |
 status | integer |              |               |
Indexe:
    "medien_exemplare_pkey" PRIMARY KEY, btree (id)
    "idx_ex_medium" btree (medium)
    "idx_ex_status" btree (status)

test=*# explain analyse SELECT m.ID, m.Autor, m.Titel, m.ISBN, sum(case when status = 0 then 1 else 0 end) AS verfuegbar, sum(case when status = 2 then 1 else 0 end) as verliehen FROM Medien m INNER JOIN MedienExemplare ex ON m.ID = ex.Medium WHERE ex.Status in (0,2) AND m.Signatur Like '05Y2-LM DE%' OR m.Signatur Like '05Y2-LM-DE%' GROUP BY m.ID, m.Autor, m.Titel, m.ISBN;
                                                                  QUERY PLAN                                                                 
----------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=16.33..16.34 rows=1 width=80) (actual time=0.036..0.036 rows=1 loops=1)
   Group Key: m.id, m.autor, m.titel, m.isbn
   ->  Nested Loop  (cost=0.25..16.31 rows=1 width=80) (actual time=0.024..0.027 rows=2 loops=1)
         ->  Index Scan using medien_pkey on medien m  (cost=0.12..8.14 rows=1 width=108) (actual time=0.014..0.015 rows=1 loops=1)
               Filter: ((signatur ~~ '05Y2-LM DE%'::text) OR (signatur ~~ '05Y2-LM-DE%'::text))
         ->  Index Scan using idx_ex_medium on medienexemplare ex  (cost=0.13..8.15 rows=1 width=8) (actual time=0.007..0.008 rows=2 loops=1)
               Index Cond: (medium = m.id)
               Filter: (((status = ANY ('{0,2}'::integer[])) AND (m.signatur ~~ '05Y2-LM DE%'::text)) OR (m.signatur ~~ '05Y2-LM-DE%'::text))
 Planning time: 0.241 ms
 Execution time: 0.103 ms
(10 Zeilen)

test=*# SELECT m.ID, m.Autor, m.Titel, m.ISBN, sum(case when status = 0 then 1 else 0 end) AS verfuegbar, sum(case when status = 2 then 1 else 0 end) as verliehen FROM Medien m INNER JOIN MedienExemplare ex ON m.ID = ex.Medium WHERE ex.Status in (0,2) AND m.Signatur Like '05Y2-LM DE%' OR m.Signatur Like '05Y2-LM-DE%' GROUP BY m.ID, m.Autor, m.Titel, m.ISBN;
 id | autor  | titel | isbn | verfuegbar | verliehen
----+--------+-------+------+------------+-----------
 38 | Goethe | Faust |      |          1 |         1
(1 Zeile)

test=*#

isbn habe ich NULL gelassen, weil ich ja den ISBN-Datentyp verwende, der Werte noch auf Validität prüft und ich grad keine valide ISBN zur Hand habe ...
(außerdem habe ich den FK nicht gesetzt, aber das ist irrelevant hier)

Jedenfalls - er verwendet Indexscans, und sollte damit auch bei größeren Datenmengen schnell sein.
 
Grandios!!!! Das ist schon die Lösung. Vielen vielen Dank, jetzt spuckt meine php-Seite in einer halben Sekunde (geschätzt) eine sehr große Datentabelle aus.

"explain" kannte ich noch nicht, Asche auf mein Haupt... Daher die Frage: Wie wende ich explain analyse an?
Ich habe naiv mich mit der DB via Linux-Konsole verbunden:

Code:
...@nas:   isql-fb
SQL>       CONNECT "<Pfad zur DB> user "SYSDBA" password "masterkey" ";
CON>       explain analyse SELECT <usw. der ganze SQL-Befehl von oben > ;

Es passiert allerdings nichts, keine Fehlermeldung ("nichts" heißt, danach steht wieder CON>, als würde auf eine Eingabe gewartet werden). "Explain" musste ich vorher über sudo apt install explain installiert, weil das Package nicht bekannt war.
 
ergänzend zu @castorp (btw, schön, daß Du nun auch her bist ;-) ), EXPLAIN kennt (mehr oder weniger) jede DB. Das zeigt den Ausführungsplan an. In der Form EXPLAIN ANALYSE zeigt es (in PostgreSQL) nicht nur den Plan an, sondern führt die Abfrage auch aus und zeigt, neben den geschätzten Werten, auch die realen Werte an. Also, was es schätzt (Anzahl Ergebnisszeilen) und was real rauskommt. Das ist ein extrem wichtiges Merkmal, um z.B. erkennen zu können, wo der Planner sich irrt.

Also, als Beispiel, der Planner schätzt, daß bei einer Tabelle, die 900.000.000 Zeilen hat, mit der Where-Condition 600.000.000 Zeilen rauskommen. Dann wird er sich zu einem Sequentiell-Scan entscheiden, weil er ja eh quasi die ganze Tabelle liefern muß. Wenn dann real nur 12 Zeilen im Result sind - dann hat er sich MASSIV verschätzt. So gesehen kürzlich bei einem meiner Kunden. Das sind dann so die Probleme, die man zu lösen hat. Zum Glück hat PostgreSQL dazu passende Tools - EXPLAIN ANALYE ist eines, Anpassungen des STATISTIC TARGET ein anderes.

tl;dr

EXPLAIN ANALYSE ist meines Wissens nach eine PostgreSQL-Spezialität. Willst Du sowas haben, dann nutze halt PostgreSQL ;-)
 
Werbung:
DANKE für die ausführlichen Antworten. Jetzt bin ich deutlich deutlich schlauer! Mit PostgreSQL habe ich davor gearbeitet, aber die Bibliothekssoftware arbeitet auf Firebird - ist nicht auf meinem Mist gewachsen ;-)

Aus dieser Firebird-Tatsache, aus dem ich definitiv nicht herauskomme - resultiert noch ein weiteres Problem.

Altes leidiges Thema der Kodierungen. "show database" gibt mir als default character set "WIN1251" aus.
Laut Firebird-Dokumentation (DATABASE) wird das automatisch so eingerichtet.

Umlaute werden in meiner php-Ausgabe mit Fragezeichen dargestellt. Ändere ich den charset bei der Verbindung auf utf8, werden die Umlaute durch kyrillische Buchstaben ersetzt.

PHP:
$charset = "utf8";
$connect = ibase_connect($path, $user, $password, $charset);

Die Suche im Netz ergab, dass eine Lösung darin bestehen könnte, eine neue DB zu erstellen - mit richtiger Kodierung - und die alte in die neue zu kopieren. Uff... das wäre mein letzter Ausweg, den ich nicht wirklich gehen will, klingt nach einer längeren Aufgabe. Und irgendwie in Systemtabellen rumzuspielen habe ich Angst.

Gibt's noch irgendeine "unkomplizierte" Lösung? Finde ja äußerst merkwürdig, dass Firebird per default "WIN1251" nutzt...

Danke!
 
Zurück
Oben