FB 3.0 Update mit where exists durchläuft alle Record der upd tbl.

[Kostas]

Hallo Zusammen,

bei den beiden Nachstehenden Beispielen, liefert das Select auf die Rechnungen genau einen record.
Das Update aktualisiert auch nur den einen Record. Allerdings wird in beiden Beispielen die komplette Datenmenge von 500.000 Adressen durchlaufen. Das dauert natürlich. Das sehe ich auch in IBExpert unter Performance Analysis.

Code:

UPDATE ADRESSEN A
   SET A.AKTIV = 1
 WHERE EXISTS (SELECT NULL
                 FROM RECHNUNGEN R
                WHERE R.RECHNUNGSNR = 1
                  AND R.ADRESSID = A.ADRESSID
               );

oder

Code:

UPDATE ADRESSEN A
   SET A.AKTIV = 1
 WHERE A.ADRESSID IN (SELECT ADRESSID
                        FROM RECHNUNGEN R
                       WHERE R.RECHNUNGSNR = 1
                     );

Hat jemand eine Idee wie man das verhindern kann?
Ich sehr nur eine Möglichkeit über Execute Block das funktioniert auch Pfeil schnell:

Code:

EXECUTE BLOCK (IRECHNUNGSNR TYPE OF COLUMN RECHNUNGEN.RECHNUNGSNR = :RECHNUNGSNR)
AS
DECLARE VARIABLE ADRESSID TYPE OF COLUMN ADRESSEN.ADRESSID;
BEGIN
  FOR SELECT ADRESSID
        FROM RECHNUNGEN R
       WHERE R.RECHNUNGSNR = :IRECHNUNGSNR 
        INTO :ADRESSID 
  DO
  BEGIN
    UPDATE ADRESSEN A
       SET A.AKTIV = 1
     WHERE A.ADRESSID = :ADRESSID;
  END

  SUSPEND;

END

[IBExpert]

würde helfen zu wissen wie du die tabelle defininert hast und welche indizes existieren
ich vermute mal da sind keine passenden

[Uwe Raabe]

Zitat:

Zitat von Kostas (Beitrag 1537780)

Allerdings wird in beiden Beispielen die komplette Datenmenge von 500.000 Adressen durchlaufen.

Das ist aber auch nachvollziehbar: Das Exist muss ja für jeden Datensatz ausgewertet werden bevor der akzeptiert oder übersprungen werden kann.

[Kostas]

ADRESSEN.ADRESSID ist der PK.
RECHNUNGEN.ADRESSID ist ein FK auf ADRESSEN.ADRESSID.
Auf RECHNUNGEN.RECHNUNGSNR ist ein Unique Index gesetzt.

Daran liegt es somit nicht. Ich hätte erwartet, dass der Select auf die Rechnungen durchgeführt wird und die Anzahl der Results gegen den Update gefahren wird. Bei kleinen Datenmengen ist dieses Verhalten auch egal aber bei etwas größeren Datenmengen eben nicht. Im MySQL kann man ein Join für das Update einbauen. In FB geht das leider nicht.

[Uwe Raabe]

Zitat:

Zitat von Kostas (Beitrag 1537791)

Ich hätte erwartet, dass der Select auf die Rechnungen durchgeführt wird und die Anzahl der Results gegen den Update gefahren wird.

Das mag für dein zweites Code-Beispiel gelten, aber für das mit dem Exists wohl eher nicht.

Was du in dem BLOCK machst, ist ja genau das, was du erwartest. Ein ähnliches Verhalten hätte ich jetzt auch von den zweiten Code-Beispiel erwartet. Da dem offenbar nicht so ist, wäre eine Analyse der in FB verwendeten Strategie interessant. Vielleicht kann man da mit einer PLAN-Anweisung noch was tunen.

[himitsu]

Zitat:

Zitat von Uwe Raabe (Beitrag 1537786)

Zitat:

Zitat von Kostas (Beitrag 1537780)

Allerdings wird in beiden Beispielen die komplette Datenmenge von 500.000 Adressen durchlaufen.

Das ist aber auch nachvollziehbar: Das Exist muss ja für jeden Datensatz ausgewertet werden bevor der akzeptiert oder übersprungen werden kann.

Na eigentlich würde es ja reichen, wenn beim ersten Fund abgebrochen wird.
Gefunden ist gefunden und noch mehr finden ändert nichts.

exists(...)
vs
count(...) > 0

Ja, beim Count könnte man zwar auch schon beim Ersten abbrechen, aber nicht, wenn Zählen und Vergleich nicht in einer gemeinsamen optimierbaren Operation stattfinden.

count(...) > 3
könnte optimiert ja auch schon beim dritten Fund abbrechen.



