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

[Kostas]

Zitat von Uwe Raabe:

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 von IBExpert:

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