Was ist schneller? MySqlDirect oder ueber die .dll?

[DelTurbo]

Hi, eine frage an die spezialisten. Ich habe ca. 800.000 einträge in einer SQLite3. Nun habe ich mit mysqldirect eine schleife gemacht, die die daten auf einen mysqlserver (Debian5) kopiert.

Das ist langsam ohne ende. Erst dachte ich die schleife geht nicht, da er lange auf 0% stand. Aber die schleife geht.

Wenn ich nun über die libmysql??.dll gehe, z.b. ZEOS, bekomme ich das irgendwie schneller?

Ich hatte schon überlegt Threads anzuschmeissen, aber ich brauche die LastInsertID.

Ich hoffe jemand hat eine idee...

[Bernhard Geyer]

Verwendest du auch prepared Statements?

[jobo]

Ist das eine einmalige Sache oder musst Du das häufiger machen?
Scheint mir eher eine Frage des Verfahrens als der Komponenten.

P.S.: Wie siehts mir Indizes, Triggern, Constraints auf der Zieltabelle aus?

[DelTurbo]

Hallo ihr beiden.

Also, in der datenbank möchte ich meine USB-Platten, also die filenamen, grössen etc. speichern.

Ich habe 2 Tabellen. Einmal mit dem eigentlichen Pfadnamen (dort gibt es ein blobfeld mit den ids der dateien die zu dem path gehören). Und halt die Tabelle mit den eigentlichen dateinamen.

Eingefügt wird es über

INSERT INTO FilesTable(Filename,Size,Date) VALUES ("test",1,1); <-- achtung, nur ein beispiel!!!

Die LastInsertID merke ich mir und Trage sie in das blobfeld der 1. Tabelle ein.

Gibt es da was besseres, schnelleres als INSERT???

[Neutral General]

Arbeitest du denn mit Parametern und Prepare?

[DelTurbo]

Nein, so wie ich oben geschrieben habe. Da ist nix davor oder dahinter. Wenn du mir sagen könntest wie ich das anwende (mit der beispielzeile oben) dann wär ich dir dankbar.

[jobo]

Du trägst ID (welche? PK der Dateinamenssätze?) in ein BLOB Feld ein?
Das wäre nicht unbedingt ein klassisches Verfahren in RDBMS.
BLOB Verarbeitung ist inkl. caching usw auch nicht unbedingt das performanteste.

[DelTurbo]

Zitat:

Zitat von jobo (Beitrag 1130464)

Du trägst ID (welche? PK der Dateinamenssätze?) in ein BLOB Feld ein?
Das wäre nicht unbedingt ein klassisches Verfahren in RDBMS.
BLOB Verarbeitung ist inkl. caching usw auch nicht unbedingt das performanteste.

Ich habe das Programm für sqlite3 gemacht. Da das blob feld kleiner ist, ist es über wlan schneller.

Natürlich hast du recht, alleine das man den blob Escapen muss wird er eh grösser.

Zu dem Prepare find ich leider garnix. Kann MySqlDirect das überhaupt? Oder gibt es einen anderen "trick" mit dem man z.b. sagen kann, pass auf hier kommen 50 datensätze.

Sorry ist was platt ausgedrückt

[jobo]

Ich verstehe glaub ich Deine Gründe für BLOB nicht richtig und vor allem was Du damit bezwecken willst. In dem Blob steht dann später eine Zahlenreihe von ID aus der anderen Tabelle?
Normalerweise würde man in der "Files" Tabelle ein Feld für den Verweis auf die ID des "Path"-Tabellensatzes spendieren. Dann könnte man es nachher auch normal mit SQL bearbeiten und abfragen, reporten, ..

[Valle]

Sehe ich das richtig, dass die SQLite-Datenbank bei dir lokal leigt und der Debian-Server im Internet steht? Dann ist wichtig wie schnell dein Internet ist. Hier würde es sich lohnen, die SQLite-Datenbank komplett hochzuladen und auf dem Server selbst in MySQL zu übertragen. Da gibt es auch mit Sicherheit schon vorgefertigte Tools.

Ansonsten muss es doch auch möglich sein, ähnlich wie bei "mysqldump" den Inhalt der SQLite-Datenbank als SQL zu exportieren. Dann kannst du ein Kompressionsverfahren (zB. bzip) drüber laufen lassen und es anschließend hochladen.

Liebe Grüße,
Valentin

[DelTurbo]

