Tabellenmigration / Sinnvolles Tabellendesign?

  • C#

Es gibt 11 Antworten in diesem Thema. Der letzte Beitrag () ist von ErfinderDesRades.

    Tabellenmigration / Sinnvolles Tabellendesign?

    Hallöchen,


    ich habe vor naja langer Zeit ein Tool geschrieben das Daten sammelt, archiviert und für mich zugänglich macht per Website.
    Nun war ich damals etwas begrenzt was das Design der Datenbank / Tabellen angeht und nun steht alles an Infos in jeder Zeile in einer Tabelle.
    Die "alte/jetzige" Tabelle sieht so aus:




    Sehr kontraproduktiv da es ja Videos vom gleichen Uploader gibt sowie Videos in gleichen Kategorien und natürlich wird nicht das Video "subscribed" sondern der Benutzer.
    Daher habe ich ein wenig rumgebastelt und folgendes Modell entworfen:



    Die erste Frage machen die Tabellen wirklich mehr Sinn? Meiner Meinung nach schon aber verbessern kann man sich ja immer.

    Die zweite Frage ist wie migriere ich das am schnellsten? Ich hatte mit eine Konsolenanwendung geschrieben die erstmal alle Daten in ein Dataset schreibt und
    dieses gehe ich dann durch und erstelle Kategorien,User sowie die Kategorien->Video Verknüpfung in der "videokategorie" Tabelle. Allerdings dauert das sehr sehr sehr lange.
    Nach 20 Minuten war ich bei 4000 Einträgen von 1,9Millionen.

    Habt ihr da Anregungen für mich?
    Grüße , xChRoNiKx

    Nützliche Links:
    Visual Studio Empfohlene Einstellungen | Try-Catch heißes Eisen

    Jo, für mich sieht das erstmal sinnvoll aus.
    Vewunderlich nur, dass du nicht den DatasetDesigner nimmst, um das TabellenDesign zu machen.

    Zur Geschwindigkeit kann man mangels Code-Ansicht nur die etwas allgemeine Empfehlung aussprechen: Schreibe effizienteren Code, wenn der derzeitig nicht schnell genug ist.

    Danke erstmal für das Feedback.
    Hab das Datenbanken Zeugs direkt in PhpMyAdmin gemacht und dort kann man auch auf nen Designer klicken um da alles zu sehen.

    Ja Code hatte ich erstmal extra weg gelassen da ich dachte es gibt da so nen "Universal-Migrations-Zaubertrick". Aber hier mal der Code.
    Ist nicht Perfekt sondern nur zweck dienlich und kommt auch nur einmal zum migrieren zum Einsatz.

    Die Crawler.VideoRow ist quasi die gleiche Abbildung wie im Post#1 die "alte/jetzige" Tabelle halt nur in nem Dataset.



    Und der Quellcode:
    Spoiler anzeigen

    C#-Quellcode

    1. private void button1_Click(object sender, EventArgs e)
    2. {
    3. MySqlConnection conn = new MySqlConnection(ConnectionstringNeu);
    4. conn.Open();
    5. int all = db.Video.Rows.Count;
    6. int x = 0;
    7. foreach (Crawler.VideoRow row in db.Video.Rows)
    8. {
    9. x += 1;
    10. label1.Invoke((MethodInvoker)delegate
    11. {
    12. label1.Text = $"{x.ToString()} von {all.ToString()}";
    13. });
    15. int vidId = ErstelleVideo(row, conn);
    16. int katId = 0;
    17. if (row.categories != "")
    18. {
    19. List<string> Kategorien = row.categories.Split(';').ToList<string>();
    20. foreach (string kategorie in Kategorien)
    21. {
    22. katId = HoleKategorieId(kategorie, conn);
    23. ErstelleVideoKategorie(vidId, katId, conn);
    24. }
    25. }
    26. }
    27. }
    28. public int ErstelleVideo(Crawler.VideoRow row, MySqlConnection conn)
    29. {
    30. MySqlCommand command = conn.CreateCommand();
    31. command.CommandText = "INSERT INTO `video` (`id`, `extern_id`, `titel`, `url`, `laenge`, `user_id`, `watched`, `uploaddate`) VALUES " +
    32. "(NULL, @extid, @titel, @url, @laenge, @userid, @watched, @timest);";
    33. command.CommandTimeout = 3000;
    34. command.Parameters.AddWithValue("@extid", row.video_id);
    35. command.Parameters.AddWithValue("@titel", row.titel);
    36. command.Parameters.AddWithValue("@url", row.url);
    37. command.Parameters.AddWithValue("@laenge", row.duration);
    39. int userId = HoleUserId(row.uploader, conn);
    41. command.Parameters.AddWithValue("@userid", userId);
    42. command.Parameters.AddWithValue("@watched", (row.watched) ? 1 : 0);
    43. command.Parameters.AddWithValue("@timest", ((DateTimeOffset)row.uploaddate).ToUnixTimeSeconds());
    44. command.ExecuteNonQuery();
    45. return Convert.ToInt32(command.LastInsertedId);
    46. }
    48. public void ErstelleVideoKategorie(int vidId, int katId, MySqlConnection conn)
    49. {
    50. MySqlCommand command = conn.CreateCommand();
    51. command.CommandText = "INSERT INTO `videokategorie` (`id`, `video_id`, `kategorie_id`) VALUES (NULL, @vidid, @katid); ";
    52. command.CommandTimeout = 3000;
    53. command.Parameters.AddWithValue("@vidid", vidId);
    54. command.Parameters.AddWithValue("@katid", katId);
    55. command.ExecuteNonQuery();
    56. }
    58. public int ErstelleUser(string name, int subscribed, MySqlConnection conn)
    59. {
    60. MySqlCommand command = conn.CreateCommand();
    61. command.CommandText = "INSERT INTO `user` (`id`, `username`, `subscribed`) VALUES (NULL, @usname, @subs); ";
    62. command.CommandTimeout = 3000;
    63. command.Parameters.AddWithValue("@usname", name);
    64. command.Parameters.AddWithValue("@subs", subscribed);
    65. command.ExecuteNonQuery();
    66. return Convert.ToInt32(command.LastInsertedId);
    67. }
    69. public int HoleUserId(string name, MySqlConnection conn)
    70. {
    71. MySqlCommand command = conn.CreateCommand();
    72. command.CommandText = "SELECT id FROM user WHERE username = @name";
    73. command.CommandTimeout = 3000;
    74. command.Parameters.AddWithValue("@name", name);
    75. var obj = command.ExecuteScalar();
    76. int userId = 0;
    77. if (obj == null)
    78. {
    79. userId = ErstelleUser(name, 0, conn);
    80. }
    81. else
    82. {
    83. userId = Convert.ToInt32(obj);
    84. }
    86. return userId;
    87. }
    88. public int HoleKategorieId(string name, MySqlConnection conn)
    89. {
    90. MySqlCommand command = conn.CreateCommand();
    91. command.CommandText = "SELECT id FROM kategorie WHERE name = @name";
    92. command.CommandTimeout = 3000;
    93. command.Parameters.AddWithValue("@name", name);
    94. var obj = command.ExecuteScalar();
    95. int katId = 0;
    97. if (obj == null)
    98. {
    99. katId = ErstelleKategorie(name, conn);
    100. }
    101. else
    102. {
    103. katId = Convert.ToInt32(obj);
    104. }
    106. return katId;
    107. }
    109. public int ErstelleKategorie(string name, MySqlConnection conn)
    110. {
    111. MySqlCommand command = conn.CreateCommand();
    112. command.CommandText = "INSERT INTO `kategorie` (`id`, `name`) VALUES (NULL, @katname); ";
    113. command.CommandTimeout = 3000;
    114. command.Parameters.AddWithValue("@katname", name);
    115. command.ExecuteNonQuery();
    116. return Convert.ToInt32(command.LastInsertedId);
    117. }
    Grüße , xChRoNiKx

    Nützliche Links:
    Visual Studio Empfohlene Einstellungen | Try-Catch heißes Eisen

    ich würde nicht jeden zu erzeugenden Datensatz gleich an die DB senden.
    Mach das typDataset richtig, also leg da alle Tabellen an, wie sie in die DB sollen. Inklusive Relationen (Bei Php-gepflegten Datenbanken werden die Relationen leider oft unterschlagen).
    Am besten wäre, du hättest eine ordentliche Datenbank (also mit Relationen, und diese mit Löschweitergabe), und daraus könntest du dir ein typisiertes Dataset generieren. (Datenquellen hinzufügen - aus Datenbank).
    Dabei entstehen auch TableAdapter, mit denen man Db-Tabellen und Dataset-Tabellen synchronisieren kann (in beide Richtungen).

    Dann könntest du die "schmuddel-Tabelle" abrufen ins Dataset, und dort die komplette Migration innerhalb des Datasets vornehmen.
    Am Ende die neu befüllten Dataset-Tabellen in die DB schubsen.

    Ist schwierig, weil meist zickt VS rum mit MySql - da muss man genau die richtige Connectivity-Libraries installiert haben.
    Und dann muss die DB noch richtig gebaut sein.
    Man musses jdfs. hinkriegen, dass alle Änderungen im Dataset (DataRowstat.Added, .Deleted, .Changed) sauber in die DB synchronisiert werden.

    Also imo muss man das sowieso haben, wenn man eine Db-Anwendung schreibt.

    Und wennde das hast, dann ist die Migration eben auch einfach machbar wie gesagt, und wie eiglich jede Db<->Dataset - Datenverarbeitung vonstatten geht:
    1. Daten vonne DB ins Dataset holen
    2. Daten im Dataset verarbeiten
    3. Dataset komplett mitte Db synchronisieren (.UpdateAll)

    Sooo jetzt erstmal danke für die Anregungen und Vorschläge ich habe das DataSet nun so aufgebaut (das wurde so aus der MySQL DB halt alles generiert):



    Nun habe ich dann angefangen die Daten erstmal im Dataset zu migrieren halt hauch row für row in der alten Tabelle durch gehen und die neuen Zeilen
    in den anderen Tabellen erstellen.
    Klappt auch. Hatte das gestern mit 30000 Zeilen getestet und klappte wunderbar auch die Übertragung zurück zur MySQL Datenbank.
    Hier mal der Code zum migrieren:
    Spoiler anzeigen

    C#-Quellcode

    1. int counter = 1;
    2. foreach (Crawler.videos_neuRow row in db.videos_neu.Rows)
    3. {
    4. progressText($"{counter.ToString()} von {allVideos.ToString()} fertig");
    5. Crawler.videoRow videorow = db.video.NewvideoRow();
    6. var userrow = db.user.FirstOrDefault(u => u.username == row.videoUploader);
    7. int userId = 0;
    8. if(userrow == null)
    9. {
    10. Crawler.userRow newUser = db.user.NewuserRow();
    11. newUser.username = row.videoUploader;
    12. newUser.subscribed = row.subscribedStatus;
    13. db.user.Rows.Add(newUser);
    14. userId = newUser.id;
    15. } else
    16. {
    17. userId = userrow.id;
    18. }
    20. videorow.extern_id = row.videoId;
    21. videorow.titel = row.videoTitle;
    22. videorow.url = row.videoUrl;
    23. videorow.laenge = row.videoDuration;
    24. videorow.user_id = userId;
    25. videorow.watched = row.watchedStatus;
    26. videorow.uploaddate = row.videoUploadDate;
    27. db.video.Rows.Add(videorow);
    29. int videoId = videorow.id;
    31. if (row.videoCategories != "")
    32. {
    33. List<string> Kategorien = new List<string>();
    35. if (row.videoCategories.Contains(","))
    36. {
    37. Kategorien = row.videoCategories.Split(',').ToList<string>();
    38. } else
    39. {
    40. Kategorien = row.videoCategories.Split(';').ToList<string>();
    41. }
    43. foreach (string kategorie in Kategorien)
    44. {
    45. var katrow = db.kategorie.FirstOrDefault(k => k.name == kategorie.Trim());
    46. int katid = 0;
    47. if (katrow == null)
    48. {
    49. Crawler.kategorieRow newKatrow = db.kategorie.NewkategorieRow();
    50. newKatrow.name = kategorie.Trim();
    51. db.kategorie.Rows.Add(newKatrow);
    52. katid = newKatrow.id;
    53. }
    54. else
    55. {
    56. katid = katrow.id;
    57. }
    59. Crawler.videokategorieRow vidkatrow = db.videokategorie.NewvideokategorieRow();
    60. vidkatrow.video_id = videoId;
    61. vidkatrow.kategorie_id = katid;
    62. db.videokategorie.Rows.Add(vidkatrow);
    63. }
    64. }
    65. counter += 1;
    66. }


    Das einzige was komisch ist es dauert trotzdem noch unheimlich lange.
    Um 10:54 Uhr heute habe ich den Vorgang gestartet und jetzt um 17:27 Uhr sind wir bei 1.318.250 migrierten Einträgen von 2.049.079 Einträgen.
    Die noch nicht in der Datenbank sind sondern erstmal nur im Dataset.

    Hab ich da noch einen Denkfehler drin?
    Grüße , xChRoNiKx

    Nützliche Links:
    Visual Studio Empfohlene Einstellungen | Try-Catch heißes Eisen

    Für eine Hierarchisierung einer flachen Tabelle sind viele Suchvorgänge erforderlich.
    Weil die ganzen Dopplungen der FlachTabelle sind dabei ja auszusieben.
    Diese Suchvorgänge sind bei dir sehr ineffektiv.
    Mit Hilfe von Dictionaries liesse sich das sicher enorm steigern (1000-fach oderso).

    Wenn du mir eine Sample-Anwendung bastelst, mit sonem Dataset drinne, welche dann auch aus einer Datei mw. 1000 Flachdatensätze laden kann, dann könnte ich sone Optimierung basteln.

    Eigentlich soll man ja erst Performance-Analysen machen, obs wirklich die Aufbereitungs-Methode ist, die so dauert.
    Eventuell zeigst du die Millionen Datensätze in einem DGV an - dann hätte ich eine andere Idee, warum das nicht vorangeht ;)

    Noch was anderes ist, dass ich auch noch nie mit einem Dataset mit Millionen Datensätzen gearbeitet hab.
    Vielleicht ist da auch iwann Ende der Bohnenstange.

    Hallöchen,

    ich habe gestern Abend noch etwas mehr rumgebastelt und im Internet recherchiert und da bin ich auf einen Beitrag im CodeProject gestoßen:
    codeproject.com/Tips/790019/Slow-LINQ-Methods

    Dort wird die Performance von FirstOrDefault sowie anderen Methoden getestet und es stellt sich halt raus das die sehr sehr langsam sind.

    Also habe ich in den betroffenen Zeilen:
    Tabellenmigration / Sinnvolles Tabellendesign? und
    Tabellenmigration / Sinnvolles Tabellendesign?

    Mal auf .Select() umgestellt und siehe da um alles zu migrieren braucht es jetzt nur noch ~20 Minuten.

    Soll ich dir @ErfinderDesRades trotzdem noch nen Sample machen?

    Ansonsten schaue ich nun mal ob alles ordentlich durch läuft also auf der Upload in die Datenbank am Ende und falls ja würde ich das Thema als erledigt markieren.
    Grüße , xChRoNiKx

    Nützliche Links:
    Visual Studio Empfohlene Einstellungen | Try-Catch heißes Eisen

    Pardon ja klar. Sind wie gesagt nur FirstOrDefault durch Select ersetzt: (hier in Z.13 und Z.55)

    C#-Quellcode

    1. public static string escapestring(string t)
    2. {
    3. return t.Replace("\'", "''").Replace("*", "[*]");
    4. }
    6. public void convertDatabase()
    7. {
    8. int counter = 1;
    9. foreach (Crawler.videos_neuRow row in db.videos_neu.Rows)
    10. {
    11. progressText($"{counter.ToString()} von {allVideos.ToString()} fertig");
    12. Crawler.videoRow videorow = db.video.NewvideoRow();
    13. var userrow = db.user.Select($"username = '{escapestring(row.videoUploader)}'");
    14. int userId = 0;
    15. if (userrow.Length == 1)
    16. {
    17. Crawler.userRow nuserrow = (Crawler.userRow)userrow[0];
    18. userId = nuserrow.id;
    19. }
    20. else
    21. {
    22. Crawler.userRow newUser = db.user.NewuserRow();
    23. newUser.username = row.videoUploader;
    24. newUser.subscribed = row.subscribedStatus;
    25. db.user.Rows.Add(newUser);
    26. userId = newUser.id;
    27. }
    29. videorow.extern_id = row.videoId;
    30. videorow.titel = row.videoTitle;
    31. videorow.url = row.videoUrl;
    32. videorow.laenge = row.videoDuration;
    33. videorow.user_id = userId;
    34. videorow.watched = row.watchedStatus;
    35. videorow.uploaddate = row.videoUploadDate;
    36. db.video.Rows.Add(videorow);
    38. int videoId = videorow.id;
    40. if (row.videoCategories != "")
    41. {
    42. List<string> Kategorien = new List<string>();
    44. if (row.videoCategories.Contains(","))
    45. {
    46. Kategorien = row.videoCategories.Split(',').ToList<string>();
    47. }
    48. else
    49. {
    50. Kategorien = row.videoCategories.Split(';').ToList<string>();
    51. }
    53. foreach (string kategorie in Kategorien)
    54. {
    55. var katrow = db.kategorie.Select($"name = '{escapestring(kategorie.Trim())}'");
    56. int katid = 0;
    57. if(katrow.Length == 1)
    58. {
    59. Crawler.kategorieRow nkatrow = (Crawler.kategorieRow)katrow[0];
    60. katid = nkatrow.id;
    61. } else
    62. {
    63. Crawler.kategorieRow newKatrow = db.kategorie.NewkategorieRow();
    64. newKatrow.name = kategorie.Trim();
    65. db.kategorie.Rows.Add(newKatrow);
    66. katid = newKatrow.id;
    67. }
    69. Crawler.videokategorieRow vidkatrow = db.videokategorie.NewvideokategorieRow();
    70. vidkatrow.video_id = videoId;
    71. vidkatrow.kategorie_id = katid;
    72. db.videokategorie.Rows.Add(vidkatrow);
    73. }
    74. }
    75. counter += 1;
    76. }
    77. }
    Grüße , xChRoNiKx

    Nützliche Links:
    Visual Studio Empfohlene Einstellungen | Try-Catch heißes Eisen

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „EaranMaleasi“ ()

    na, da bin ich echt platt.
    Wie hat mans geschafft, .FirstOrDefault so miserabel zu programmieren?

    Weil die Substitution läuft die Auflistung ja komplett durch, indem .Count aufgerufen wird.
    Hingegen ein korrekt implementiertes .FirstOrDefault würde ja abbrechen, sobald ein erstes verwertbares Ergebnis auftritt - müsste also schon von daher schneller sein als .Count.

    Aaaber halt!! deine neue Version ist ja falsch!
    Die Version in post#5 hat ein .FirstOrDefault mit Bedingung, deine .Select-VAriante erwählt den nächstbesten, und generiert auch nur einen String.
    Das dürfte m.E. eine Exception geben - wenn da in #17 der String in eine userRow gecasted wird.

    Dieser Beitrag wurde bereits 2 mal editiert, zuletzt von „ErfinderDesRades“ ()

    ErfinderDesRades schrieb:

    Die Version in post#5 hat ein .FirstOrDefault mit Bedingung, deine .Select-VAriante erwählt den nächstbesten, und generiert auch nur einen String.
    Das dürfte m.E. eine Exception geben - wenn da in #17 der String in eine userRow gecasted wird.


    Also das .Select gibt mir ein Array aus DataRow Objekten zurück siehe: docs.microsoft.com/de-de/dotne…ble_Select_System_String_
    Um zu prüfen ob ja überhaupt min. eine DataRow da ist checke ich ja nach ob das Array.Length == 1 ist.
    Und erst wenn ich SICHER bin das eine DataRow enthalten ist caste ich die erste (Index 0) DataRow zu der userRow.
    Da ich in der Select Anweisung ja nur auf wirklich den gleichen Usernamen prüfe kann es da keine doppelten geben / mehrere Zeilen.

    Oder verstehe ich dich falsch?

    Btw. ist das Migrieren komplett fertig und durchgelaufen. Alles ohne Fehler / Exceptions.
    Grüße , xChRoNiKx

    Nützliche Links:
    Visual Studio Empfohlene Einstellungen | Try-Catch heißes Eisen

    Ah - du hast recht! Das ist ja nicht Linq-Select(), sondern DataTable.Select()!
    DataTable.Select() verwende ich normal nie, weil ist nicht typisiert. Ich verwende immer Linq - aber in diesem Falle scheint das ja performance-Relevant.
    Jo, jetzt kann ich mir das auch zusammenreimen. DataTable.Select() hält intern ein DataView, also eine Liste mit einem Index.
    Ist also eine vergleichbare Optimierung wie das was ich vorhätte mit meinen Dictionaries.
    Dann bin ich ja beruhigt, und .FirstOrDefault ist nicht miserabel programmiert.
    .FirstOrDefault ist halt ein linearer Durchgang durch eine Liste - kommt natürlich gegen einen Index nicht an.

    Hmm- dassis ja interessant - das wäre ja ein Grund, sich eine typisierte typDataTable.Select(string) - Extension zu basteln.

    Zu meiner Schande mussich gestehen, den CP-Artikel erst jetzt nachgeguckt zu haben.
    Dazu noch eine Anregung an dich: Rate den Tip up (mit 5!), wenn er dir nützlich war.
    Dassis wichtig, weil das Rating-System auf CP ist under-used, und so konnten da "Seilschaften" entstehen, die gegenseitig ihre mittelmässigen Artikel mit Fleiss hochraten, was wiederum die Artikel-Suche verzerrt, wennde einfach mal'n guten Artikel lesen willst.

    Dieser Beitrag wurde bereits 2 mal editiert, zuletzt von „ErfinderDesRades“ ()