[gelöst] GUI Andwendung zum Importieren in MySQL sehr langsam

Hallo MyCSharp-Community,

für einen Kunden wurde eine Datenbank auf einem Webserver eingerichtet, die mehrmals in der Woche aktualisiert werden muss. Anfangs wurden die Daten, welche aus einer csv-Datei stammen, mit Hilfe von PHP so aufbereitet, dass jede Zeile in der csv-Datei als Query in einer Datei steht. Es stehen dann die Daten als INSERT-Queries in einer einzigen Datei. Die 700.000 Datensätze hatten in der csv eine Größe von 70MB eingenommen.
Nach dem Umwandeln durch das Script hat die Datei dann eine Größe von 170MB. Diese Datei mussten wir dann auf den Server laden und haben die Datei dort mit einem Script importiert.
Dieser Vorgang hat 1h gedauert.

Da man dem Kunden so eine Arbeit nicht zumuten kann, haben wir uns dazu entschlossen ein Programm in C# zu schreiben, in dem man nurnoch die zu importierende Datei auswählt und dann passiert alles von alleine. Ich habe jetzt aber feststellen müssen, dass mein Programm für diesen Vorgang 7h braucht.

Mein Programm macht im Prinzip folgendes. Ich lese die csv-Datei zeilenweise aus und bauen mit den Daten dann eine Query zusammen.
Mache also folgendes:
Ich lese die Keys aus einer Datei und die Values kommen aus der csv-Datei.

sqlQuery = "INSERT INTO meineTabelle SET (" + keys + ") VALUES (" + values + ");";

Diese Query schicke ich dann mit Hilfe von einem MySQLCommand an die Datenbank auf dem Webserver. Dann beginnt es wieder von vorne. Ich lese eine Zeile aus, bauen die Query zusammen und schicke sie los.

Wenn ich das Programm im internen Netzwerk probiere, dann stelle ich fest, dass ich dafür nur 1h brauche, was ja auch mein Ziel ist. Darauf lässt sich ja schließen, dass entweder die Verbindung knapp ist (was ich mir nicht vortellen kann und gleich erläutere), oder eine eventuelle Rückantwort aufgrund von Firewall und Router nicht durchkommt und das Programm immer ein bißchen wartet.
Da das Programm ja nichts anderes macht als die csv-Dateien in Queries umzubauen und die dann hochlädt, kommt dann eigentlich eine Datenmenge auf, die auch der Datei mit 700.000 Queries entspricht, also 170MB. Da ich dafür sonst nur 1h brauche, kann es ja daran nicht liegen.
Ich habe bereits im Connectionstring "use compression = true" angegeben um vielleicht ein paar Bytes zu sparen, stelle aber keinen Geschwindigkeitsunterschied fest.

Da bleibt mir nurnoch die Frage, woran es liegen kann, dass mein Programm 7h braucht. Ich hoffe ihr wisst eine Antwort.

Matthias Moser

Diese bzw eine Ähnliche Frage ist alleine auf dieser aktuellen Foren Seite 3 mal beantwortet.

1.
Benutze niemals eine Adhoc Query, sondern immer eine parametriesierte Query
mit der ParameterCollection benutzen.

2.
Immer bei Massenoperationen eine Trnsaction vorher öffnen, und erst am ende Committen.
Sonst wird das für jedes einzelne Kommand getan.

3.
Schonmal Den Dump von MySql angeschaut?

Neben den o.g. Sachen könntest du dir auch mal LOAD DATA INFILE anschauen. Eine weitere Möglichkeit sind Prepared Statements, muss aber seperat aktviert werden, da es laut Aussage des Entwicklers vom MySql .Net Connector zu Dateninkosistenzen führen kann.

Hallo,

ich habe die Tips von FZelle und x86fanboy ausprobiert und musste feststellen, dass bei mir kein Geschwindigkeitsvorteil zu spüren ist. Der Import benötigt immernoch so lange. Hat die Community vielleicht noch eine Idee, warum der Import so lange dauert?

Glaube ich nicht, das es mit meinen Empfehlungen genauso langsam ist.
Da wirst Du in der Umsetzung etwas suboptimal gelöset haben.

                MySqlCommand myCommand = new MySqlCommand();
                myCommand.Connection = conn;
                myCommand.CommandText = "START TRANSACTION";
                myCommand.ExecuteNonQuery();

                // In myKeys stehen die Keys für die Query als Array
                for (int x = 0; x < myKeys.Length; x++)
                {
                    // Hier setze ich die Parameter für die Query, die dann später nurnoch ersetzen muss.
                    param += ",?" + x;
                }
                myCommand.CommandText = "INSERT INTO " + table + " ( " string.Join(",",myKeys) + ") VALUES ( " + param + ")";
                myCommand.Prepare();
                // Ich übergebe dem Server die Query und schicke ihm dann nurnoch die Values um Traffic zu sparen und somit 
                //schneller zu importieren
/*

... der Teil der die Daten aufbereitet und die "Values" in das Array myValues schreibt ...

\*/

                // Hier ersetze ich immer einen Parameter mit einem Wert.
                // Diesen Vorgang mache ich so oft, wie es Keys (oder Values) gibt. Ich ersetze somit alle Parameter und habe dann
                //am Ende eine fertige Query
                myCommand.Parameters.AddWithValue(Convert.ToString(counter), "'" + myValues[counter].Trim() + "'");


                //Query abschicken
                myCommand.ExecuteNonQuery();


                //Wenn alle Queries abgeschickt
                myCommand.CommandText = "COMMIT";
                myCommand.ExecuteNonQuery();

Ich benutze jetz parametisierte Queries und starte eine Transaction. Geht trotzdem nicht schneller.