Kommt also drauf an, wie das DBMS die Aufgabe löst.
* erst suchen und dann schauen, was/wieviel rauskam
* speziell suchen und dabei gleich auswerten

[Uwe Raabe]

Zitat:

Zitat von himitsu (Beitrag 1537793)

Na eigentlich würde es ja reichen, wenn beim ersten Fund abgebrochen wird.
Gefunden ist gefunden und noch mehr finden ändert nichts.

Das ist schon richtig - und das wird im SELECT-Teil wohl auch so passieren. Ändert aber nichts daran, dass trotzdem der gesamte Artikelstamm durchsucht und für jeden Datensatz das EXISTS ausgewertet wird.

[himitsu]

kommt drauf an, wie es optimiert wird
und welche Datenlage wahrscheinlicher geben ist.

* das SubSelect zu Beginn einmal auswerten dann das Exists auf diese TempTable (also mit IN)
* in jedem Durchlauf das Exists+Select neu auswerten (also mit EXISTS)

Das Eine ist besser, wenn sehr viele ADRESSEN und sehr wenige RECHNUNGEN
das Andere ist besser, wenn sehr wenige ADRESSEN und sehr viele RECHNUNGEN (was hier bestimmt wahrscheinlicher sein wird).

[Kostas]

Rows affected funktioniert bei Execute Block auch nicht oder?

[Uwe Raabe]

Könntest du den BLOCK Code nicht als Stored Procedure anlegen?

[Kostas]

Zitat:

Zitat von Uwe Raabe (Beitrag 1537800)

Könntest du den BLOCK Code nicht als Stored Procedure anlegen?

das ist durchaus machbar.

[IBExpert]

ursache ist recht einfach, weil du hier einen fehler machst

WHERE R.RECHNUNGSNR = 1

was ist der fehler?

RECHNUNGSNR ist sicherlich kein integer sondern ein char oder varchar
und daher muss firebird integerwerte in index stringlisten suchen, das geht nicht
immer so einfach. umgekehrt ist das selten ein problem.

workaround

WHERE R.RECHNUNGSNR = '1'

das läuft das indiziert. in deinem execute block gehst du über variablen,
die werden vor der Nutzung intern passend zu den indizierten felder
umgewandelt.

mach einfach immer passende sqls zu deinen feldtypen.

[himitsu]

Zitat:

Zitat von IBExpert (Beitrag 1537803)

RECHNUNGSNR ist sicherlich kein integer sondern ein char oder varchar
und daher muss firebird integerwerte in index stringlisten suchen,

Man könnte sich doch auch einen passenden Index erstellen, also inkl. CAST zum Integer.

[IBExpert]

klar kann man so einen index zusätzlich erstellen, der aber speed beim schreiben
kostet wie jeder index.

CREATE INDEX RECHNUNGEN_IDX2 ON RECHNUNGEN COMPUTED BY (cast(rechnungsnr as integer));

Bringt aber in dem beispiel auch nur dann was wenn das indizierte feld mit der gleichen
expression benutzt wird.

select ADRESSID
from RECHNUNGEN R
where cast(R.RECHNUNGSNR as integer)= 1

auch das hier geht schon indiziert mit dem schon vorhandenen index ohne extra index, ist
aber ein sehr umständlicher Ersatz für die beiden tüdelchen

select ADRESSID
from RECHNUNG R
where R.RECHNUNGSNR= cast(1 as varchar(80))

[Kostas]

Das Feld RECHNUNG.RECHNUNGSNR ist wirklich ein Integer und es existiert ein unique index darauf. Die Ausgangsrechnungen werden durchnummeriert und über ein Trigger geprüft das keine Lücken entstehen.

[IBExpert]

dann stell mal die beteiligten metadaten hier ein, und die real benutzen sqls
am besten mit benutztem index plan und datenbankstatistik.

Das verhalten, was ich da geschildert hab, ist reproduzierbar, falls es ein varchar
feld ist.

Wenn es wirklich integer ist wird das so nicht passieren. selbst extrem
schlecht statistics werte der indizes sollten trotzdem nicht dafür sorgen, das
der index komplett ignoriert wird.

ich weiss leider zu oft von fällen wo man meinte das irgendwas bestimmte metadaten
hätte, das dann aber nachher komplet was anderes war.

[Kostas]

Ich habe eine Test-DB angelegt mit diesen zwei Tabellen. Adressen hat 1000 Datensätze und die Rechnungen 5000.

Über IBExpert Testdaten-Generator die Tabellen befüllt.

Die RechnungsNr 520 existiert nur einmal und ist ein unique Index gesetzt.

Code:

update adressen a
   set a.aktiv = 1
where exists (select null
                from rechnungen r
               where r.rechnungsnr = 520
                 and r.adresseid = a.adresseid)  /* hier die Verknüpfung zwischen Rechnung und Adressen */
