Hallo!

Ich muss die Tabelleninhalte von einer Tabelle ("Conv") bearbeiten
und das Ergebnis in einer anderen Tabelle (result) abspeichern.

"Conv" hat etwa 35.000 Zeilen, "result" etwa 20.000

Die Tabbellen sehen etwa soaus:
CREATE TABLE Conv (
Ab VARCHAR (25),
Txt VARCHAR (250),
TxtGer VARCHAR (200),
Bes VARCHAR (3000),
Quellen VARCHAR (1000)
);

CREATE TABLE result (
Ab varchar(42) COLLATE latin1_general_cs DEFAULT NULL,
Txt varchar(3500) COLLATE latin1_general_cs DEFAULT NULL)
ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs;


Die Arbeit macht die folgende Prozedur:
-- ************************
-- ************************
DELIMITER $$

CREATE PROCEDURE `P_ToWord2`()
BEGIN

DECLARE vAb VARCHAR(42);
DECLARE vTxt VARCHAR(300);
DECLARE vTxtGer VARCHAR(300);
DECLARE vBes VARCHAR(3000);

DECLARE done INT DEFAULT 0;
DECLARE cur1 CURSOR FOR
SELECT Ab, Txt, TxtGer, Bes
FROM Conv
ORDER BY Ab,
LENGTH (Quellen) - LENGTH (REPLACE(Quellen,';','')) desc,
Txt;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

OPEN cur1;
REPEAT
FETCH cur1 INTO vAb, vTxt, vTxtGer, vBes;

IF (EXISTS (SELECT Ab FROM Result WHERE Ab = vAb ))
THEN
UPDATE Result
SET Txt = CONCAT (Txt, '||', vTxt, vTxtGer, vBes)
WHERE Ab = vAb;
ELSE
INSERT INTO Result VALUES (vAb, CONCAT(vTxt, vTxtGer, vBes));
END IF;

UNTIL done END REPEAT;
DROP TABLE Conv;
END $$

DELIMITER ;
-- ************************
-- ************************

Mein Problem ist die Schleife mit dem Cursor, das Ding läuft über
zwei Stunden. Kann mir jemand einen Tipp geben, wie ich die Sache
beschleunigen kann?

Besten Dank
Dieter


PS: Von MySQL habe ich nicht viel Ahnung, bisher habe ich
überwiegend mit MS-SQL gearbeitet.

Dieter Valicek schrieb:
> Hallo!
>
> Ich muss die Tabelleninhalte von einer Tabelle ("Conv") bearbeiten
> und das Ergebnis in einer anderen Tabelle (result) abspeichern.
>
> "Conv" hat etwa 35.000 Zeilen, "result" etwa 20.000
>
> Die Tabbellen sehen etwa soaus:
> CREATE TABLE Conv (
> Ab VARCHAR (25),
> Txt VARCHAR (250),
> TxtGer VARCHAR (200),
> Bes VARCHAR (3000),
> Quellen VARCHAR (1000)
> );
>
> CREATE TABLE result (
> Ab varchar(42) COLLATE latin1_general_cs DEFAULT NULL,
> Txt varchar(3500) COLLATE latin1_general_cs DEFAULT NULL)
> ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs;
>
>
> Die Arbeit macht die folgende Prozedur:
> -- ************************
> -- ************************
> DELIMITER $$
>
> CREATE PROCEDURE `P_ToWord2`()
> BEGIN
>
> DECLARE vAb VARCHAR(42);
> DECLARE vTxt VARCHAR(300);
> DECLARE vTxtGer VARCHAR(300);
> DECLARE vBes VARCHAR(3000);
>
> DECLARE done INT DEFAULT 0;
> DECLARE cur1 CURSOR FOR
> SELECT Ab, Txt, TxtGer, Bes
> FROM Conv
> ORDER BY Ab,
> LENGTH (Quellen) - LENGTH (REPLACE(Quellen,';','')) desc,
> Txt;
>
> DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
>
> OPEN cur1;
> REPEAT
> FETCH cur1 INTO vAb, vTxt, vTxtGer, vBes;
>
> IF (EXISTS (SELECT Ab FROM Result WHERE Ab = vAb ))
> THEN
> UPDATE Result
> SET Txt = CONCAT (Txt, '||', vTxt, vTxtGer, vBes)
> WHERE Ab = vAb;
> ELSE
> INSERT INTO Result VALUES (vAb, CONCAT(vTxt, vTxtGer, vBes));
> END IF;
>
> UNTIL done END REPEAT;
> DROP TABLE Conv;
> END $$
>
> DELIMITER ;
> -- ************************
> -- ************************
>
> Mein Problem ist die Schleife mit dem Cursor, das Ding läuft über
> zwei Stunden. Kann mir jemand einen Tipp geben, wie ich die Sache
> beschleunigen kann?
>
>
Ich könnte mir vorstellen, dass das ORDER BY im obigen "P_ToWord2" etwas
Zeit kostet: Für jede Zeile erst in Quellen alle Semikolons wegwerfen
und dann die Länge davon von der Länge der unveränderten Spalte abziehen...

