Was mache ich falsch? Firebird rattert wie irre auf der HDD

[Gecko]

Hallo,

wollte mir einen kleinen Spamfilter schreiben und daher alle Wörter einer SpamMail in die DB packen und dann später noch in einer zweiten Spalte die Häufigkeit dazutragen.
Also Tabellenaufbau:

Spamwort | Häufigkeit

So schön so gut, erster Ansatz:

for I := 0 to SpamwordStringList.Count-1 do begin
qrsecond.SQL.Text:='INSERT INTO POSTEIN (SUBJECT) VALUES (:WORD)';
qrsecond.ParamByName('WORD').AsString:=SpamwordStr ingList.Strings[i];
qrsecond.ExecSQL;
end;

Sobald die Mails etwas grösser werden und sagen wir ein paar tausend wörter anfallen, fängt meine HDD gar fürcherlich für ca. 15-20 Sec an zu rappeln.
Ist das normal für meine Vorgehensweise oder mache ich Querymässig etwas falsch?

So etaws merke ich bei Spamfiltern von Thunderbird und co nicht...was machen die anders?

[mkinzler]

Der Vorteil von Parametern ist eigentlich, daß das Statement nur einmal an das DBMS gesendet werden muß. Diesen Vorteil nutzt du nicht

Delphi-Quellcode:

qrsecond.SQL.Text:='INSERT INTO POSTEIN (SUBJECT) VALUES (:WORD)';
for I := 0 to SpamwordStringList.Count-1 do begin
   qrsecond.ParamByName('WORD').AsString:=SpamwordStringList.Strings[i];
    qrsecond.ExecSQL;
end;

Wie hast du die Transaktionssteuerung eingestellt?

[Gecko]

Zitat:

Zitat von mkinzler

1. Der Vorteil von Parametern ist eigentlich, daß das Statement nur einmal an das DBMS gesendet werden muß. Diesen Vorteil nutzt du nicht

2. Wie hast du die Transaktionssteuerung eingestellt?

1. Habs mal so umgebaut, das bringt aber so gut wie nichts.

2. Ehrlich gesagt garnicht ;) Wo geht das? Und was sollte ich einstellen?

[mkinzler]

Ich vermute mal du verwendest AutoCommit, d.h. jeder Insert wird in eine eigene Transaktion gepackt und einzeln comitted.
http://www.dsdt.info/tutorials/zeosfirebird/?page=4

[Gecko]

Zitat:

Zitat von mkinzler

Ich vermute mal du verwendest AutoCommit, d.h. jeder Insert wird in eine eigene Transaktion gepackt und einzeln comitted.
http://www.dsdt.info/tutorials/zeosfirebird/?page=4

Zitat:

Dabei ist der sog. AutoCommit-Modus standardmäßig angeschaltet (True), was auch das Standardverhalten der BDE-Komponenten ist. Bei aktiviertem AutoCommit wird jedes einzelne SQL-Statement nach erfolgreicher Ausführung durch COMMIT bestätigt und die Änderungen in der Datenbank festgeschrieben. Soll dieses Verhalten ausgeschaltet werden und somit eine explizite Transaktion gestartet werden, in der man mehrere Datenbankzugriffe nacheinander durchführt, um diese als "Gruppe" durch COMMIT zu bestätigen, so muss die Methode StartTransaction aufgerufen werden. AutoCommit ist während einer expliziten Transaktion ausgeschaltet. Mit dem Aufruf der Methode Commit werden die Änderungen, die durch die Zugriffe in dieser Transaktion durchgeführt wurden, bestätigt. Der Aufruf der Methode Rollback macht diese Änderungen rückgängig. In beiden Fällen ist AutoCommit nach Methodenaufruf wieder eingeschaltet. Die explizite Transaktion ist beendet.

Wie mache ich das denn jetzt das er alles als eine Transaktion betrachtet? :cat:

[mkinzler]

Steht doch in dem Zitat. Vor der Schleife Transaktion starten und danach commiten.

[Gecko]

