Select-Tuning

[nachti1505]

Hallo,

ich habe eine Tabelle mit ca. 100.000 Personen. Jede Person gehört nun noch zu einer bestimmten Gruppe (Referentiell Key).


Eine stored procedure liefert mir nun aus der Tabelle eine Menge von Personen, für die bestimmte Eigenschaften gelten (Alter, Wohnort, etc.) und liefert als Rückgabewert außerdem die Gruppe, in welcher diese Person ist.

Zusätzlich möchte ich nun noch als boni angeben, wieviel Personen grundsätzlich aus dieser Gruppe gefiltert wurden.

Beispiel (Personen sind alle 25 Jahre alt)

Name:Maier, Alter:25, Gruppe:7, Anzahl:3 (heißt, es befinden sich insgesamt 3 Personen auf dieser Liste, die in Gruppe 7 sind)
Name:Müller, Alter:25, Gruppe:1, Anzahl:1
Name:Edel, Alter:25, Gruppe:7, Anzahl:3
Name:Stark, Alter:25, Gruppe:7, Anzahl:3

Code:

  for select vv_id,
             vv_name,
             vv_alter,
             ...
             vv_gruppe
  from vv_personas where vv_alter = 25 and vv_xyz is null and vv_abc is null
  into :vv_id,
       :vv_name,
       :vv_alter,
       :...
       :gruppe
  do begin
    select count(vv_id) from vv_personas where vv_alter = 25 and vv_xyz is null and vv_abc is null
                             and vv_gruppe = :vv_gruppe into :vv_gruppencount;
    suspend;
  end

Auf das originäre Suchkriterium (vv_alter, vv_xyz, vv_abc) ist ein Index gesetzt, so dass hier ca. 400 indizierte reads ausgeführt werden. Durch das select(count) in der inneren Schleife werden nun je gefundenem Datensatz nochmal ca. 400 indizierte reads ausgeführt --> macht 20.000 reads.

Ich frage mich jetzt, ob man die originäre Liste (200 matched personas) irgendwie buffern kann, um das select count effizienter zu machen?

[omata]

Vielleicht so...

SQL-Code:

SELECT vv_id,
       vv_name,
       vv_alter,
       ...
       p.vv_gruppe, 
       g.anzahl
FROM vv_personas p
LEFT JOIN (SELECT vv_gruppe, COUNT(*) anzahl
           FROM vv_personas 
           WHERE vv_alter = 25 
             AND vv_xyz IS NULL 
             AND vv_abc IS NULL
           GROUP BY vv_gruppe) g
  ON p.vv_gruppe = g.vv_gruppe
WHERE vv_alter = 25 
  AND vv_xyz IS NULL 
  AND vv_abc IS NULL

Zitat von nachti1505:

Ich frage mich jetzt, ob man die originäre Liste (200 matched personas) irgendwie buffern kann, um das select count effizienter zu machen?

Mit Firebird geht das leider so nicht. Unter MSSQL oder auch MySQL würde so etwas mit temporären Tabellen möglich sein.

[nachti1505]

Gute Idee, scheint aber performance-technisch keinen Unterschied zu machen... trotzdem vielen Dank!

[nachti1505]

Zitat von omata:

Zitat von nachti1505:

Ich frage mich jetzt, ob man die originäre Liste (200 matched personas) irgendwie buffern kann, um das select count effizienter zu machen?

Mit Firebird geht das leider so nicht. Unter MSSQL oder auch MySQL würde so etwas mit temporären Tabellen möglich sein.

Wobei ja FB >=2.1 temp tables beherrscht! Bringt mir das was?

[omata]

Zitat von nachti1505:

Wobei ja FB >=2.1 temp tables beherrscht! Bringt mir das was?

Das habe ich auch gerade gesehen. Allerdings sind das globale temporärer Tabellen.

[tsteinmaurer]

Sollte eigentlich über ein Inline-Select mit einer SELECT-Anweisungen gehen, obs performanter ist => Keine Ahnung. Generell ist es bei Performancefragen immer gut, den Ausführungsplan hier mitanzugeben. Dann auch noch die Info welche Indizes vorhanden und ob die Statistiken aktuell sind.

Die SQL-Abfrage:

Code:

select
  vv_id,
  vv_name,
  vv_alter,
  ...
  vv_gruppe,
  (select
     count(p2.vv_id)
   from
     vv_personas p2
   where
     p2.vv_alter = 25
     and p2.vv_xyz is null
     and p2.vv_abc is null
     and p2.vv_gruppe = p1.vv_gruppe
   ) as vv_gruppe_anzahl
from
  vv_personas p1
where
  p1.vv_alter = 25
  and p1.vv_xyz is null
  and p1.vv_abc is null

lg,
Thomas

[tsteinmaurer]

Global = Betreffend der Speicherung der Struktur, d.h. die Tabelle(nstruktur) ist für jeden verfügbar. Die Daten sind dann "lokal" per Transaktion oder Verbindung.

Thomas

[omata]

@tsteinmaurer: naja, da war mein Left-Join ja noch besser (Weil das parallel ausführbar ist).

[tsteinmaurer]

Mit den Window Functions in Firebird 3 werden dann solche Sachen wirklich spannend.

lg,
Thomas

[omata]

So hier mal ein Vorschlag (ungetestet)

SQL-Code:

CREATE GLOBAL TEMPORARY TABLE temp_data (
  vv_id INT PRIMARY KEY,
  vv_name VARCHAR(100),
  vv_alter INT,
       ...
  vv_gruppe VARCHAR(100)
);


INSERT INTO temp_data
SELECT vv_id,
       vv_name,
       vv_alter,
       ...
       p.vv_gruppe,
FROM vv_personas p
WHERE vv_alter = 25
  AND vv_xyz IS NULL
  AND vv_abc IS NULL;


SELECT p.*, g.anzahl
FROM temp_data p
LEFT JOIN (SELECT vv_gruppe, COUNT(*) anzahl
           FROM temp_data
           GROUP BY vv_gruppe) g
  ON p.vv_gruppe = g.vv_gruppe;


DROP GLOBAL TEMPORARY TABLE temp_data;