4. Wie Daten aus schnell db lesen?

Wie Daten aus schnell db lesen?


  • 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

  • Der Grund ist das WHERE beim Erzeugen der Temp-Tables.
    Dadurch muss der JOIN dann nicht mehr die kompletten Tabellen joinen, sondern nur mehr das was nach dem WHERE übrig bleibt.

    Bei der "normalen" Abfrage scheint Postgres das falschrum zu machen, also erstmal alles zu JOINen und dann erst zu filtern.

    0

  • So, neues Update:
    Dank eurer Hilfe habe ich die Performance enorm steigern können, die Laufzeit einer Abfrage ist von 240 Sekunden auf 35 Sekunden gefallen. 7 Sekunden davon entfallen auf eine 3rd Party Komponente, die nach einer Abfrage wohl noch Aufräumarbeiten durchführen muss und dazu ewig braucht. Hab den Hersteller mal angeschrieben und ein Ticket aufgemacht, mal sehen, was dabei rauskommt.
    Parallelisieren lässt sich das Ganze nicht, da die temporären Tabellen sessionbezogen sind und nur für die Erzeuger-Session sichtbar sind. Die db Zugriffskomponenten lassen den Zugriff nur in dem Thread zu, in dem sie erzeugt worden sind, also müsste ich für jeden Thread eine neue db Session erzeugen, die dann aber die temporären Tabellen nicht sehen. Und persistente Tabellen zu erzeugen zieht wieder andere Problem nach sich, die 3 Sekunden Laufzeitgewinn IMHO nicht rechtfertigen.

    Edit:
    Die o.g. Abfrage stellt das Maximum dessen dar, was praxistauglich ist. IdR laufen die Abfragen wesentlich kürzer und sind nach ca. 12-15 Sekunden zurück.

    0

  • Cool!
    Würde mich interessieren wie die aktuelle Lösung jetzt aussieht.

    0

  • Die Daten werden nach wie vor zweistufig gelesen.

    Stufe 1)
    Bestimmen der Messungen-Treffermenge.
    Die alte Lösung brauchte noch ein DISTINCT in der Abfrage, was irre zeit gefressen hat. Die neue Variante kommt ohne aus und hat dadurch schon im Worst Case 80 Sekunden gewonnen). Sie trägt alle Treffer in eine temporäre Tabelle ein.

    DROP TABLE IF EXISTS tmp_measurements;
DROP TABLE IF EXISTS tmp_files;
DROP TABLE IF EXISTS tmp_tables;
DROP TABLE IF EXISTS tmp_data;
DROP TABLE IF EXISTS tmp_custom;
DROP TABLE IF EXISTS tmp_material;
DROP TABLE IF EXISTS tmp_process;

CREATE TEMPORARY TABLE tmp_measurements (LIKE measurements) WITH (FILLFACTOR = 100);
CREATE TEMPORARY TABLE tmp_files        (LIKE measurement_files) WITH (FILLFACTOR = 100);
CREATE TEMPORARY TABLE tmp_tables
(
   measurement_id        INTEGER,
   file_id               INTEGER,
   measurement_result_id INTEGER,
   measurement_name      TEXT
) WITH (FILLFACTOR = 100);

CREATE TEMPORARY TABLE tmp_data
(
   measurement_id        INTEGER,
   file_id               INTEGER,
   measurement_result_id INTEGER,
   measurement_name      TEXT,
   measurement_data_id   INTEGER,
   item_name             TEXT,
   value                 REAL,
   unit                  TEXT
) WITH (FILLFACTOR = 100);

CREATE TEMPORARY TABLE tmp_custom
(
   measurement_id      INTEGER,
   file_id             INTEGER,
   custom_data_id      INTEGER,
   user_id             INTEGER,
   timestamp           TIMESTAMP(0) WITHOUT TIME ZONE,
   key                 TEXT,
   value               TEXT
) WITH (FILLFACTOR = 100);

CREATE TEMPORARY TABLE tmp_material
(
   measurement_id      INTEGER,
   file_id             INTEGER,
   material_data_id    INTEGER,
   material_id         INTEGER,
   material            TEXT
) WITH (FILLFACTOR = 100);

CREATE TEMPORARY TABLE tmp_process
(
   measurement_id      INTEGER,
   file_id             INTEGER,
   process_data_id     INTEGER,
   item_id             INTEGER,
   item_value          REAL
) WITH (FILLFACTOR = 100);