Ich sage ja, das Du es nicht so machst wie gesagt.

Du sollst Connection.BeginTransAction() benutzen und dies dem Command
dann auch mit übergeben, denn sonst macht jedes Command nocheinmal
eine auf.

Auch das AddWithValue ist Suboptimal.

dieses mal habe ich das aber richtig gemacht.

verbindung geöffnet.

mit der verbindung einen commander und eine transaction gestartet.

                MySqlCommand myCommand = conn.CreateCommand();
                MySqlTransaction trans = conn.BeginTransaction();
                myCommand.Connection = conn;
                myCommand.Transaction = trans;

dann baue ich ne query um parameter zu nutzen

                for (int x = 0; x < makeRealKeys(myKeys).Length; x++)
                {
                    param += ",?" + x;
                    myCommand.Parameters.Add("?" + x.ToString(),MySqlDbType.String);
                }

myCommand.CommandText = "INSERT INTO " + table + " ( " + string.Join(",",makeRealKeys(myKeys)) + ") VALUES ( " + param + ")";

dann gehe ich zeile für zeile in meiner datei vor und überprüfe und verändere die werte nach meinen wünschen.

nach jeder überprüfung eines wertes setze ich den parameter dafür

myCommand.Parameters["?" + xyz.ToString()].Value = myValues[xyz].Trim();

wenn ich alle parameter gesetzt habe, wird die query natürlich abgeschickt:

myCommand.ExecuteNonQuery();

wurden alle queries abgeschickt, sage ich meiner transaction, dass er die einstellungen übernehmen soll:

trans.Commit();

so soll es wohl aussehen und so habe ich es der doku vom mysqlConnector entnommen (v5.0.6.0), trotzdem hat sich an der geschwindigkeit nichts geändert.

hat noch jemand eine idee, warum das so langsam sein könnte und wie ich das problem behebe?

vielleicht solltest du mal beim kunden testen wie hoch seine uploadgeschwindigkeit ist

nachtrag: 1h im internen netzwerk finde ich aber auch viel zu hoch... überleg mal 170 mb kopieren wie lange das normaler weise dauert... vielleicht solltest du deine algorithmen mal prüfen wo da der flaschenhals steckt

wie schaun denn deine daten aus und wie verarbeitest du diese?
messe mal die zeit die du für 1x auslesen, umwandeln und in db eintragen brauchst

danke für die idee an sheitman. ich hab mal mit der stopwatch-klasse die zeiten gemessen.

ich habe 4 werte messen lassen. (durchschnittswerte stehen in klammern)

1. gesamtzeit pro datensatz (32531,36647 ticks)
2. zeit für bearbeitung der daten (87,15396324 ticks)
3. benötigte zeit um query abzuschicken (11559,57218 ticks)
4. fortschrittsbalken aktualisieren (14796,12586 ticks)
   zu 4.: ich habe das importieren in einem asynchronen thread, das aktualisieren des fortschrittsbalken mache ich über invoke.

        private void ZeigeFortschritt(int schritte, int maximal)
        {
            if (PBfortschritt.InvokeRequired == false)
            {
                PBfortschritt.Maximum = maximal;
                PBfortschritt.Value = schritte;
                lImport.Text = schritte+"/"+maximal;
            }
            else
            {
                ZeigeFortschrittDelegate fortschritt = new ZeigeFortschrittDelegate(ZeigeFortschritt);
                this.Invoke(fortschritt,new object[] {schritte,maximal});
            }

        }

mir ist dabei dann folgendes aufgefallen:

2. ist immer im bereich von 70 - 100 ticks, während 3. und 4. sehr unterschiedlich ausfallen können, besonders das aktualisieren des fortschrittsbalken (ProgressBar). da liegt das minumum bei 5.000 ticks und kann bis zu 700.000 dauern. ich finde das im vergleich zu den 87 ticks, um die daten aufzubereiten, doch sehr gravierend.
   queries abschicken, also 3., kann auch groß werden, aber dort lässt sich wohl momentan nicht viel schrauben.

da ja der fortschrittsbalken wirklich viel ausbremst, stelle ich mir die frage, ob ich das beschleunigen kann. der balken muss drin bleiben um zu erkennen, dass überhaupt etwas passiert. wenn es allerdings ein anderes control gibt, dass mir die anwendung nicht so ausbremst, wäre ich über den tip sehr froh, ich gehe aber nicht davon aus, dass es sowas gibt.

fazit: ich sehe jetzt kaum einen weg um das programm zu beschleunigen, ich hoffe ihr konnt mich eines besseren belehren!

ich hab mittlerweile meine geschwindigkeit drosseln können und sehe somit das thema als beendet. die lösung:

1. im connectionstring hatte ich auch noch compression=true stehen. das hat das ganze sehr stark ausgebremst, ist also auch rausgeflogen.

2 .ich lasse meine progressbar nurnoch alle paar 1000 imports aktualisieren -> enormer geschwindigkeitsboost

3. in meine insertquery baue ich mehrere inserts ein:

INSERT INTO tabelle (key1,key2,key3...) VALUES (val1,val2,val3...),(val1,val2,val3...)...
(infos dazu in der MySQL-doku)

solang die anzahl der keys mit den values übereinstimmt funktioniert das prächtig.

nice =)

auf weiviel ist das übertragen denn nun gesunken durch deine maßnahmen?

ich habe es nicht bei den vollen datensätzen gemessen, aber für 6000 datensätze braucht er jetzt statt 1min und 20sek nurnoch 15sek im internen netzwerk.

bei den 700.000 datensätzen war er im internen netzwerk nach ungefähr 5min statt 1h fertig.