Ich verstehe auf Anhieb auch nicht, *warum* du das überhaupt tun willst.
Letztlich versuchst Du doch bloß, Daten in einer Tabelle einzutragen
bzw. zu ändern - wieso ist da irgendeine Reihenfolge relevant, in der
das passieren sollte ?
Möglicherweise könnte Dir auch der REPLACE-Befehl von MySQL helfen. Dann
sparst Du Dir das "EXISTS ..." in jedem Schleifendurchgang. Die
Dokumentation zu MySQL findest Du bei dev.mysql.com/doc

Dieter Valicek wrote:
> Hallo!
>
> Ich muss die Tabelleninhalte von einer Tabelle ("Conv") bearbeiten
> und das Ergebnis in einer anderen Tabelle (result) abspeichern.
>
> "Conv" hat etwa 35.000 Zeilen, "result" etwa 20.000
>
> Die Tabbellen sehen etwa soaus:
> CREATE TABLE Conv (
> Ab VARCHAR (25),
> Txt VARCHAR (250),
> TxtGer VARCHAR (200),
> Bes VARCHAR (3000),
> Quellen VARCHAR (1000)
> );
>
> CREATE TABLE result (
> Ab varchar(42) COLLATE latin1_general_cs DEFAULT NULL,
> Txt varchar(3500) COLLATE latin1_general_cs DEFAULT NULL)
> ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs;
>
>
> Die Arbeit macht die folgende Prozedur:
> -- ************************
> -- ************************
> DELIMITER $$
>
> CREATE PROCEDURE `P_ToWord2`()
> BEGIN
>
> DECLARE vAb VARCHAR(42);
> DECLARE vTxt VARCHAR(300);
> DECLARE vTxtGer VARCHAR(300);
> DECLARE vBes VARCHAR(3000);
>
> DECLARE done INT DEFAULT 0;
> DECLARE cur1 CURSOR FOR
> SELECT Ab, Txt, TxtGer, Bes
> FROM Conv
> ORDER BY Ab,
> LENGTH (Quellen) - LENGTH (REPLACE(Quellen,';','')) desc,
> Txt;
>
> DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
>
> OPEN cur1;
> REPEAT
> FETCH cur1 INTO vAb, vTxt, vTxtGer, vBes;
>
> IF (EXISTS (SELECT Ab FROM Result WHERE Ab = vAb ))
> THEN
> UPDATE Result
> SET Txt = CONCAT (Txt, '||', vTxt, vTxtGer, vBes)
> WHERE Ab = vAb;
> ELSE
> INSERT INTO Result VALUES (vAb, CONCAT(vTxt, vTxtGer, vBes));
> END IF;
>
> UNTIL done END REPEAT;
> DROP TABLE Conv;
> END $$
>
> DELIMITER ;
> -- ************************
> -- ************************
>
> Mein Problem ist die Schleife mit dem Cursor, das Ding läuft über
> zwei Stunden. Kann mir jemand einen Tipp geben, wie ich die Sache
> beschleunigen kann?


Warum haben Conv.Ab und Result.Ab nicht die gleiche Größe (25 und 42)?
Auf jeden Fall solltest Du aber einen Index auf die Abs setzen. Und die
Sortierung solltest Du noch mal überdenken, ist die wirklich notwendig?
Du sortierst ja nach der Ab, dann nach der Anzahl der ; ind Quellen und
dann nach Txt.
Speziell die Sortierung nach den ; ist sehr aufwendig. Ist die notwndig?