order by a.adresseid

Plan für Exists
--------------------------------------------------------------------------------
PLAN (R INDEX (RECHNUNGEN_IDX1))
PLAN (A ORDER PK_ADRESSEN)
1 record(s) was(were) updated in ADRESSEN

------ Performance info ------
Prepare time = 31ms
Execute time = 813ms
Current memory = 35.717.744
Max memory = 35.889.424
Memory buffers = 2.048
Reads from disk to cache = 3
Writes from cache to disk = 0
Fetches from cache = 6.025
-----------------

Über die StoreProc:


------ Performance info ------
1 record(s) was(were) updated in ADRESSEN

Prepare time = 0ms
Execute time = 47ms
Current memory = 35.986.464
Max memory = 36.173.296
Memory buffers = 2.048
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 21

[himitsu]

kann man statt dem Subselect nicht auch einen JOIN nutzen?

so ala

SQL-Code:

join rechnungen r on r.adresseid = a.adresseid
where r.rechnungsnr = 520
  --and r.adresseid IS NOT NULL  -- das exists, aber wenn r.rechnungsnr trifft, dann muß das ja sowieso existieren

[Kostas]

Ein Join geht unter Firebird nicht, zumindest ist mir das nicht bekannt. Bei MySQL gibt es so eine Konstruktion.

das geht nicht.

Code:

update adressen a
   set a.aktiv = 1
join rechnungen r on r.adresseid = a.adresseid
where r.rechnungsnr = 520
order by a.adresseid

[Kostas]

Es gibt auch die Möglichkeit über MERGE doch das Ergebnis ist dasselbe, zumindest von der performance:

Code:

MERGE INTO adressen AS A
    USING rechnungen AS R
    ON a.adresseid = r.adresseid AND r.rechnungsnr = 520
    WHEN MATCHED THEN
        UPDATE SET a.aktiv = 1

Plan
--------------------------------------------------------------------------------
PLAN JOIN (R INDEX (RECHNUNGEN_IDX1), A INDEX (PK_ADRESSEN))
1 record(s) was(were) updated in ADRESSEN

------ Performance info ------
Prepare time = 31ms
Execute time = 781ms
Current memory = 35.691.552
Max memory = 35.939.392
Memory buffers = 2.048
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 29

[IBExpert]

bei mir braucht der merge in deiner db (nach set statistics auf allen tabellen)
16ms und auch nur je ein indexed read pro tabelle, hab mich zwar bisher mit merge noch nicht wirklich
angefreundet und mache dann lieber gleich execute blocks, aber technisch ist deine aussage mit den sqls
scheinbar korrekt.

wenn es immer nur eine record aus der unterdatenmenge gibt würde auch so was gehen (= operator vor der klammer)
update adressen a
set a.aktiv = 1
where a.adresseid =(select rec.adresseid
from rechnungen rec
where rec.rechnungsnr =530
)
braucht auch nur je 1 indexed read

bei mehreren records seh ich auch das problem für den optimierer, das der ja pro
adresse noch nicht wissen kann, ob es da eine rechnung gibt mit den kriterien, daher
klappert der alle ab. es könnte ja auch noch weitere bedingungen geben, die den dann
doch wieder ausschliessen.

ein wenig schneller wäre das hier (frag mich nicht warum, aber ich meine weil es eine
multirecord result menge im inneren select geben könnte wegen between)

update adressen a
set a.aktiv = 1
where exists(select rec.adresseid
from rechnungen rec
where rec.rechnungsnr between 530 and 530
)

der macht so nur 1 indexed read auf rechnung, aber weiterhin 1000 auf adresse

bei mir wäre das ergebnis ganz sicher dein execute block, ist zwar die ein oder andere zeile
quellcode mehr, aber einfach zu verstehen und auch in 5 jahren kapierst du den grund für den
code und die schachtelung noch.

[Kostas]

Ich verwende sehr intensiv Exists weil ich mal gelesen habe "WHERE Feldname in (select ..." sollte eher durch Exists ersetzen. Ich habe mir um die Performance keine Gedanken gemacht. Ich habe zufällig nie größere Datenmenge zu verarbeiten und deshalb ist es nicht aufgefallen. Execute Block verwende ich liebend gern. Ist für mich überhaupt kein Problem. Und wenn ich irgendwann mal auf D12 umsteige (hatte noch keine Zeit) dann ist zukünftig eh kein Problem im Code da ab D12 Das SQL in einem Block im Code abgelegt werden kann durch dreifache Anführungszeichen.

Mit MERGE habe ich einiges experimentiert und nicht wirklich einen Einsatzfall gesehen. Alle meine Versuche hatten nahezu den gleichen Performance. Aber vermutlich gibt es einen Fall bei dem MERGE Sinnvoll sein kann.