Hab ich gemacht, bringt aber in Sachen Rappelei 0,0 :(

Delphi-Quellcode:

qrsecond.SQL.Text:='INSERT INTO POSTEIN (SUBJECT) VALUES (:WORD)';
zconnection1.StartTransaction;

for I := 0 to sl.Count-1 do begin
qrsecond.ParamByName('WORD').AsString:=sl.Strings[i];
qrsecond.ExecSQL;
end;

zconnection1.Commit;

[mkinzler]

Wieviele Einträge willst du in einem Rutsch in die Datenbank schreiben?
Vielleicht wäre Insert Into ne Option

[Gecko]

Das waren jetzt ca. 8000 auf einmal. Aber auch bei 1000 rappelt es ja noch lang genug, und das ist ja kaum was...

Was meinst du mit Insert Into? das hab ich doch da?

[Hansa]

Jo, ist schon schlimm. Wieso werden immer im Klartext (innerhalb des Programms selbst) irgendwelche SQL-Statements zusammengebaut. Ihr kapiert einfach nicht, was eine Methode ist. :mrgreen: In diesem Fall ist das Insert und das würde so aussehen :

Delphi-Quellcode:

Dataset.Insert;
Dataset.Parambyname (...);
Dataset.post;

1000 DS ergibt dann wohl 1-10 Sek.

[Gecko]

So Update:

Der Code schafft jetzt 8000 in 4 Sekunden...ich denk viel besser wirds nicht gehn.

Delphi-Quellcode:

zconnection1.StartTransaction;
qrsecond.SQL.Text:='INSERT INTO SPAM (WORD) VALUES (:WORD)';

for I := 0 to sl.Count-1 do begin

qrsecond.ParamByName('WORD').AsString:=sl.Strings[i];
qrsecond.ExecSQL;

end;

zconnection1.Commit;

Allerdings muss ich vorher bei jedem Eintrag ja noch nen Select machen um zu schaun ob der Eintrag schon drin ist und dann nen Update um den Zähler um eins zu erhöhen,
wie oft das Word schon vorgekommen ist. Falls er nicht drin ist eben den Insert hier.

Hm gibts da irgenden Weg um das halbwegs effizient zu lösen?

[omata]

Hallo Gecko,

mach dein WORD zum PrimaryKey und bau um dein ExecSQL ein Try-Except.

Wenn die Zahl nicht da ist wird sie hinzugefügt, wenn sie schon da ist wird eine Exception ausgelöst und dann kannst du dein UPDATE machen.

Das sollte wesentlich schneller gehen.

Gruss
Thorsten

[alex517]

Hallo Gecko,

Lass doch die Arbeit Firebird machen, dazu hast du ihn (den Vogel ;)) doch.
Für deinen Fall dürfte diese SP reichen.
Es wird nachgesehen ob der Begriff schon vorhanden ist und entsprechend reagiert.
Wichtig ist ein Index auf das Feld "BEGRIFF" für den indizierten Zugriff.

SQL-Code:

CREATE PROCEDURE SP_SPAM_IU (
    BEGRIFF VARCHAR(30))
RETURNS (
    ANZAHL INTEGER)
AS
DECLARE VARIABLE ID INTEGER;
BEGIN
  BEGRIFF = UPPER(BEGRIFF);
  SELECT ID, ANZAHL FROM SPAM WHERE (BEGRIFF = :BEGRIFF) INTO :ID, :ANZAHL;
  IF (ID is not NULL) THEN
  BEGIN
    UPDATE SPAM SET ANZAHL = :ANZAHL +1
    WHERE (ID = :ID);
  END ELSE
  BEGIN
    INSERT INTO SPAM (
        BEGRIFF,
        ANZAHL)
    VALUES (
        :BEGRIFF,
        1);
    ANZAHL = 1;
  END
  SUSPEND;
END

alex

[gsh]

warum nicht einfach:

SQL-Code:

INSERT INTO TABLE SET id = 1, Test = 'Test' ON DUPLICATE KEY UPDATE Test = 'Test'

[Gecko]

Zitat:

Zitat von gsh

warum nicht einfach:

SQL-Code:

INSERT INTO TABLE SET id = 1, Test = 'Test' ON DUPLICATE KEY UPDATE Test = 'Test'

