4. Wie Daten aus schnell db lesen?

Wie Daten aus schnell db lesen?


  • Hallo,

    wie löst ihr das Problem, Daten, die über mehrere Tabellen verstreut sind und jeweils mehrere Zeilen haben, schnell aus einer db zu lesen?

    Nehmen wir als Beispiel mal diese Datenstrukturen:

    struct Tuple
{
   string Key;
   string Val;
};

struct Detail1
{
...
};

struct Detail2
{
   ...
   vector<Tuple> Tuples;
};

struct Object
{
   vector<Detail1> Details_1;
   vector<Detail2> Details_2;
};

    Der naive Ansatz sieht so aus und ist ausgesprochen langsam, weil sehr viele Abfragen notwendig sind.

    vector<Object> vo = query_objects();
for( Object& Obj : vo )
{
   Obj.Details_1 = query_details_1( Obj.id );
   Obj.Details_2 = query_details_2( Obj.id );
   for( Detail& dt : Obj.Details_2 )
   {
     dt1.Tuples = query_tuples( dt1.id );
   }
}

    Wie löst man sowas geschickt?

    0
  • ?

    wenn es um SQL geht: Subselects? Join?

    0
  • M

    Ja, möglichst versuchen, alles in einen Join zu packen. Oder schauen, ob du ein ORM findest, das schlau genug ist, das selber zu machen. Ich hab sowas vor zig Jahren mal in C# geschrieben. Mit dem naiven Absatz angefangen, der sehr langsam war. Dann selber sowas wie ein ORM geschrieben und das intern so optimiert, dass ich dann möglichst komplexe Queries mit vielen Joins zusammengebaut habe, war viel schneller.

    0

  • Ja, ich nehme dafür auch üblicherweise JOINs.

    Bzw. halt grosse SQL Statements die sich alles nötige möglichst auf einmal aus der DB ziehen. Ob jetzt über JOINs oder Sub-SELECTs macht dabei keinen grossen Unterschied (zumindest nicht auf vernünftigen Datenbanken, die Sub-SELECTs intern zu JOINs optimieren können).
    Notfalls auch nen Temp-Table oder ne Table-Variable im SQL Batch erstellen und darin erstmal alles zusammenbauen was man braucht (mit möglichst wenig SQL Statements natürlich!), und dann zum Schluss einen SELECT auf diese Temp-Table/Table-Variable.

    Theoretische Überlegung:

    Wenn die JOINs dazu führen dass das Result-Set massiv aufgeblasen wird, könnte es sein dass man mit anderen Lösungen schneller fährt. Also z.B. wenn man eine sehr schmale Tabelle mit sehr vielen Zeilen hat, die auf eine viel breitere Tabelle mit sehr wenigen Zeilen verweist. (Man würde also sehr viele Duplikate aus der 2. Tabelle bekommen.) Wenn man die Tabellen einfach zusammenjoint, dann blasen die vielen Duplikate der breiten Tabelle das Result-Set mächtig auf, und man kann u.U. etwas Performance z.B. dadurch verlieren dass die Daten alle vom SQL Server zur Applikation über's Netz müssen. Bzw. falls der JOIN Operator etwas "teurer" ist (z.B. wenn String-Spalten im Spiel sind) alleine schon durch den JOIN.
    In dem Fall könnte man probieren die Daten der breiten Tabelle mit den wenigen Zeilen mit einem 2. SELECT zu holen. Wenn das was bringen soll, muss man es aber auf jeden Fall so anstellen dass man dafür eben nur ein SELECT braucht. Sonst vernichtet der "1 SELECT pro Zeile" Overhead sofort jeglichen theoretischen Geschwindigkeitsvorteil. Wie man das anstellen könnte, hängt dann von der DB ab.

    Praxis:

    Hab' ich noch nie gebraucht 🙂

    0

  • Danke für die Antworten, im Prinzip mache ich das schon über Joins und zerstückel das in der Anwendung. Aktuell ist das zweistufig gelöst, im ersten Schritt bestimme ich alle Zeilen der Object-Tabelle und im zweiten Schritt hole ich Details zu den Objekten. Leider kann ich den zweiten Schritt nicht mit einer Query erledigen, da insgesamt 5 Tabellen mit einer Abhängigskeitstiefe von 2 beteiligt sind. Eine typische Abfrage liefert zwischen 2.000 und 25.000 Elemente aus der Object Tabelle zurück. Die Abhängigkeiten und die Anzahl der verknüpften Elemente sehen etwa so aus:

    `

    obj

    +-- pd (6-10 Elemente)

    +-- ma (8-16 Elemente)

    +-- cd (2+ Elemente)

    +-- md (0-5 Elemente)

      +-- mr (1-16 Elemente)

    `

    Wenn ich das als Join ausführe fliegt mir die Abfrage um die Ohren und das DBMS (PostgreSQL 9.1.x) kann das nicht mehr im RAM sortieren sondern muss auslagern. Aktuell dauert so eine Abfrage ~20 Sekunden, wobei knapp 40% Sekunden auf den ersten Schritt entfallen.
    Vielleicht kann ich den 2. Schritt parallelisieren, je nachdem wie groß die Treffermenge ist.

    0
  • ?

    gib uns mal ein vereinfachtes/anonymisiertes/aber korrektes Schema von den Tabellen und den Relationen - mit Will-ich-haben-Feld Markierung - falls nicht in den Relationen schon drinn einfach ein Fake-Feld einfügen

    und deinen Join der dir den Speicher auffrisst - mölicherweise können wir dann besser helfen

    0
  • ?

    am aller besten mit http://sqlfiddle.com/

    dann kann man leichter spielen 🙂

    0

  • Ich schau morgen mal, wie ich euch das aufbereiten kann. Für sqlfiddle ist das Schema zu groß, da ist bei 8K Zeichen Schluss.

    0

  • OK. Also...
    Was du momentan machst ist quasi

    objects = GetObjects()        -- some thousands