Neee, der Server steht nicht im Internet. Ausser du nennst meine abstellkammer Internet :-D

Mit dem mysqldump ist mir auch klar. So hohle ich nachts immer backups. Aber das hat ja mit meinem MySqlDirect problem nix zu tun. Ich habe eben mal unter Windows einen MySql-Server drauf gemacht. Und auf dem gleichen system die 800.000 einträge reingeschrieben. Das war noch langsamer.

Kurz noch zu dem blob-feld. Daran sollten wir uns nun nicht aufhängen. Das ist bei sqlite3 schneller. Ich klicke z.b. auf einen Path. Da ich in dem moment die ID habe, brauch ich nur die "FilePointer" zu lesen und kann sie direkt anzeigen. Das ist bei WLAN schneller, als wenn ich nur einen Pointer zurück auf den path habe. Weil dann muss sqlite3 die ganze DB lesen. Die datenbank ist 300MB gross. Bis die durch das WLAN ist, ist der Akku leer :)

So, ich frage nun nochmal ganz gezielt.

1. Wäre es schneller wenn ich die libmysql??.dll nutze (z.b. über ZEOS)?
2. Wäre es dann möglich zig datensätze zu übertragen und die ids zu kommen? Mit IDs meine z.b. bei MySqlDirect den befehl LastInsertID bzw. sqlit3 GetLastInsertRowID.
oder
3. Gibt es einen "trick" bei MySqlDirect um sowas zu erreichen?

Gruss und danke im voraus

[DeddyH]

Ich mag mich irren, aber AFAIK besteht MySQL Direct doch lediglich aus den Delphi-Übersetzungen der Header-Dateien der libmysql.dll, diese wird also bereits benutzt. Inwieweit damit jetzt Prepared-Statements möglich sind, entzieht sich aber meiner Kenntnis.

[DelTurbo]

Nei, das kann nicht sein. Ich habe keine libmysql.dll auf meinem system.

[DelTurbo]

Ich habe mal ZEOS mit der libmysql.dll probiert. Ist leider auch gääähn.... So habe ich es gemacht.

Delphi-Quellcode:

ZConnection1.ExecuteDirect('INSERT INTO FileTable (FileName,FileSize,FileDate,EntryDate,PathTablePtr) VALUES ("ddd",'+IntToStr(i)+',2,3,4);');

Bei ~290.000 habe ich abgebrochen. Ich denke mal ich mache das falsch. Kennt jemand eine "einfache" Demo?

[p80286]