Mich beschleicht das Gefühl, das es ON DUPLICATE KEY in Firebird garnicht gibt :-( ??
Es funktioniert nämlich nicht und bei google gibts nicht nen einzigen eintrag dazu

Was kann man anstattdessen verwenden?

[hoika]

Hallo,

der Code On Duplicate klappt erst ab FB2.x.

8000 Einträge in 4 sec sind doch nicht schlecht...
Ist auf Word überhaupt ein Index drauf ?


Heiko

[Gecko]

Zitat:

Zitat von hoika

der Code On Duplicate klappt erst ab FB2.x.

Ich hab aber Firebird 2.x, klappen tuts trotzdem nicht :(
Invalid Token: ON

[mkinzler]

Mit 2.x ist >= 2.1 gemeint

[Gecko]

Zitat:

Zitat von mkinzler

Mit 2.x ist >= 2.1 gemeint

Ist die im Alpha Stadium? Auf der HP gibts nämlich nur 2.01
Hm was mach ich da? Den Workaround von alex517 nehmen oder kann man diese Alpha bereits stabil nutzen? (vlt hats ja jemand getestet)

[mkinzler]

Zitat:

Ist die im Alpha Stadium?

Ja

Zitat:

Auf der HP gibts nämlich nur 2.01

http://www.ibphoenix.com/main.nfs?a=...bp_download_21

Zitat:

Hm was mach ich da? Den Workaround von alex517 nehmen?

Kommt auf den Zeitrahmen, des Einsatzes deines Programmes an. Meiner Erfahrung nach ist die 2.1 recht stabil

[Gecko]

Hm als Embedded gibts das aber wohl noch nicht oder?
Kann jedenfalls nirgends einen Download finden

[mkinzler]

Wohl noch nicht

[Gecko]

So 2.1 installiert, Fehler bleibt jedoch

FB Version 2.1.0.15199

Fehler: Token unknown - line 1, column 43. ON.

Hat das vllt. was mit dem Zeos zu tun? Das unterstützt ja offiziell nur 2.0

[mkinzler]

Der Syntax in FB sieht etwas anders aus:

Zitat:

Zitat von "

UPDATE OR INSERT INTO <table or view> [(<column_list>)]
VALUES (<value_list>)
[MATCHING <column_list>]
[RETURNING <column_list> [INTO <variable_list>]]

[Gecko]

sry aber kapier ich nichtso ganz :cat:
INSERT INTO SPAM (WORD,ANZAHL) VALUES ('TEST',0) ON DUPLICATE KEY UPDATE ANZAHL=ANZAHL+1;

könnteste das mal ganz kurz übersetzen? :nerd:

[hoika]

Hallo,

die Syntax heisst halt anderes,
siehe deinem Vortipper.

SQL <> Standard Query Language

Hast du mal mit dem (Z SQL Monitor ? )
deine Abfragen geprüft ?


Heiko

[Gecko]

Zitat:

Zitat von hoika

Hallo,

die Syntax heisst halt anderes,
siehe deinem Vortipper.

SQL <> Standard Query Language

Hast du mal mit dem (Z SQL Monitor ? )
deine Abfragen geprüft ?


Heiko

Naja das sie anders heisst hab ich mitbekommen, aber durch diese andere Syntax blick ich jetzt nicht ganz durch ^^
Darum fragte ich mkinzler ob er mir das

INSERT INTO SPAM (WORD,ANZAHL) VALUES ('TEST',0) ON DUPLICATE KEY UPDATE ANZAHL=ANZAHL+1;

vllt in die neue Syntax übersetztn könnte, weil ich leider auch keine doku über FB 2.1 oder über den INSERT OR UPDATE Befehl gefunden habe.

[Luckie]

Zitat:

Zitat von omata

Wenn die Zahl nicht da ist wird sie hinzugefügt, wenn sie schon da ist wird eine Exception ausgelöst und dann kannst du dein UPDATE machen.

Exceptions sollten dazu genutzt werden, wozu sie auch gedacht sind, nämlich Fehler oder Randbedingungen abzufangen, auf die man keinen Einfluss hat als Programmierer und nicht als Möglichkeit dienen den Programmablauf zu steuern.
Das was du da vorschlägst ist ungefähr so, als wenn du mit geschlossenen Augen über die Ampel fährst und wenn es knallt war es rot.

[omata]

Danke für diesen wunderbaren Hinweis.

Und sorry, das ich was gesagt habe.

[Gecko]

Zitat:

Zitat von omata

Danke für diesen wunderbaren Hinweis.

Und sorry, das ich was gesagt habe.

Ach, er meint das nicht so ;-)

[hoika]

Hallo,

was passiert, wenn du z.B. nur 100 Einträge in die DB packst,
rattert der dann genauso ?

Wie sieht es bei dem Server mit dem RAM und RAM-Verbrauch von FB aus ?


Heiko

[Gecko]

Zitat:

Zitat von hoika

Hallo,
was passiert, wenn du z.B. nur 100 Einträge in die DB packst,
rattert der dann genauso ?
Wie sieht es bei dem Server mit dem RAM und RAM-Verbrauch von FB aus ?
Heiko

Nein, bei 100 Einträgen rattert nichts, ich konnte das ganze ja auch durch die freundliche Hilfe von mkinzler und co soweit in den Griff kriegen.
Was ich aber noch bräuchte ist eine Übersetzung von

Code:

INSERT INTO SPAM (WORD,ANZAHL) VALUES ('TEST',0) ON DUPLICATE KEY UPDATE ANZAHL=ANZAHL+1;

in die Syntax von Firebird 2.1, also irgendwie sowas:

Code:

UPDATE OR INSERT INTO <table or view> [(<column_list>)] 
VALUES (<value_list>) 
[MATCHING <column_list>] 
[RETURNING <column_list> [INTO <variable_list>]]

Das peil ich nämlich nicht so ganz ;)