for each o in objects
    x = GetXDetailsFor(o)     -- X details for current object
    y = GetYDetailsFor(o)     -- Y details for current object
    z = GetZDetailsFor(o)     -- Z details for current object

    Das macht mehrere Abfragen pro Objekt (Zeile) im Ergebnis der ersten Abfrage.
    Und das ist das Hauptproblem.

    Die einfachste Lösung ist meistens gleich alles zusammenzujoinen, nur wenn das nicht geht, weil dir das eine grosse SELECT dann um die Ohren fliegt, dann muss man halt anders.

    Das eigentliche Problem ist hier nicht dass die Daten in insgesamt 4 Schritten geholt werden (in meinem Beispiel, objects, x, y und z). Sondern dass einige der Schritte pro Zeile wiederholt werden müssen, und du damit einige zig- bis hunderttausend SELECTs zusammenbekommst.

    Lösung: Hol die benötigten Details mit je einem einzigen SELECT pro Detail-Typ. Also quasi

    objects = GetObjects()          -- some thousands
x = GetXDetailsForAll(objects)  -- X details for all objects
y = GetYDetailsForAll(objects)  -- Y details for all objects
z = GetZDetailsForAll(objects)  -- Z details for all objects

    Um so eine GetDetailsForAll Funktion zu implementieren gibt es mehrere Möglichkeiten. Welche geht bzw. optimal ist hängt vom Datenbank-System ab.

    1. Dynamic SQL mit "IN (o1, o2, o3, o4 ...)".
      Geht mit SQL Server ziemlich gut, der verdaut ohne Probleme mehrere tausend Einträge in der "IN" Liste. SQL Batches von mehreren MB sind kein Ding. Parse- und Compile-Zeiten etc. sind dabei auch kein echtes Thema -- auf jeden Fall bist du damit nach meiner Erfahrung immer viel viel schneller als wenn du 1 SELECT pro "o" machst.

    2. Dynamic SQL mit Table Variablen
      Ähnlich der "IN" Variante, nur mit Table-Variable statt IN.
      Quasi

    DECLARE @ids TABLE (id INT);
INSERT @ids (id) VALUES (1), (2), (3), (4) ...;
SELECT * FROM xxx WHERE xxx_id IN (SELECT id FROM @ids);

    1. Temp Table
      3.1) Dynamic SQL mit Temp Table - analog zu (2)
      3.2) Temp Table der von der Applikation über einen Bulk-Inserter befüllt wird
      3.3) Temp Table der vom 1. SELECT ("GetObjects") erstellt wird und dann wiederverwendet um die Details nachzuladen

    2. Mit manchen DBMS' + passender Zugriffs-API kann man auch Arrays als Parameter binden.
      Dann kann man im Prinzip einfach schreiben...

    command = new Command