Ich kenne Mysql nicht, aber so 300.000 Datensätze in 30 Minuten (?) finde ich garnicht so schlecht.
Je nach Verfassung des Netzes darf es auch weniger sein. Deinem Beispiel entnehme ich, daß Du keine Parameter nutzt, das wäre u.U auch noch ein Gewinn, da der SQL-String dann nicht jedesmal neu zusammen gesetzt werden muß.
Läßt Du einen Zähler mitlaufen (

Delphi-Quellcode:

label1.caption:=inttostr(i); label1.repaint;

dann solltest Du das vllt einmal einstellen, denn das frißt auch Zeit.

Wie sieht es eigentlich mit dem commit aus? So alle 10-100 Datensätze sollte das auch erfolgen, kommt aber stark auf das verwendete DBMS an.

Gruß
K-H

[DelTurbo]

Das findest du schnell? Da ist ja sqlite3 schneller. Aber wesentlich. Laut iptraf kommt der nur auf 2MBit. Find ich bissl dünn für ein 1000er netzwerk.

Diese Commit wie ich es bei sqlite3 mache braucht man wohl nicht. Die Datensätze waren da. Das war eine "normale" for schleife, ohne alles. Also an der Perfomance hier im Client kann es nicht liegen. Zumal die CPU noch fast im idle lief.

Ich nehme stark an, das es an den vielen kleinen TCP packeten liegt.

Deswegen frage ich ja, ob es ein kommando gibt, nach dem motto "hier kommen x datensätze". Die könnte er dann in einem rutsch liefern.

[DeddyH]

Gibt es bei ZEOS AutoCommit? Falls ja, kannst Du das ja mal testhalber abschalten, explizit eine Transaktion starten und alle z.B. 100 DS ein Commit schicken.

[DelTurbo]

Hellseher? :-D

AutoCommit war an. Ich habe es nun mal abgeschaltet und mache alle 10.000 (zum testen) ein Commit. Der Speed ist wirklich auf 3MBit hoch. Ist nicht berauchend, aber so langsam wird es was :thumb:

Was mich nur wundert ist, das der Stream ständig da ist. Müsste das nicht so sein, das er nach 10.000 einen dicken batzen schickt?

[p80286]

Das commit ist dafür da, daß die Datenbank die Datensätze wirklich speichert und kein rollback mehr möglich ist.
Wenn Du für jeden DS einen Commit schickst, dann ist die Belastung des Servers rel niedrig, da er sich nicht merken muß was rückgängig zu machen ist.
Je mehr Datensätze Du einem Commit zuordnest, desto höher st die Belastung des Servers, aber die Netzlast geht etwas zurück.

Gruß
K-H

[DelTurbo]

Ah, ok. Dann habe ich das Falsch verstanden. Ich dachte er "sammelt" und das Commit schickt los.

[Valle]

Zitat:

Zitat von DelTurbo (Beitrag 1130511)

Deswegen frage ich ja, ob es ein kommando gibt, nach dem motto "hier kommen x datensätze". Die könnte er dann in einem rutsch liefern.

Geht das nicht einfach mit mehreren VALUES-Klauseln?

Code:

INSERT INTO bla (...) VALUES (...) VALUES (...) VALUES (...)

Liebe Grüße,
Valentin

[DelTurbo]

Leider nicht. Wäre aber ne coole idee. Ich habe nun alles durch. Angefangen mit deinem vorschlage über nochmaliges INSERT in einer neuen, mit AND usw...

Hier z.b. der Fehler mit AND.

Code:

Fehler
SQL-Befehl: 

INSERT INTO FileTable( FileName, NFOTablePTr, FileSize, FileDate, EntryDate, PathTablePtr ) 
VALUES (

"ddd", 1, 2, 3, 4, 5
)
AND (
FileName, NFOTablePTr, FileSize, FileDate, EntryDate, PathTablePtr
)
VALUES (
"ddd", 1, 2, 3, 4, 5
) 

MySQL meldet:  

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND (FileName,NFOTablePTr,FileSize,FileDate,EntryDate,PathTablePtr) VALUES ("ddd' at line 1

Das ist leider dein vorschlag.

Code:

Fehler
SQL-Befehl: 

INSERT INTO FileTable( FileName, NFOTablePTr, FileSize, FileDate, EntryDate, PathTablePtr ) 
VALUES (

"ddd", 1, 2, 3, 4, 5
)
VALUES (
"ddd", 1, 2, 3, 4, 5
) 

MySQL meldet:  

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VALUES ("ddd",1,2,3,4,5)' at line 1

[Valle]

o0

Also eigentlich kenne ich das ohne das "AND (...)", genau wie ich es oben geschrieben habe.

Code:

INSERT INTO FileTable( FileName, NFOTablePTr, FileSize, FileDate, EntryDate, PathTablePtr ) 
VALUES (
"ddd", 1, 2, 3, 4, 5
)
VALUES (
"ddd", 1, 2, 3, 4, 5
)

Edit:// Waren da schon immer zwei Codeblöcke in deinem Post? :-O

[p80286]

könnte es sein, daß dir " unverdaulich sind? also 'dddd' statt "dddd"

Gruß
K-H

[DelTurbo]

Zitat:

Zitat von Valle (Beitrag 1130521)

Edit:// Waren da schon immer zwei Codeblöcke in deinem Post? :-O

Nein, ich hatte nach dem Post nochmal deins probiert und gepostet ;)

Also an den " oder ´ oder ' kann es nicht liegen. Ich habe den String mal wegelassen.

Code:

Fehler
SQL-Befehl: 

INSERT INTO FileTable( NFOTablePTr, FileSize, FileDate, EntryDate, PathTablePtr ) 
VALUES ( 1, 2, 3, 4, 5 ) 
VALUES ( 1, 2, 3, 4, 5 ) 

MySQL meldet:  

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VALUES (1,2,3,4,5)' at line 1

[Valle]

Es fehlt das Komma nach VALUES:

Code:

INSERT INTO FileTable( NFOTablePTr, FileSize, FileDate, EntryDate, PathTablePtr ) 
VALUES ( 1, 2, 3, 4, 5 ),
VALUES ( 1, 2, 3, 4, 5 )

[DelTurbo]

Geht leider auchnicht. Ich habe auchschon zig varianten getestet. Leider finde ich auch in google nix. Die frage wäre dann auch noch wie ich für jeden eintrag an die LastInsertID komme. Obwohl, das ist ja ein autoincrement feld. Ich müsste ja dann nur die letzte ID hohlen und dann dementsprechend runterzählen.

Code:

Fehler
SQL-Befehl: 

INSERT INTO FileTable( NFOTablePTr, FileSize, FileDate, EntryDate, PathTablePtr ) 
VALUES ( 1, 2, 3, 4, 5 ) , 
VALUES ( 1, 2, 3, 4, 5 ) 

MySQL meldet:  

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VALUES ( 1, 2, 3, 4, 5 )' at line 3

[Valle]

So, jetzt aber. Sorry dass ich so auf dem Schlauch stand. Das hier funktioniert definitiv:

SQL-Code:

INSERT INTO `test` (`position`) VALUES (1), (1)

(also nur ein mal Values)

Liebe Grüße,
Valentin

[DelTurbo]

Zitat:

Zitat von Valle (Beitrag 1130569)

So, jetzt aber. Sorry dass ich so auf dem Schlauch stand. Das hier funktioniert definitiv:

SQL-Code:

INSERT INTO `test` (`position`) VALUES (1), (1)

(also nur ein mal Values)

Liebe Grüße,
Valentin

Ich danke dir. So geht es. Ich werde es morgen mal an meinem Programm testen. Ich hoffe nur das das autoinc feld wirklich nur hochzählt, und nicht etwa versucht löscher zu stopfen. Wenn ich z.b. eine Platte lösche.

Ich werde auf jeden fall berichten.

Antwort vom Server war:

Code:

Eingefügte Zeilen: 2
Letzte automatisch eingefügte ID: 2729050 (die Abfrage dauerte 0.0003 sek.)

Code:

Eingefügte Zeilen: 196
Letzte automatisch eingefügte ID: 2729620 (die Abfrage dauerte 0.0029 sek.)

Vielen dank

[Valle]

Keine Sorge, es füllt keine Löcher.

Liebe Grüße,
Valentin

[Medium]

Genau so eine Änderung hat mir letztens (allerdings mit MySQL) auch etliche Minuten eingespart. Zur Sicherheit würde ich nur alle paar zig bis hundert Value-Vektoren ein neues Insert anfangen, da manche DBMS nicht so gut auf volle 2GB Strings zu sprechen sind :)