[MrSpock]

Hallo Gecko,

alex517 hat dir in seinem Beitrag auf der ersten Seite dieses Threads eine StoredProcedure vorgestellt, die automatisch ein UPDATE anstelle eines INSERT durchführt, wenn der Schlüssel schon existiert. Diese kannst du benutzen, denn die funktioniert bei ellen FB Versionen. So mache ich es bei ähnlicher Problemstellung auch immer. :stupid:

[Gecko]

Zitat:

Zitat von MrSpock

Hallo Gecko,

alex517 hat dir in seinem Beitrag auf der ersten Seite dieses Threads eine StoredProcedure vorgestellt, die automatisch ein UPDATE anstelle eines INSERT durchführt, wenn der Schlüssel schon existiert. Diese kannst du benutzen, denn die funktioniert bei ellen FB Versionen. So mache ich es bei ähnlicher Problemstellung auch immer. :stupid:

Wie rufe ich die denn auf?
Ist die ganze Procedure das dann meine SQL Query? also Zeosquery.SQL.text:=Code von Alex?

[MrSpock]

Hallo Gecko,

nein, eine SP wird in der Datenbank gespeichert und direkt auf dem Server ausgeführt. Ich benutze kein Zeos, aber gibt es dort keine Komponente für die Verbindung mit StoredProcedures? Eine solche Komponente hat dann in der Regel eine Methode wie "Execute".

[mkinzler]

TZStoredProc oder halt einen Query

[Gecko]

Ich hab die StoredProc von Alex jetzt (leicht abgeändert) in IBExpert probiert:

Tabellenaufbau:
Word | Anzahl
Primary Key
Indiziert

SQL-Code:

CREATE PROCEDURE SP_SPAM_IU (
    word varchar(30))
returns (
    anzahl integer)
as

BEGIN 
  SELECT  WORD, ANZAHL FROM SPAM WHERE (WORD = :WORD) INTO :WORD, :ANZAHL;
  IF (WORD is not NULL) THEN
  BEGIN 
    UPDATE SPAM SET ANZAHL = :ANZAHL +1 
    WHERE (WORD = :WORD);
  END ELSE 
  BEGIN 
    INSERT INTO SPAM ( 
        WORD,
        ANZAHL) 
    VALUES ( 
        :WORD,
        1); 
    ANZAHL = 1; 
  END 

  SUSPEND;
END

Es kommt dann beim Ausführen auch ein Abfragefeld für den Parameter (Word), allerdings ist der Rückgabeparameter (anzahl) der mir dann angezeigt wird immer NULL
und in die Tabelle wird auch nichts eingefügt

[hoika]

Hallo,

der Parameter Word wird überschrieben.
Ist nie ne gute Idee, einen Parameter als Variable zu missbrauchen

mal so hingetippert.

SQL-Code:

CREATE PROCEDURE SP_SPAM_IU (
    word varchar(30))
returns (
    anzahl integer)
as
  declare variable LocalWord varchar(30);
BEGIN
  SELECT  WORD, ANZAHL FROM SPAM WHERE (WORD = :WORD) INTO :LocalWord, :ANZAHL;
  IF (LocalWord is not NULL) THEN
  BEGIN
    UPDATE SPAM SET ANZAHL = :ANZAHL +1
    WHERE (WORD = :WORD);

    Anzahl = Anzahl +1;
  END ELSE
  BEGIN
    INSERT INTO SPAM (
        WORD,
        ANZAHL)
    VALUES (
        :LocalWord,
        1);
    ANZAHL = 1;
  END

  SUSPEND;
END

Heiko

[Gecko]

@hoika

Jetzt kommt leider "The insert failed because a column definition includes validation constrains.
validation error for column WORD, value ***NULL***

:wall:

[mkinzler]

Im 2. Fall ist ja localWord leer, da Word nehmen:

SQL-Code:

CREATE PROCEDURE SP_SPAM_IU (
    word varchar(30))
returns (
    anzahl integer)
as
  declare variable LocalWord varchar(30);
BEGIN
  SELECT  WORD, ANZAHL FROM SPAM WHERE (WORD = :WORD) INTO :LocalWord, :ANZAHL;
  IF (LocalWord is not NULL) THEN
  BEGIN
    UPDATE SPAM SET ANZAHL = :ANZAHL +1
    WHERE (WORD = :WORD);

    Anzahl = Anzahl +1;
  END ELSE
  BEGIN
    INSERT INTO SPAM (
        WORD,
        ANZAHL)
    VALUES (
        :Word,
        1);
    ANZAHL = 1;
  END

  SUSPEND;
END