command.Text = "SELECT * FROM xxx WHERE xxx_id IN @param" -- Genaue Syntax hängt vermutlich vom DBMS ab
command.AddParam("param", someArray)
reader = command.Execute()
...

    ----

    Bzw. wenn dir nur der Sort um die Ohren fliegt... ist der Sort für die Ausführung der Abfrage nötig, oder nur damit das Ergebnis halt dann sortiert ist? Wenn letzteres, dann könntest du ja einfach in deiner Anwendung sortieren.

    ----

    Bzw. noch eine Überlegung: Wenn ein Sort zu lange dauert, dann kann man auch versuchen das Ergebnis erstmal unsortiert in einen Temp-Table zu knallen, dann auf dem Temp-Table nen passenden Index erzeugen, und dann erst ein SELECT mit ORDER BY machen. Je nach Abfrage und DBMS kann das einiges bringen.

    0
  • T

    Wenn ich das als Join ausführe fliegt mir die Abfrage um die Ohren und das DBMS (PostgreSQL 9.1.x) kann das nicht mehr im RAM sortieren sondern muss auslagern.

    das kann ich mir nicht vorstellen. da muss der join an sich fehlerhaft/zu unelegent formuliert sein. ich hab schon joins mit >25 tabellen und tiefen von >3 gemacht und es ging alles in unter 30 sekunden.

    ich würde gern mal die korrekten tabellen und die gewünschten ausgabefelder sehen und das dann bei mir selbst probieren.

    kannst du das ganze nicht in eine sqlite db exportieren und die irgendwo hochladen?

    0

  • Nochmal danke für die Antworten.
    Ich mache mal einen Dump mit den Testdaten, mit denen ich teste. Die db enthält Messdaten, die wie oben beschrieben organisiert sind:

    Tabelle measurements:
  Enthält Daten zu einer Messung (Start, Ende, etc). 
  Zu jeder Messung gehören:
    1+ Messdateien (Tabelle measurement_files)

Tabelle measurement_files:
  Enthält Daten zu einer Messdatei( Typ, Größe, etc).
  Zu jeder Messdatei gehören:
    0 - 12 process data Einträge (Tabelle process_data)
    0+     custom_data Einträge (Tabelle custom_data)
    0 - 64 material_data Einträge (Tabelle material_data)
    0+     measurement_data einträge (Tabelle measurement_results)

Tabelle process_data
  Enthält Informationen über die Umstände der Messung (Temperaturen, etc)

Tabelle custom_data
  Enthält beliebige Zusatzinformationen zur Messdatei

Tabelle material_data
  Enthält Informationen über das gemessene Material sowie einige Metainformationen

Tabelle measurement_results
  Enthält die tabellarischen Messergebnisse der Messdatei (Messtabellenname, keine db Tabelle)
  Zu jeder Messdaten-Eintrag gehören:
  1+     Messergebnis

Tabelle measurement_data
  Enthält die konkreten Messergebnisse der Messdaten (Name, Wert, Einheit)

    @Hustbaer:
    Ich mache das aktuell schon mit joins, aber zweistufig. Im ersten Schritt bestimme ich alle Objekte, die tatsächlich angefordert werden. Es handelt sich dabei um Messungen mit einem Zeitstempel, die für einen bestimmten Zeitraum angefordert werden, aber limitiert sind. Zum Beispiel alle Daten vom Juli 2016, aber davon nur die 5.000 jüngsten. Ich muss also schon sortieren lassen, um mit LIMIT die 5.000 jüngsten zu bestimmen. Ich mache das eigentlich schon genau so, wie du das in 1) Dynamic SQL machst. PostgreSQL hat auch kein Problem damit, wenn in der IN Menge 20.000 IDs stehen. In Pseudocode ausgedrückt sieht das so aus:

    1) map<unsigned int,Measurement> obj_result = query_measurements( some_filter_criteria );