[DelTurbo]

@Valle, danke für die auskunft. Ich habe gestern abend noch bissl "rumgespielt". Was ich garnicht wusste, man kann das autoinc feld ja auch beschreiben.

Ich habe gestern abend mal eine Tabelle gedropt und einfach einen eintrag mit ID 500 gemacht. Wenn ich danach wieder "normal" in die Tabelle schreibe, ist der nächste index 501. Das würde heissen das ich die db von sqlite3 1:1 rüberziehen kann.

@Medium, ich weiss noch garnicht wie lang der String sein darf. Bei sqlite3 ist z.b. eine grenze bei 1.000 values.

SELECT FileName FROM TABLE WHERE ID=1 OR ID=2 OR ID=3.....

Na meckert sqlite3 ab 1.000 rum das es zu gross ist. Er meckert definitiv die 1.000 IDs an. Nicht die Stringlänge.

Gruss und nochmals vielen dank

[Sir Rufo]

Die schnellste Möglichkeit Daten in den MySQL zubekommen ist mit MySQL LOAD DATA INFILE

Allerdings frage ich mich auch noch, was das mit dem BLOB-Feld da auf sich hat?
Das kann ja nur dann zum Tragen kommen, wenn die SQLite-Datei im Netzwerk liegt ... ist das so?

Soll denn jetzt der MySQL die SQLite ersetzen?

Denn dann macht es keinen Sinn dieses BLOB weiter zu nutzen, denn die Zusammenstellung der Daten erfolgt bei MySQL ja auf dem Server und es wird nur noch das Ergebnis durch das Netz geschickt.
Andersherum ist es auch kein Problem, dass der MySQL per VIEW dir dieses BLOB-Feld (nicht als BLOB) dann direkt baut und mitschickt.

Liegt die SQLite lokal vor, dann macht das BLOB-Feld auch keinen Sinn ...

Zitat:

Zitat von DelTurbo (Beitrag 1130607)

@Medium, ich weiss noch garnicht wie lang der String sein darf. Bei sqlite3 ist z.b. eine grenze bei 1.000 values.

