UPDATE-Statement Laufzeit zu lang

[Jochen Reichenberg]

Hallo zusammen,

ich hab folgendes UPDATE-Statement:

UPDATE STAT SET STAT.KD_NAME = (SELECT KS.KURZ_NAME FROM
KUNDENSTAMM KS WHERE (KS.KUNDNR = STAT.KUNDNR))

Die Laufzeit beträgt unerwartete 23 Minuten! PLAN für KS und STAT sind NATURAL. (ist eine embedded Firebord 2). Zugriffskomponenten sind die IBObjects, respektive das Tool IB_SQL.

Die Tabellendefinition:

CREATE TABLE STAT (
  BUMO                   INTEGER                NOT NULL
, RGNR                   INTEGER                NOT NULL
, AUFNR                  INTEGER                NOT NULL
, LSNR                   INTEGER                NOT NULL
, RGPOS                  INTEGER                NOT NULL
, RGPOS_FOLGE            INTEGER                NOT NULL
, RG_STATUS              VARCHAR( 1 )
, RG_DATUM               DATE
, KUNDNR                 VARCHAR( 8 )
, KD_NAME                VARCHAR( 20 )
, KD_ABC                 VARCHAR( 1 )
, POSID                  INTEGER
, ARTNR                  VARCHAR( 25 )
, ARTBEZ1                VARCHAR( 30 )
, ARTBEZ2                VARCHAR( 30 )
, ARTBEZ3                VARCHAR( 30 )
, VK_NETTO               DECIMAL ( 18, 2 )
, VK_ZUSCHLAG            DECIMAL ( 18, 2 )
, RG_VERTNR              VARCHAR( 8 )
, RG_VERT_NAME           VARCHAR( 20 )
, KV_VERTNR              VARCHAR( 8 )
, KV_VERT_NAME           VARCHAR( 20 )
, PRIMARY KEY ( BUMO, RGNR, AUFNR, LSNR, RGPOS, RGPOS_FOLGE ));

CREATE INDEX stidx1 ON STAT (KUNDNR);
CREATE INDEX stidx2 ON STAT (KV_VERTNR);
CREATE INDEX stidx3 ON STAT (RG_VERTNR);

Knapp 200.000 Datensätze in der Tabelle.

CREATE TABLE KUNDENSTAMM (
  KUNDNR                 VARCHAR( 8 )           NOT NULL
, KURZ_NAME              VARCHAR( 20 )
, ABC_ANALYSE            VARCHAR( 1 )
, PRIMARY KEY ( KUNDNR ));

Knapp 6500 Datensätze in der Tabelle.

Ich hatte eigentlich erwartet, dass er die Indizes verwenden würde. Dafür habe ich sie ja extra erzeugt. Muss ich vorgeben, welcher Index verwendet wird? Wenn ja, wie?
Oder muß ich auch noch einen Index für KURZ_NAME in der KUNDENSTAMM-Tabelle anlegen?

Insgesamt gibt es 6 ähnlicher Statements auszuführen... 23 Minuten ist schon zu lang, aber 2 Stunden...

Vielen Dank im voraus,

Jochen

[hwoess]

Na ja, 200.000 Records upzudaten sind schon nicht mehr so wenig - obwohl natürlich 23 Minuten auch da zu lange sind. Wie lange dauert denn zB. ein UPDATE STAT SET STAT.KD_NAME = '123' ?
Dann sollte man mal den Anteil für das Lesen aus dem Kundenstamm sehen, ob das wirklich der entscheidende Part ist.

bye,
Helmut

[Jochen Reichenberg]

Wie lange dauert denn zB. ein UPDATE STAT SET STAT.KD_NAME = '123' ?

Hab ich, in weiser Voraussicht , schon probiert:
Ein UPDATE auf RG_VERTNR = '-", VK_VERTNR = '-' und VK_NETTO = 0 dauert 23 Sekunken.

Grüße Jochen

[Thomas Steinmaurer]

Hallo Jochen,

hast Du das in einer isolierten Umgebung (= nur eine DB-Verbindung) ausprobiert oder können auch noch konkurrierende Zugriffe mit ins Spiel kommen?

Wie und wann wird den die Tabelle STAT befüllt? Einmalig? Periodisch? Meine erste Vermutung ist, dass Deine Indizes nach der Befüllung vielleicht nicht mehr ausbalanciert sind und die Statistiken nicht mehr passen. Versuch einmal folgendes:

alter index stidx1 inactive;
alter index stidx1 active;
commit;

Dann führ das Update Statement nochmal aus. Vielleicht hilfts ja. Wenn nicht, dann würde mich der genaue Zugriffsplan einmal interessieren.

Thomas

[Jochen Reichenberg]

Hallo Thomas,

jein das macht (k)einen Unterschied...

Es gibt ein Script, mit dem die Tabellen und Indize erstellt werden. Dort war nur ein einzelnes Commit ganz am Ende. Unter diesen Bedingungen macht es keinen Unterschied.

Ich habe jetzt zwischen jeden Befehl ein Commit gesetzt und das Statement dauert nur noch ca. 90 Sek., auch ohne den Index zu de- und zu reaktivieren.

Wieso macht das denn so einen Unterschied?
Allerdings dauert das Befüllen der 7 'Basistabellen' (1:1 aus dem ERP-System übernommen) plötzlich 5 x so lange wie vorher.
Des weiteren dauert das UPDATE auf RG_VERTNR = '-", VK_VERTNR = '-' und VK_NETTO = 0 jetzt gut 2 Minuten, statt der 20 Sekunden.