2a) vector<CustomData> cd_result = query_custom_data( list_of_object_ids );
    for( auto cd : cd_result ) obj_result[cd.obj_id].add_custom_data( cd );

2b) vector<ProcessData> pd_result = query_process_data( list_of_object_ids );
    for( auto pd : pd_result ) obj_result[pd.obj_id].add_process_data( cd );

2c) vector<MaterialData> ma_result = query_material_data( list_of_object_ids );
    for( auto ma : ma_result ) obj_result[ma.obj_id].add_material_data( ma );

2d) vector<MeasurementResult> mr_result = query_measurement_results( list_of_object_ids );
    for( auto mr : mr_result ) obj_result[mr.obj_id].add_measurement_result( mr );

    Man könnte jetzt das Bestimmen der Detaildaten in 4 eigene Threads auslagern und gucken, ob das damit schneller wird. Bzw. erst prüfen, wieviele Messungen in Schritt 1 rausfallen und erst ab, sagen wir 2.500, Threads für die Detailabfrage benutzen.

    Ich habe da schon relativ lange dran rumoptimiert, da war ich wohl auf dem richtigen Weg. Trotzdem sind manche Kunden unzufrieden, weil sie teilweise 30 Sekunden auf ein Ergebnis oder länger warten müssen. Dass dabei einige Millionen Tabellenzeilen betrachtet werden müssen und dass das einfach dauert verstehen sie oft nicht. Bei extremen Wartezeiten (gab auch schon mehrere Minuten Wartezeit) konnten wir eigentlich immer deren Netzwerk/Hardware dafür verantwortlich machen.

    @tenim:
    Der JOIN ist halt stumpf über alle Tabellen. Ich brauche tatsächlich alle Spalten aus jeder beteiligten Tabelle.

    EXPLAIN ANALYZE SELECT 
   m.*,
   mf.*,
   pd.*,
   cd.*,
   ma.*,
   mr.*,
   md.*
FROM 
   measurements m,
   measurement_files mf,
   process_data pd,
   custom_data cd,
   material_data ma,
   measurement_results mr,
   measurement_data md