Code:

SELECT FileName FROM TABLE WHERE ID=1 OR ID=2 OR ID=3.....

Na meckert sqlite3 ab 1.000 rum das es zu gross ist. Er meckert definitiv die 1.000 IDs an. Nicht die Stringlänge.

Dafür gibt es 2 Wege

Code:

SELECT FileName FROM TABLE WHERE ID IN (1,2,3,...)

oder man legt sich eine temporäre Tabelle mit den IDs an und

Code:

SELECT FileName FROM TABLE WHERE ID IN ( SELECT tmpID FROM tmpTABLE )

oder

Code:

SELECT FileName FROM TABLE JOIN tmpTABLE ON ID=tmpID

Ach ja und nicht vergessen einen INDEX (hier wohl geht wohl auch ein UNIQUE) auf die tmpTABLE zu legen ;)

[DelTurbo]

Danke für die Tipps.

Du hast es bestimmt überlesen (steht irgendwo vorne). Die sqlite3 liegt im Netzwerk. Das macht an einem Client mit Kabel nix aus. Aber über WLAN wird es schnell langweilig....

Ob MySql nun die sqlite3 ersetzt weiss ich noch nicht. Dafür werde ich dann mal Speedtests machen, um zu sehen was besser ist. Weil beides hat seine vor und nachteile...

Zitat:

Zitat von DelTurbo (Beitrag 1130492)

Kurz noch zu dem blob-feld. Daran sollten wir uns nun nicht aufhängen. Das ist bei sqlite3 schneller. Ich klicke z.b. auf einen Path. Da ich in dem moment die ID habe, brauch ich nur die "FilePointer" zu lesen und kann sie direkt anzeigen. Das ist bei WLAN schneller, als wenn ich nur einen Pointer zurück auf den path habe. Weil dann muss sqlite3 die ganze DB lesen. Die datenbank ist 300MB gross. Bis die durch das WLAN ist, ist der Akku leer :)

[Sir Rufo]

Dann solltest du aber auch für die Tests mit dem MySQL dieses BLOB Feld nicht benutzen sondern den RDBMS Weg gehen ;)

Lege doch einfach in der Tabelle mit den FileNamen den Verweis auf den Pfad mit rein,Mann kannst du beide Wege ausprobieren.

[DelTurbo]

Zitat:

Zitat von Sir Rufo (Beitrag 1130614)

Lege doch einfach in der Tabelle mit den FileNamen den Verweis auf den Pfad mit rein,Mann kannst du beide Wege ausprobieren.

Ist schon drinn :)

[Sir Rufo]

Zitat:

Zitat von DelTurbo (Beitrag 1130615)

Zitat:

Zitat von Sir Rufo (Beitrag 1130614)

Lege doch einfach in der Tabelle mit den FileNamen den Verweis auf den Pfad mit rein,Mann kannst du beide Wege ausprobieren.

Ist schon drinn :)

;) ich sehe wir verstehen uns ;)

[DelTurbo]

Kleine rückmeldung:
738.007 Datensätze bestehen aus

6x : int
1x : vchar (Da steht der Filename drinne)

in ~40 Sekunden. Gemacht mir MySqlDirect. Und es ich noch keine wietergehende optimierung drinn. Nur das wie oben besprochen wurde, also VALUES (1,"filename",2,3,4,5,6),(1,"filename",2,3,4,5,6) , usw.. Da packe ich immer 2.500 stück rein.

Nochmal danke.....

[DelTurbo]

Wie versprochen die "Speed Rückmeldung"

Also, es ist wesentlich schneller wenn ich auf einen Path klicke und dann das FPtrs feld lese und mir die Fileinfos hohle, statt ihm zu sagen "Gib mir alle files die auf den Path zeigen". Das habe ich aber schon vermutet. Es ist halt ein unterschied ob er bei jedem "klick" ~730.000 datensätze absuchen muss, oder nicht.

Ich habe es unter gleichen vorrausetzungen gestestet. Der Debianserver war bei beiden versuchen neu gebootet.

[Sir Rufo]

Aber du hast schon einen Index auf der Spalte Path?
Und die Spalte Path ist auch ein Int-Feld?

Code:

TABLE PATH
PATHID INT PRIMARY
PATHNAME VARCHAR

Code:

TABLE FILES
FILEID INT PRIMARY
PATHID INT INDEX <<--
FILENAME VARCHAR