4. Warum ist query 2 schneller?

Warum ist query 2 schneller?

  • ?

    Da ich bei mir einige Lücken in SQL feststellen konnte, habe ich mich mal mit SQL beschäftigt und gelesen, dass man Sub-Selects vermeiden soll und nach möglichkeit alles über JOINS löst.

    Das wollte ich nun mal testen. Also schnell 2 Queries geschrieben:

    SELECT * FROM Tabelle1 
INNER JOIN Tabelle2 ON Tabelle2.Tabelle1_DSN = Tabelle1.DSN
INNER JOIN Tabelle3 ON Tabelle2.Tabelle3_DSN = Tabelle3.DSN 
WHERE Tabelle3.Name = 'Name'

    SELECT * FROM Tabelle1
WHERE Tabelle1.DSN IN (SELECT Tabelle1_DSN FROM Tabelle2 
				INNER JOIN Tabelle3 On Tabelle2.Tabelle3_DSN = Tabelle3.DSN 
				WHERE Tabelle3.Name = 'Name')

    Beide queries machen das gleiche. Also geben das gleiche aus.
    Allerdings ist das zweite Query bedeutend schneller als das erste.

    Q1 00:00:00.1720172
    Q1 00:00:00.1160116
    Q1 00:00:00.2280228
    Q2 00:00:00.0930093
    Q2 00:00:00.0910091
    Q2 00:00:00.0930093

    So sehen die Zeiten aus. Eigentlich müsste das zweite wegen des Sub-Select doch langsamer sein?
    Hab sogar nach jedem Test den SQL Server neu gestartet, damit da keine Daten gecashed sind.
    Auch wenn man das zweite zuerst ausführt sieht es so aus.
    Egal welchen Namen ich suche, die ergebnisse sind immer ähnich.

    Die PS und FK sind jeweils uniqueidentifier.
    Der name ein varchar.

    Kann das jemand erklären?

    0

  • Nein die machen nicht das gleiche. Die 1. gibt viel mehr aus. Wenn Query 1 das "viel mehr" aus den Tabellen laden muss, Query 2 aber mit Spalten aus Indexen auskommt, dann ist auch klar warum Query 1 langsamer ist. Nen?

    Also

    SELECT * FROM Tabelle1
    =>
    SELECT Tabelle1.* FROM Tabelle1

    Dann nochmal vergleichen.

    Ansonsten Execution-Plan angucken wenn du wissen willst wieso die eine schneller ist als die andere.

    ps:

    Eigentlich müsste das zweite wegen des Sub-Select doch langsamer sein?

    Nein. SQL Datenbanken haben üblicherweise nen recht mächtigen Query-Optimizer.

    Dieser versteht natürlich dass das "IN" hier nicht pro Datensatz neu ermittelt werden muss, da es nicht vom aktuellen Datensatz abhängig ist. Im Endeffekt wird da meistens der selbe Execution-Plan draus wie wenn man es als JOIN schreibt.

    0
  • R

    Ein "IN" fasse ich nur mit Kneifzange an ⚠

    Hab damit schon mächtig Serverlast erzeugt, die mit einem JOIN nicht auftrat.

    0

  • Bist du der Sache auf den Grund gegangen warum das IN so langsam war?
    Wenn ja: Was war der Grund?
    Wenn nein: Wieso glaubst du dass es in anderen Situationen nicht auch genau andersrum ausgehen kann ( IN schneller als JOIN )?

    0
  • R

    hallo Hustbaer,

    ich beziehe mich auf ein solches Konstrukt.
    ... Where id IN (SELECT id ...)

    Wenn id als Primär-Key definiert ist mag "IN" als korrektes JOIN interpretiert werden. Wenn id jedoch nur als Index definiert ist konnte ich bei wenigen 100
    Datensätzen bei MySQL leicht 100% Last und ein load-average jenseits von Gut&Böse sicher reproduzieren. Bei MSSQL ist das Verhalten genauso feststellbar
    gewesen.

    Ich weiß auch von DBF-Datenbanken, dass hier kein Problem entsteht wenn id nur
    als Index definiert ist.

    Wenn ich so eine Abfrage sehe, ändere ich sie jedenfalls immer in ein JOIN um.

    0
  • C

    RED-BARON schrieb:

    Ein "IN" fasse ich nur mit Kneifzange an ⚠

    Hab damit schon mächtig Serverlast erzeugt, die mit einem JOIN nicht auftrat.

    Ach ich wette das schaffst du auch mit nem JOIN 😃 :p

    0

  • RED-BARON schrieb:

    hallo Hustbaer,

    ich beziehe mich auf ein solches Konstrukt.
    ... Where id IN (SELECT id ...)

    Wenn id als Primär-Key definiert ist mag "IN" als korrektes JOIN interpretiert werden. Wenn id jedoch nur als Index definiert ist konnte ich bei wenigen 100
    Datensätzen bei MySQL leicht 100% Last und ein load-average jenseits von Gut&Böse sicher reproduzieren. Bei MSSQL ist das Verhalten genauso feststellbar
    gewesen.

    Ich arbeite seit ca. 15 Jahren mit MSSQL, und ich hatte noch nie Probleme mit IN. Ob Primary-Key oder "einfach nur Index" spielt dabei keine Rolle.
    Was ich bisher beobachten konnte ist dass es - so lange beide Abfragen das selbe tun - egal ist, ob man es mit IN oder mit JOIN schreibt. Der Execution-Plan der erzeugt und ausgeführt wird ist identisch. Das ist auch nicht geraten, ich hab' mir das bei verschiedenen Beispielen angeguckt.

    Siehe z.B. auch
    http://explainextended.com/2009/06/16/in-vs-join-vs-exists/

    Ich bleibe dabei: dass IN bzw. EXISTS problematisch/böse/langsam/gefährlich sind, ist einfach veraltet. Wer sich deswegen graue Haare wachsen lassen und/oder zusätzlich Arbeit machen möchte soll es ruhig machen. Ich mach's nicht.

    0
  • C

    IN ist im Prinzip ja auch nichts anderes. Nur wird die Datenbank die Unterabfrage wahrscheinlich nur einmal ausführen, im Cache halten und den Rest dagegen laufen lassen. Das und dass lediglich ein Ergebnisfeld geliefert werden muss das der User nicht mehr zu sehen bekommt bringt wahrscheinlich etwas Performance gegenüber der klassischen Verknüpfung. Unnatürliche Serverlast kenne ich eh nur von rekursiven PL/SQL Funktionen oder sowas wie CONNECT BY auf Oracle (und auch nur wenn man es krass versaut :p )

    0
  • R

    Die beiden Abfragen "tun" zwar logisch das selbe, aber "tun" nicht die selben
    Spalten dafür verwenden. So gesehen tun sie nicht das selbe. Die langsame Variante
    nutzt Indizes mit extrem geringer Kardinalität, anstatt "id" mit wesentlich
    höherer.

    schnell:
    EXPLAIN SELECT tbl_pl_l.syncstate FROM tbl_pl_l JOIN tbl_pl_m ON tbl_pl_m.mid = tbl_pl_l.mpid AND tbl_pl_m.syncid=0 AND tbl_pl_m.syncstate=2 WHERE tbl_pl_l.syncid=0 AND tbl_pl_l.syncstate=3

    id select_type table type possible_keys key key_len ref rows Extra
    1 SIMPLE tbl_pl_m ref PRIMARY,syncid_syncts,syncstate syncstate 1 const 104 Using where
    1 SIMPLE tbl_pl_l ref mpid,syncid_syncts,syncstate mpid 4 sandbox.tbl_pl_m.mid 1 Using where

    langsam:
    EXPLAIN SELECT tbl_pl_l.syncstate FROM tbl_pl_l WHERE syncid=0 AND syncstate=3 AND id IN (SELECT id FROM tbl_pl_m WHERE syncid=0 and syncstate=2)

    id select_type table type possible_keys key key_len ref rows Extra
    1 PRIMARY tbl_pl_l ref syncid_syncts,syncstate syncid_syncts 4 const 1701 Using where
    2 DEPENDENT SUBQUERY tbl_pl_m index_subquery id,syncid_syncts,syncstate id 4 func 4 Using where

    mid ist PK der Mastertabelle ( unique: ja )
    mpid ist FK der Childtabelle ( unique: nein )

    id (Master/Child) entspricht der gleichen Beziehung wie mid/mpid jedoch aus
    anderer Quelle. Index ( unique: nein ), keine FK-Beziehung explizit definiert.

    Im Anwendungsfall steht für das Select jedoch ein Update im Bsp. abgefragte Spalte, was nicht gerade positiv wirkt.
    EXPLAIN funktioniert aber nur mit SELECT

    Das ein "IN" bei MSSQL ein JOIN erzeugt habe ich auch schon mitbekommen.
    Wenn in der 1. Variante statt JOIN ein IN bezogen auf mid/mpid eingesetzt
    wird, ist diese wohl auch nicht langsamer.

    ... könnte man meinen.

    EXPLAIN SELECT tbl_pl_l.syncstate FROM tbl_pl_l WHERE tbl_pl_l.mpid IN (SELECT tbl_pl_m.mid FROM tbl_pl_m WHERE tbl_pl_m.syncid =0 AND tbl_pl_m.syncstate =2) AND tbl_pl_l.syncid =0 AND tbl_pl_l.syncstate =3

    id select_type table type possible_keys key key_len ref rows Extra
    1 PRIMARY tbl_pl_l ref syncid_syncts,syncstate syncid_syncts 4 const 1701 Using where
    2 DEPENDENT SUBQUERY tbl_pl_m unique_subquery PRIMARY,syncid_syncts,syncstate PRIMARY 4 func 1 Using where

    4. Versuch, Abfrage umformuliert, dass der FK aus den PK gesucht wird
    EXPLAIN SELECT tbl_pl_m.syncstate FROM tbl_pl_m WHERE tbl_pl_m.mid IN (SELECT tbl_pl_l.mpid FROM tbl_pl_l WHERE tbl_pl_l.syncid=0 AND tbl_pl_l.syncstate=2) AND tbl_pl_m.syncid=0 AND tbl_pl_m.syncstate=3

    id select_type table type possible_keys key key_len ref rows Extra
    1 PRIMARY tbl_pl_m ref syncid_syncts,syncstate syncid_syncts 4 const 490 Using where
    2 DEPENDENT SUBQUERY tbl_pl_l index_subquery mpid,syncid_syncts,syncstate mpid 4 func 1 Using where

    und deswegen fasse ich ein "IN" weiterhin nur mit der Kneifzange an.
    Unbedingt dann, wenn eine Abfrage evtl. auch noch auf unterschiedliche
    Datenbank-Systeme abgelassen wird.

    Der Optimizer von MSSQL mag intelligenter sein ( wobei ich auch schon ungünstiges Laufzeitverhalten festgestellt haben will ).

    Ich erinnere mich an ein Kommentar und finde ihn auch im Quellcode
    // "DELETE FROM NWL WHERE ID IN ..." dauert ewig über OLDB. Also einzeln.
    Wobei beide Tabellen in dem Fall kein(!!!) PK hatten und ID ein nicht eindeutiger
    Index in der Childtabelle, Master ist ID eindeutig.

    Wenn überprüft würde das ein IN sich wie ein JOIN verhält auf allen verwendeten Systemen, dann gern.

    0
Anmelden zum Antworten