WHERE
   m.measurement_id IN 
   (
      SELECT measurement_id 
      FROM measurements
      WHERE start_time > '2016-07-01 00:00:00' AND start_time < '2016-08-01 00:00:00'
      ORDER BY start_time DESC
      LIMIT 5000
   ) AND         
   m.measurement_id = mf.measurement_id AND
   mf.file_id = pd.file_id AND
   mf.file_id = cd.file_id AND
   mf.file_id = ma.file_id AND
   mf.file_id = mr.file_id AND
   mr.measurement_result_id = md.measurement_result_id

    So, der Dump ist durch. Gezippt sind das immer noch 6GB (roh 30GB), wollt ihr das immer noch zum Spielen haben? :D. Hab keine Ahnung, wo ich sowas hochladen könnte.

    Edit:
    Formatierung verbessert

    0

  • Nachtrag 1): EXPLAIN ANALYZE der Komplettabfrage.

    Nachtrag 2): Abfrage durchlaufen lassen, Abbruch nach 4:30 Minuten: Out of memory for query result.

    0
  • ?

    Warum klopft ihr solche Datenmengen in eine SQL-Datenbank?

    Warum nicht Upscaledb, LevelDB oder sonstige Key/Value-Pair-DB (https://influxdata.com/blog/benchmarking-leveldb-vs-rocksdb-vs-hyperleveldb-vs-lmdb-performance-for-influxdb/) - die für sowas entwickelt wurden
    und nur das Management in der SQL-Datenbank machen
    - fühlt sich an wie Bilder und Videos als Blobs in der DB

    0

  • Gast3 schrieb:

    Warum klopft ihr solche Datenmengen in eine SQL-Datenbank?

    Warum nicht Upscaledb, LevelDB oder sonstige Key/Value-Pair-DB (https://influxdata.com/blog/benchmarking-leveldb-vs-rocksdb-vs-hyperleveldb-vs-lmdb-performance-for-influxdb/) - die für sowas entwickelt wurden
    und nur das Management in der SQL-Datenbank machen
    - fühlt sich an wie Bilder und Videos als Blobs in der DB

    Das ist ne gute Frage... fehlende Erfahrung spielt da sicher eine Rolle.

    0

  • Hm.
    Clustered Indexe bzw. Tabellen partitionieren könnten hier noch helfen.

    0

  • Der measurement_id primary key ist bereits geclustert. Einige Versuche mit Partitionen haben unterschiedliche Ergebnisse gebracht, mal schneller, mal langsamer. Wir haben uns dann wegen des Mehraufwands dagegen entschieden.
    Wenn´s nicht schneller geht, dann geht´s halt nicht schneller. DBMS können auch nicht zaubern. Hab jetzt die Meinung von Leuten gehört, die bei DB mehr Erfahrungen haben als ich, das ist dann auch ok. Wenn mehr Performance nur durch ein anderes DBMS gewonnen werden kann dann können wir uns überlegen, ob wir migrieren wollen.

    0

  • Sieht so aus als würde er das "measurement_id IN (...)" viel zu spät machen. Kann man Postgres da Hints geben welchen Teil es als erstes machen soll?

    0

  • DocShoe schrieb:

    Der measurement_id primary key ist bereits geclustert. Einige Versuche mit Partitionen haben unterschiedliche Ergebnisse gebracht, mal schneller, mal langsamer. Wir haben uns dann wegen des Mehraufwands dagegen entschieden.
    Wenn´s nicht schneller geht, dann geht´s halt nicht schneller. DBMS können auch nicht zaubern.

    Aaaalso.
    Clusterung auf measurement_id ist für diese Abfrage sicher nicht optimal.
    Wenn dann müsste auf start_time bzw. (start_time, xxx_id) geclustert werden. Und zwar alle beteiligten Tabellen.
    Und dann für alle Tabellen die Einschränkung auf das gewünschte start_time Intervall dazugeben.

    Wenn das die einzige Abfrage auf die DB ist die eine nennenswerte Anzahl von Zeilen liest, dann könnte man das auch durchaus so machen. Falls nicht muss man sich natürlich angucken was das dann für Auswirkungen auf die Ausführungzeit der restlichen Abfragen hat.

    Natürlich könnte man auch das Clustering so lassen wie es ist, und zusätzlich für jede Tabelle einen convering Index auf start_time anlegen. Die Abfrage müsste dann ebenfalls halbwegs schnell gehen. Nur wird dadurch INSERT/UPDATE/DELETE deutlich langsamer - vermutlich wird es dann mehr als doppelt so lange dauern.

    Ansonsten, was du z.B. noch probieren kannst ist Temp-Tables zu verwenden, um damit die Ausführungsreihenfolge zu erzwingen.
    z.B.:

    SELECT *
INTO #m
FROM measurements
WHERE start_time > '2016-07-01 00:00:00' AND start_time < '2016-08-01 00:00:00'
ORDER BY start_time DESC
LIMIT 5000

SELECT * -- hier wird * nicht wirklich gehen, du wirst alle Spalten aufführen müssen
         -- (in * gäbe es ja sonst measurement_id doppelt => uneindeutiger Spaltenname => Fehler)
INTO #mf
FROM #m m
INNER JOIN measurement_files mf ON mf.measurement_id = m.measurement_id

SELECT
   mf.*,
   pd.*,
   cd.*,
   ma.*,
   mr.*,
   md.*
FROM
   #mf mf
   INNER JOIN process_data pd ON pd.file_id = mf.file_id
   INNER JOIN custom_data cd ON cd.cd.file_id = mf.file_id
   INNER JOIN material_data ma ON ma.file_id = mf.file_id
   INNER JOIN measurement_results mr ON mr.file_id = mf.file_id
   INNER JOIN measurement_data md ON md.measurement_result_id = mr.measurement_result_id

DROP TABLE #m
DROP TABLE #mf

    Wäre interessant was der Postgres daraus macht.
    Ein potentielles Problem bleibt dabei natürlich noch bestehen, nämlich der letzte Join von measurement_results nach measurement_data .
    Hier könnte man versuchen nochmal das selbe zu machen wie bei measurement_files, also den Join auf measurement_results damit er schon im Temp-Table materialisiert ist, wenn der Query-Optimizer anfängt das letzte SELECT zu optimieren.

    Aber andere Frage: Wie viele Datensätze fallen denn da am Ende raus?
    Bei wirklich grossen Mengen hilft nämlich i.A. einfach nur Clustern bzw. Partitionieren.
    Wenn die Lokalität der Rows im File gut ist und sie in der richtigen Reihenfolge angefordert werden kann auch ein Nested Loops Join akzeptable Resultate liefern. Merge und Hash sind halt bei grossen Tabellen Killer, weil sie - wenns keine weiteren Einschränkungen gibt - nen kompletten Table-Scan machen müssen.

    0

  • Wobei...
    Wenn ich mir nochmal angucke was du da schreibst...

    DocShoe schrieb:

    `

    obj

    +-- pd (6-10 Elemente)

    +-- ma (8-16 Elemente)

    +-- cd (2+ Elemente)

    +-- md (0-5 Elemente)

      +-- mr (1-16 Elemente)

    `

    ... dann denke ich mir: Da wird wohl ein JOIN über alles die falsche Wahl sein.
    Weil sich ja die Zeilenanzahl immer multipliziert.
    Du joinst ja immer auf file_id...
    Wenn du im 1. Join für eine file_id 6 Zeilen bekommst, dann joinst du diese file_id im 2. Join schon 6-fach. Da kommen dann nochmal JE 8 Zeilen dazu, bist du auf 48-fach. Usw.

    Weisst du natürlich alles, ich schreib's mehr für mich als für dich.

    Schlussfolgerung: Einzelne SELECTs auf die verschiedenen Tabellen machen da sicher mehr Sinn als ein grosser JOIN über alles.

    Also vielleicht doch statt IN mal mit Temp-Table versuchen. Und der Tip mit Clusterung auf start_time bzw. Covering-Index auf start_time ist natürlich nach wie vor gut (hoffe ich :)). Nur nicht vergessen die WHERE start_time BETWEEN ... Bedingung dann bei jedem SELECT mit dazuzugeben.

    0
  • ?

    DocShoe schrieb:

    Nachtrag 1): EXPLAIN ANALYZE der Komplettabfrage.

    Nachtrag 2): Abfrage durchlaufen lassen, Abbruch nach 4:30 Minuten: Out of memory for query result.

    Du liest aber schon über cursor in dein Programm?

    0

  • So, kurzes Feedback. Ich optimiere noch fleißig und muss dabei feststellen, dass der Query Planer von Postgres nicht immer die beste Strategie wählt. Die schnellste Lösung für PostgreSQL 9.1.13 ist im Moment, alle Abfrage separat in temporäre Tabellen zu schreiben und anschließend auszulesen.

    SELECT m.* FROM measurements m INTO TEMP TABLE tmp_measurements WHERE...