Kann ich das so interpretieren, dass ohne die einzelnen Commits, die Indize nicht korrekt erstellt und beim befüllen nicht gewartet werden könnnen?

Grüße Jochen

[Thomas Steinmaurer]

Hallo Jochen,

eine goldene Regel bei Firebird ist immer, dass man DDLs committen soll, bevor auf die darin angelegten Objekte über DML zugegriffen wird!

Ich würde folgende Vorgehensweise einmal versuchen:

- Tabelle anlegen => Commit
- Indizes anlegen => Commit
- Indizes deaktivieren => Commit
- Befüllung der Tabelle => Entweder die Befüllung in einer Transaktion oder z.B. alle 10.000 Datensätze committen. Wenn es in Häppchen passiert, dann auf jeden Fall zum Schluss auch ein Commit machen, damit auch wirklich alles bestätigt wurde.
- Indizes wieder aktivieren => Commit
- Abfrage ausführen

Generell ist zu sagen, dass ein aktiver Index bei einer Schreiboperation entsprechend im Hintergrund von der Engine aktualisiert werden muss. D.h. Schreiboperation mit Indizes werden immer langsamer sein als ohne Index. Umso mehr spürt man diesen Unterschied bei Batch-Operationen, wo sehr viele Datensätze betroffen sind. Hier ist es immer klug, etwaige Indizes vorher zu deaktivieren, diese Änderung committen, die Batch-Operationen durchführen, committen und dann die Indizes wieder aktivieren. Somit läuft die Batch-Operation schneller und nach dem Aktivieren der Indizes sind diese wieder ausbalanciert und die Selektivitätstatistik ist auch wieder am Stand.

Thomas

[Jochen Reichenberg]

Hallo Thomas,

werde ich sofort umsetzen. Ergebnis (oder weiter Fragen ) poste ich dann...

Auf jeden Fall schon mal ein dickes Dankeschön für Deine bisherige Hilfe!

Jochen

[Jochen Reichenberg]

Hallo nochmal,

also das deaktivieren der Indizes bring nicht viel, es geht etwas schneller, statt 300 Sekunden, noch 270 Sekunden. Allerdings kann das auch an der Auslastung des ERP-Systems liegen. Der Datenimport findet auch programmseitig statt. Vor dem Einfügen der Daten führe ich folgendes aus:

// Index auf RG_ARTPOS deaktivieren
ibotrans->StartTransaction();
ibodsql->SQL->Clear();
ibodsql->SQL->Add("ALTER INDEX raidx1 INACTIVE");
ibodsql->ExecSQL();
if (ibotrans->TransactionIsActive)
	ibotrans->Commit();

ibodsql ist eine IBObjects DSQL-Komponente und ibotrans eine IBObejects Transaction-Komponente. Sollte doch auch funktionieren?
Allerdings ist das Erzeugen und Befüllen der (für mich relevanteren) Statistiktabelle viel schneller geworden. Hier bin ich von mehreren Stunden Laufzeit auf ca 12 Minuten herunter. Und dabei ist die CPU-Last auch noch von 100% auf durschnittlich 20% gesunken.

Grüße Jochen

[Thomas Steinmaurer]

Hallo Jochen,

bei Deinem Code-Snippet unten wirst Du nicht zum Commit kommen, weil beim expliziten Starten einer Transaktion die TransactionIsActive property False sein wird. Warum wird überhaupt überprüft, ob eine Transaktion aktiv ist, wenn Du diese eh explizit startest? Das einzige was Du sicherstellen solltest ist, dass bei einem Fehler von ExecSQL die Transaktion beendet wird, d.h. mit einem Try/Except-Block würde ich das wie folgt lösen (Delphi-Syntax):

// Index auf RG_ARTPOS deaktivieren
ibotrans.StartTransaction;
try
  ibodsql.SQL.Clear;
  ibodsql.SQL.Add('ALTER INDEX raidx1 INACTIVE');
  ibodsql.ExecSQL;
  ibotrans.Commit;
except
  ibotrans.Rollback;
end;

Thomas

[Jochen Reichenberg]

Hallo Thomas,

die Prüfung auf TransaktionActive funktioniert problemlos. Kann das daran liegen, dass ich der DSQL-Kompo eplizit eine Transaktion zugewiesen habe?

Wenn das potentiell Probleme bereiten kann, muss ich ein paar Dinge umbauen, da auch Fehler geworfen werden können, bevor eine Transaktion überhaupt gestartet wird. Deshalb muß ich zumindest im except-Block prüfen, ob die Transaktion überhaupt schon aktiv ist.

Grüße Jochen

[Thomas Steinmaurer]

Hallo Jochen,

jetzt geht es sehr in Richtung Anwendungarchitektur (Transaktions-Handling, ...) mit IBObjects. Ich kann Dir nur empfehlen, dass Du Dir die TechInfo-Sheets von IBObjects einmal etwas näher ansiehst. http://www.ibobjects.com/TechInfo.html

Vor allem:
http://www.ibobjects.com/TechInfo.html#ti_Transactions
http://www.ibobjects.com/TechInfo.html#trantutor

HTH,
Thomas

[Jochen Reichenberg]

Hallo Thomas,

da hab ich ja ein bißchen was zu lesen....

Bedankt,

Jochen