INSERT INTO tmp_measurements
SELECT m.* FROM measurements m
WHERE
(-- BEGIN SYSTEM FILTERS
   ( -- BEGIN SYSTEM FILTER GROUP Vordefinierter Filter (Zeitraum)
      EXISTS( SELECT 1 FROM measurements WHERE
         measurements.measurement_id = m.measurement_id AND
         measurements.start_time > '2016-08-22 00:00:00' AND
         measurements.start_time < '2016-08-23 00:00:00'
      )
   ) -- END SYSTEM FILTER GROUP Vordefinierter Filter (Zeitraum)
   AND
   ( -- BEGIN SYSTEM FILTER GROUP Vordefinierter Filter (Mount Point)
      EXISTS( SELECT 1 FROM measurement_files WHERE
         measurement_files.measurement_id = m.measurement_id AND
         measurement_files.mount_point_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
      )
   ) -- END SYSTEM FILTER GROUP Vordefinierter Filter (Mount Point)
)-- END SYSTEM FILTERS
ORDER BY
   m.start_time DESC
LIMIT 2001;

SELECT
   tm.measurement_id,
   tm.measurement_name,
   tm.start_time,
   tm.stop_time,
   tm.comment,
   tm.measurement_guid
FROM
   tmp_measurements tm

    Stufe 2)
    Bestimmung der Messdateien und aller Detaildaten. In Schritt 1) wurden bereits alle notwendigen temporären Tabellen erzeugt, in Schritt 2) werden sie befüllt. Die temp. Tabellen enthalten für jede Zeile zusätzlich die ID der Zeile aus der "Parent" Tabelle, damit die finalen Datentabellen keine Daten mehr aus anderen Tabellen benötigen. Was mich dabei nur wirklich wundert ist, dass das Erzeugen, Füllen und Auslesen der temp. Tabellen schneller geht als das direkte Auslesen der Daten per WHERE Klausel aus zwei Tabellen, denn es wird prinzipiell das Gleiche gemacht.

    INSERT INTO tmp_files
SELECT mf.* FROM measurement_files mf
WHERE
(
   EXISTS( SELECT 1 FROM tmp_measurements
				WHERE
              mf.measurement_id = tmp_measurements.measurement_id
   )
);

INSERT INTO tmp_tables
SELECT
  tf.measurement_id,
  tf.file_id,
  mr.measurement_result_id,
  mr.measurement_name
FROM
   tmp_files tf,
   measurement_results mr
WHERE
   tf.file_id = mr.file_id;

INSERT INTO tmp_data
SELECT
   tt.measurement_id,
   tt.file_id,
   tt.measurement_result_id,
   tt.measurement_name,
   md.measurement_data_id,
   md.item_name,
   md.value,
   md.unit
FROM
   tmp_tables tt,
   measurement_data md
WHERE
   tt.measurement_result_id = md.measurement_result_id;

INSERT INTO tmp_custom
SELECT
   tf.measurement_id,
   tf.file_id,
   cd.custom_data_id,
   cd.user_id,
   cd.timestamp,
   cd.key,
   cd.value
FROM
   tmp_files tf,
   custom_data cd
WHERE
   tf.file_id = cd.file_id;

INSERT INTO tmp_process
SELECT
   tf.measurement_id,
   tf.file_id,
   pd.process_data_id,
   pd.item_id,
   pd.item_value
FROM
   tmp_files tf,
   process_data pd
WHERE
   tf.file_id = pd.file_id;

INSERT INTO tmp_material
SELECT
   tf.measurement_id,
   tf.file_id,
   ma.material_data_id,
   ma.material_id,
   ma.material
FROM
   tmp_files tf,
   material_data ma
WHERE
   tf.file_id = ma.file_id;

SELECT
   tf.file_id,
   tf.measurement_id,
   tf.mount_point_id,
   tf.file_type,
   tf.file_name,
   tf.comment,
   tf.file_status,
   tf.file_version,
   tf.archive_name,
   tf.file_size,
   tf.file_guid,
   tf.file_flags
FROM
   tmp_files tf
    0

  • Danke.

    DocShoe schrieb:

    Was mich dabei nur wirklich wundert ist, dass das Erzeugen, Füllen und Auslesen der temp. Tabellen schneller geht als das direkte Auslesen der Daten per WHERE Klausel aus zwei Tabellen, denn es wird prinzipiell das Gleiche gemacht.

    Nur im Prinzip, oder ist das SELECT wirklich identisch?
    Bei identischen SELECTs würde mich das nämlich etwas wundern.

    0
Anmelden zum Antworten