SELECT f.* FROM measurement_files f INTO TEMP TABLE tmp_files WHERE f.measurement_id = f.measurement_id
...

    Damit wird das alles schon deutlich schneller. Was mich nur etwas wundert ist, dass das Erzeugen und anschließendes SELECT auf zwei temp. Tabellen 2-3x so schnell ist wie die Abfrage über die "echten" Tabellen. Hängt vermutlich iwie mit Plattenzugriffen oder Caching zusammen.

    -- "echte" Abfrage dauert ca. ~1:30 mit 1.7M Treffern
SELECT 
  m.val1,
  m.val2,
  r.val1,
  r.val2
FROM
  measurement_data m,
  measurement_results r
WHERE
  m.result_id = r.result_id AND
  EXISTS( SELECT 1 FROM tmp_files WHERE m.file_id = tmp_files.file_id );

-- Erzeugen temp. Tabellen und anschließende Abfrage dauert ~0:35 mit 1.7M Treffern
SELECT 
  m.*
INTO TEMP TABLE 
   tmp_data 
FROM
   measurement_data m
WHERE EXISTS( SELECT 1 FROM tmp_files WHERE m.file_id = tmp_files.file_id );

SELECT
   r.*
INTO TEMP TABLE 
   tmp_result
FROM
   measurement_results r
WHERE EXISTS( SELECT 1 FROM tmp_data WHERE r.result_id = tmp_data.result_id );

SELECT 
  m.val1,
  m.val2,
  r.val1,
  r.val2
FROM
  tmp_data d,
  tmp_result r
WHERE
  m.result_id = r.result_id;
    0
Anmelden zum Antworten