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

[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.