Ich würde ansonsten einfach mal zum Vergleich die Indizes setzen und die
Sortierung auf Ab begrenzen. Wie schnell ist dann das ganze?

Danke für die schnelle Antwort.


> Ich verstehe auf Anhieb auch nicht, *warum* du das überhaupt tun willst.
> Letztlich versuchst Du doch bloß, Daten in einer Tabelle einzutragen
> bzw. zu ändern - wieso ist da irgendeine Reihenfolge relevant, in der
> das passieren sollte ?

In "Quellen" stehen die Quellen :-) aus denen die anderen Angaben
stammen. Normalerweise gibt es viele Quellen, die durch ein
Semikolon getrennt sind. Und ich
muss das, was im Ergebnis in eine Zeile steht nach dessen
Häufigkeit sortieren. Ich zähle also die Semikolons um die
Anzahl der Quellen zu erhalten..
Das ist aber nicht das Problem - glaube ich

SELECT Abk
FROM Conv
ORDER BY Abk,
LENGTH (Quellen) - LENGTH (REPLACE(Quellen,';','')) desc,
Txt;
i
ist schnell.

> Möglicherweise könnte Dir auch der REPLACE-Befehl von MySQL helfen. Dann
> sparst Du Dir das "EXISTS ..." in jedem Schleifendurchgang.

Vielleicht, ich versuche es mal, aber eigentlich glaube ich nicht, dass
REPLACE wirklich viel schneller als EXISTS ist - oder doch?


Gruß
Dieter

> Warum haben Conv.Ab und Result.Ab nicht die gleiche Größe (25 und 42)?

Später wird in Conv.Ab noch etwas angefügt. Die unterschiedlichen Größen
sind aber für die Geschwindigkeit egal - oder täusche ich mich?


> Auf jeden Fall solltest Du aber einen Index auf die Abs setzen.
Ja.

> Und die
> Sortierung solltest Du noch mal überdenken, ist die wirklich notwendig?

Zwingend.

> Speziell die Sortierung nach den ; ist sehr aufwendig. Ist die notwndig?

Ja, was das soll habe ich in meiner Antwort zu Krisian Kirsch kurz
beschrieben.


> Ich würde ansonsten einfach mal zum Vergleich die Indizes setzen und die
> Sortierung auf Ab begrenzen. Wie schnell ist dann das ganze?

Also ich brauche die Sortierung nach den Semikolons unbedingt. Das mit den
Indizes
versuche ich nachher noch.



Vielleicht hier noch ein Beispiel:
"Conv":

'ab1' ' txt1' 'txtGer1' 'Bes1' 'Quelle1;'
'ab1' ' txt2' 'txtGer2' 'Bes2' 'Quelle2;Quelle3;'
'ab1' ' txt3' 'txtGer3' 'Bes3' ';'
'ab4' ' txt4' 'txtGer4' 'Bes4' 'Quelle4;'

wird in "Result" etwa zu:

'ab1' 'txt2 txtGer2 Bes2 || txt1 txtGer1 Bes1 || txt3 txtGer3 Bes3'
'ab4' 'txt4 txtGer4 Bes4'



Gruß
Dieter

Gehe ich da von einer falschen Annahme aus?
Sowohl Christian als auch Stehpan sehe in der
Sortierung nach den Quellen ein mögliches
Problem.
Die Abfrage
SELECT Ab, Txt, TxtGer, Bes
FROM Conv
ORDER BY Ab,
LENGTH (Quellen) - LENGTH (REPLACE(Quellen,';','')) desc,
Txt;
selbst ist ja schnell genug - und steht auch nicht direkt in der
Schleife. Also ich nahm an, MySQL erzeugt zunächst so etwas wie
eine temporäre Tabelle, auf die dann der Cursor in der Schleife
arbeitet. Ist das falsch, wird die Abfrage mehr als einmal ausgeführt?

Dieter Valicek wrote:
>
>
> Vielleicht, ich versuche es mal, aber eigentlich glaube ich nicht, dass
> REPLACE wirklich viel schneller als EXISTS ist - oder doch?

Replace hilft in Deinem Fall auch nicht wirklich weiter, da beim UPDATE
andere Daten abgespeichert werden, als beim INSERT.

Dieter Valicek schrieb:

>> Speziell die Sortierung nach den ; ist sehr aufwendig. Ist die notwndig?
>
> Ja, was das soll habe ich in meiner Antwort zu Krisian Kirsch kurz
> beschrieben.
>

Nenne ich Dich Detlef oder Dogan?

>
>> Ich würde ansonsten einfach mal zum Vergleich die Indizes setzen und die
>> Sortierung auf Ab begrenzen. Wie schnell ist dann das ganze?
>
> Also ich brauche die Sortierung nach den Semikolons unbedingt. Das mit den
> Indizes
> versuche ich nachher noch.
>

Du hast solche Tabellen *ohne* Index? Warum?

>
>
> Vielleicht hier noch ein Beispiel:
> "Conv":
>
> 'ab1' ' txt1' 'txtGer1' 'Bes1' 'Quelle1;'
> 'ab1' ' txt2' 'txtGer2' 'Bes2' 'Quelle2;Quelle3;'

Normalisierung?

> 'ab1' ' txt3' 'txtGer3' 'Bes3' ';'

Konsistenz? Wenn ein *leerer* Eintrag schon ein Semikolon hat, warum hat
einer mit einem Element dann nicht *zwei* Semikolons: ";Quelle4;" ?

> 'ab4' ' txt4' 'txtGer4' 'Bes4' 'Quelle4;'
>
> wird in "Result" etwa zu:
>
> 'ab1' 'txt2 txtGer2 Bes2 || txt1 txtGer1 Bes1 || txt3 txtGer3 Bes3'
> 'ab4' 'txt4 txtGer4 Bes4'
>

Das war zu befürchten. Denormalisiertes rein, denormalisiertes raus.
Wollt Ihr das dann im nächsten Schritt an den "||" zerlegen?

Warum normalisiert ihr das Ganze nicht ordentlich? Eine Tabelle mit
"ab", "txt", "txtGer" und "bes", und vielleicht noch mit einem
synthetischen Schlüssel (alternativ könnt Ihr Euch natürlich auch einen
unique index aus (ab,txt) basteln), denn ein Integer dürfte etwas
schneller sein. Eine zweite Tabelle enthält für jeden Schlüssel aus der
Ersten in jeweils *einem* Feld die passenden Quellen - oder eben nix,
wenn's keine Quelle gibt.

Dieter Valicek schrieb:
> Gehe ich da von einer falschen Annahme aus?
> Sowohl Christian als auch Stehpan sehe in der
> Sortierung nach den Quellen ein mögliches
> Problem.
> Die Abfrage
> SELECT Ab, Txt, TxtGer, Bes
> FROM Conv
> ORDER BY Ab,
> LENGTH (Quellen) - LENGTH (REPLACE(Quellen,';','')) desc,
> Txt;
> selbst ist ja schnell genug - und steht auch nicht direkt in der
> Schleife. Also ich nahm an, MySQL erzeugt zunächst so etwas wie
> eine temporäre Tabelle, auf die dann der Cursor in der Schleife
> arbeitet. Ist das falsch, wird die Abfrage mehr als einmal ausgeführt?
>
>

Was ist "schnell genug" - was sagt Explain zu Deiner Query? Was ist
"langsam"? Die Abfrage läuft allerdings hoffentlich nur einmal, deshalb
ja der Cursor, mit dem Du durch das Ergebnis iterierst.

Aber wie im anderen Posting gesagt: Das sieht für mich nach einem
Designproblem aus.

Stefan Dreyer schrieb:
> Dieter Valicek wrote:
>>
>> Vielleicht, ich versuche es mal, aber eigentlich glaube ich nicht, dass
>> REPLACE wirklich viel schneller als EXISTS ist - oder doch?
>
> Replace hilft in Deinem Fall auch nicht wirklich weiter, da beim UPDATE
> andere Daten abgespeichert werden, als beim INSERT.

REPLACE ... SET bla = bla || fasel

das wäre nun wirklich kein Problem, denn beim INSERT ist ja "bla" noch
leer. Angesichts der Datenstrukturen schadet das "||" am Anfang nicht
wirklich ;-) Alternativ könnte man vielleicht
SET bla = IF(bla is null, fasel, bla || fasel)
oder sowas machen.

Aber bislang wissen wir ja nur, was alles *nicht* langsam ist ...