[C#] Optimisation de l'envoi des données dans MySQL

Version imprimable

Bonjour à tous,

[Historique et contexte, vous pouvez zapper jusqu'à QUESTION en dessous]

Je précise que je suis débutant dans les BDD, j'ai récupéré la mise à jour d'un logiciel utilisant des bases de données, SQlite en Local, sur plusieurs postes et Oracle ou MySQL sur un serveur.
Les postes doivent pouvoir fonctionne en autonome sans le serveur, et des que le serveur est présent ils synchronise les données. il peut y avoir une grosse quantité de données, notamment des mesures en permanence relevé par des appareils connectés aux postes.

Il est compliqué voir impossible de changer la structure des bases malheureusement, les système peuvent évoluer mais les anciens doivent pouvoir marcher sur le même serveur.

Voila pour le contexte.

Ont a constaté que les conflits de concurrence étaient très nombreux, lors des synchronisations entre le serveur et les bornes avec des crash dù au non respect des clefs secondaire, des pertes de données ou des doublons impossible normalement.
Par exemple le même appareils de mesure avec plusieurs relevé identique, dont la date de démarrage et la fin, ce qui est impossible. Bref ont essaye de colmaté tout cela.

L'un des points étant d'optimisé les envoies des données actuellement très longue.
On a fait des testes avec deux table, TB1 et TB2 plus de 30000 lignes dans TB1, et 350 000 ligne dans TB2. TB1 étant principalement la date de début et fin, ainsi que le numéro de l'appareil, TB2 étant les mesures. Entre les 2 j'ai des clés secondaires et primaires, la clef secondaire de TB2 rappelant la primaire de TB1.

Historique
Pour Oracle ce qui était fait:
Il n'y avait pas d'auto-incrémente de l'ID...Donc:
1) Demande du Max(ID) de TB1
2) Création de la requête d'insertion avec le Max(ID) + 1
3) Envoie de la requête dans TB1.
4) Utilisation de ce Max(ID)+1 pour la clef secondaire dans la TB2

et pour TB2 pareil en insérant donc le Max(ID) +1 précèdent.

Bonjour la concurrence possible.

Ce que nous avons fait:
Rajouter des triggers pour l'Auto-Increment dans oracle, avec la prise en charge et correction éventuel de l'ID s'il est inséré (des anciens postes).
Puis:
1) Création de la requête sans l'ID pour TB1,
2) Un select pour la récupération de l'ID suivant ce que je viens d'insérer (même matériel, même date de début et fin). Et plus un MAX (ID) dangereux.
3) Passer l'ID A TB2 pour sa clé secondaire etc etc..

4) Je met à jour la base local (qui est en SQlite) avec l'ID distant et la date de la mise à jour dans mes 2 tables local.

Cela n'empêche pas les anciens système de fonctionner, mais de planter puisqu'il ne récupère pas l'ID après l'insertion et considère que le max(id) est toujours bon. TB2 risque de ne pas avoir la bonne clef. Enfin bref le sujet de ma question n'est pas là.

Mais problème, cela met plus de 2 heures pour faire les Updates des tests avec les 30 000 lignes de TB1 et 350 000 de TB2.

[QUESTION]

Pour changer la façons de faire et d'envoyer une grosse quantité de données d'un seul coup, J'ai trouvé pour Oracle, cela:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 OracleConnection Connection; public void ExecuteWriteAllData(DataTable insertDT) { string connectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" + serveur_name+ ")(PORT=" + oraclePort + ")) (CONNECT_DATA=(SERVICE_NAME=" + database_name + "))); User Id=" + user_name + ";Password=" + password + ";"; Connection= new OracleConnection(connectionString); using (var bulkCopy = new OracleBulkCopy(Connection, OracleBulkCopyOptions.UseInternalTransaction)) { bulkCopy.DestinationTableName = table; bulkCopy.WriteToServer(insertDT); } Connection.Close(); }
Existe t'il la même chose en MySQL?

Le but étant d'envoyer la totalité des requetés sur TB1, puis récupérer TB1, filtrer éventuellement avec la plage des dates, dans une DataTable.
Recherche dans cette Datatable les ID puis les créer une seconde Datatable à envoyer dans Oracle / ou MySQL ainsi que la mise à jour dans SQlite local.

L'envoie des 350 000 lignes dans Oracle grâce avec cette méthode au dessus n'est que de quelques secondes

Merci

21/01/2023, 10h05
Artemus24

Salut megamario.

Citation:

Envoyé par megamario

Il n'y avait pas d'auto-incrémente de l'ID...Donc:

Quelques remarques :

1) vous n'êtes pas mono utilisateur dans votre SGBDR et les problèmes de concurrences sont à gérer.

2) utiliser le max()+1 pour déterminer la prochaine valeur de l'identifiant est ce qui vient à l'esprit.
Sur un grand nombre de lignes, cette opération, surtout si elle est très fréquente, peut s'avérer désastreuse en terme de performance.
Ce n'est pas la bonne idée pour résoudre votre problème d'auto incrément.

3) créer une table où l'on stocke la prochaine valeur à incrémenter.
Il suffit de lire la table, puis d'incrémenter la valeur lue et de mettre à jour avec cette nouvelle valeur.
C'est simuler ce que fait en interne l'auto incrément.
C'est bien meilleur que le max()+1 car on peut sérialiser les accès à cette table et éviter les doublons de l'auto incrément.

4) pourquoi ne pas utiliser comme valeur, le timestamp(6) avec les millionièmes de secondes ?
La valeur de votre auto incrément serait alors unique.

5) ou bien, basculer dans une nouvelle version de votre SGBDR qui permet de faire des auto incréments nativement.
A mon avis, cela serait la solution la plus adaptée à ce que vous désirez faire.

6) il faut faire la distinction entre l'auto incrément tel que vous le faite et qui prend beaucoup trop de temps.
Et les problèmes de concurrences qui sont dû à la conséquence de la lenteur pour déterminer la prochaine valeur de votre auto incrément.
Il faudrait sérialiser les accès à la table où est stocké votre auto incrément.
Cela va créer un goulot d'étranglement et résoudre le problème des doublons de l'auto-increment.
Cela va nuire aux performances en terme d'accès.

7) le gros de vos problèmes concernent l'intégrité de vos données qui sont dû à l'auto incrément.
C'est ce point qu'il faut résoudre en premier.

8) est-ce que l'auto incrément en local, donc de TB1, fonctionne correctement ?
Dans ce cas, pourquoi ne pas ajouter une colonne donnant l'origine de votre borne, dans le serveur TB2 ?
Cela permet d'éviter de le calculer dans TB2 et de résoudre vos problèmes de conflits.

Citation:

Envoyé par megamario

L'un des points étant d'optimisé les envoies des données actuellement très longue.

Par clef secondaire, je suppose que vous voulez dire "clef étrangère ? Ce point n'est pas clair du tout.

A ma connaissance, il n'existe pas un équivalent sous MySql de ce que vous faites sous Oracle.
La synchronisation sous Mysql se nomme la réplication et se fait automatiquement, à chaque exécution d'une requête.

Cordialement.
Artemus24.
@+
23/01/2023, 09h31
megamario

Citation:

Envoyé par Artemus24

Salut megamario.
Quelques remarques :

1) vous n'êtes pas mono utilisateur dans votre SGBDR et les problèmes de concurrences sont à gérer.

Bonjour,
J'avais mis tous ce texte pour expliquer le contexte, mais merci de l'avoir lu:
Parfaitement d'accord avec vous, comme je l'ai dit, je reprends un projet existant et je ne suis pas du tout spécialiste en BDD, vous l'avez compris je suppose par les termes que j'emploie, pas encore bien encré en moi. Mais c'est la 1ere chose que j'ai vu. Il n'y a pas de gestion des concurrences.

Citation:

Envoyé par Artemus24

2) utiliser le max()+1 pour déterminer la prochaine valeur de l'identifiant est ce qui vient à l'esprit.
Sur un grand nombre de lignes, cette opération, surtout si elle est très fréquente, peut s'avérer désastreuse en terme de performance.
Ce n'est pas la bonne idée pour résoudre votre problème d'auto incrément.

Performance certes, mais concurrence aussi je suppose, si 2, voir plus, de poste client demande en même temps une synchronisation de ces mêmes tables, il va forcément y de forte "chance" d'avoir des erreurs, surtout que le code ne réinterrogeait pas la base pour renseigner les clefs étrangères des tables les utilisant, le Max(ID) était considéré comme bon ....

Citation:

Envoyé par Artemus24

3) créer une table où l'on stocke la prochaine valeur à incrémenter.
Il suffit de lire la table, puis d'incrémenter la valeur lue et de mettre à jour avec cette nouvelle valeur.
C'est simuler ce que fait en interne l'auto incrément.
C'est bien meilleur que le max()+1 car on peut sérialiser les accès à cette table et éviter les doublons de l'auto incrément.

Mon collègue a ajouté des triggers, pour gérer l'auto-incrément et de mon côté je réinterroge la table après l'insert pour récupérer l'ID et l'affecter aux clés étrangères des tables suivante, pour moi ça c'est résolu, mais il n'empêche que les anciennes versions des logiciels tourneront dessus et ne le font pas, malheureusement je n'ai pas les sources de ces anciennes versions. RRRrrr, ce n'est pas le même langage et sur des machines ne supportant pas forcément la nouvelle version, pas vraiment de solution pour ceux-là.

Citation:

Envoyé par Artemus24

4) pourquoi ne pas utiliser comme valeur, le timestamp(6) avec les millionièmes de secondes ?
La valeur de votre auto incrément serait alors unique.

J'y ai pensé et maintenant que vous me le rappeler cela aurait pu être une solution, mais comme vous l'avez vu plus haut j'ai résolu ce problème d'ID, mais il ne faut pas oublier que des anciens poste continue à tourner sur les serveurs et je ne peux pas modifier les tables... Ou du moins il faut que les anciens systèmes continus de fonctionner (avec leur bogue malheureusement).

Citation:

Envoyé par Artemus24

5) ou bien, basculer dans une nouvelle version de votre SGBDR qui permet de faire des auto incréments nativement.
A mon avis, cela serait la solution la plus adaptée à ce que vous désirez faire.

C'est ce que nous avons fait, avec le trigger non seulement il gère l'auto-incrémente, mais aussi les requêtes qui forcent l'ID des anciens poste.
Si à cause de la concurrence l'ID est déjà affecté il l'upgrade. Le souci c'est que le logiciel (ancien) en face aura pas le bon ID pour les clefs étrangères, mais dans tous les cas il ne l'aurai pas eue. Mais je vais voir avec mon collègue si à la place de l'upgrade de l'ID, ont peu ne rien faire, mais créer un retour d'erreur, ainsi le poste client retentera l'insertion lors de la prochaine synchro.

Citation:

Envoyé par Artemus24

6) il faut faire la distinction entre l'auto incrément tel que vous le faite et qui prend beaucoup trop de temps.
Et les problèmes de concurrences qui sont dû à la conséquence de la lenteur pour déterminer la prochaine valeur de votre auto incrément.
Il faudrait sérialiser les accès à la table où est stocké votre auto incrément.
Cela va créer un goulot d'étranglement et résoudre le problème des doublons de l'auto-increment.
Cela va nuire aux performances en terme d'accès.

J'avoue que je n'ai pas tout compris, je vais voir avec mon collègue qui connait mieux les BDD.

Citation:

Envoyé par Artemus24

7) le gros de vos problèmes concernent l'intégrité de vos données qui sont dû à l'auto incrément.
C'est ce point qu'il faut résoudre en premier.

Parfaitement d'accord, c'est pour sa que l'on a fait le trigger pour cela, mais il n'y a pas que sa malheureusement. Dans le logiciel tout passe par des backgroundworker très mal gérer pour l'accès aux BDD local et distante au point que lorsque l'utilisateur renseigne sont appareil de mesure, une synchronisation pouvait avoir lieu en même temps, avec de l'effacement de données puis insertion dans la même table, SQlite, qui n'est pas faite pour avoir un multi accès !!!!

Citation:

Envoyé par Artemus24

8) est-ce que l'auto incrément en local, donc de TB1, fonctionne correctement ?
Dans ce cas, pourquoi ne pas ajouter une colonne donnant l'origine de votre borne, dans le serveur TB2 ?
Cela permet d'éviter de le calculer dans TB2 et de résoudre vos problèmes de conflits.

Dans oracle aucun auto-incrément avait été implanté, j'ai trouvé un commentaire dans le code comme quoi il n'y été pas arrivé, c'est pour cela qu'il utilisait le Max(id), par contre je n'ai pas ce souci dans MySQL, il avait réussi à le faire. Heureusement, c'est MySQL qui est le plus utilisé. Pour rappel sur le serveur on a soit Oracle ou MySQL en local on a SQLite (que je trouve super rapide d'ailleurs).

Citation:

Envoyé par Artemus24

Par clef secondaire, je suppose que vous voulez dire "clef étrangère ? Ce point n'est pas clair du tout.

Vous avez raison, désolé.

Citation:

Envoyé par Artemus24

A ma connaissance, il n'existe pas un équivalent sous MySql de ce que vous faites sous Oracle.
La synchronisation sous Mysql se nomme la réplication et se fait automatiquement, à chaque exécution d'une requête.

Sniffff. Ha mince.

J'ai essayé de faire des ajouts multiples comme
INSERT INTO table_name (column_list)
VALUES
(value_list_1),
(value_list_2),
...
(value_list_n);

Malgré tout c'est très long.

Je vais regarder du coté de Linq (pas link le copain de Zelda ;)), Cela fait un moment que l'on m'en parle, mais cela m'a toujours un peu fait peur ce truc, cela semble tellement puissant.

L'idée aurait été d'insérer la totalité des lignes dans TB1, puis interroger TB1, éventuellement en sélectionnant la plage des dates d'insertion (le champ existant) pour ne pas récupérer toute la table. Puis chercher chaque ID dans le retour. Avant de l'affecter à la table TB2 puis l'insérer.
Je pense que plus on va vite, moins ont laisse le temps aux collisions.

En théorie cela va assez vite, car ont ne devrait jamais avoir de si grosse bases à synchroniser, comme notre essai avec 300 000 lignes. Surtout depuis que j'ai changé le mode de connexion déconnexion, qui était fait à chaque instruction, houaii...

Merci en tout cas d'avoir pris le temps de répondre et d'y avoir apporté tant de détails.

Cordialement,
23/01/2023, 10h40
noureddine1967

Bonjour,

Est ce que vous avez essayé : LOAD DATA INFILE ???

Bonjour,

Non je n'ai pas essayé LOAD DATA, je vais essayer de trouver des infos.

Pour le moment j'essaie ça:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
    public void BulkInsertMySQL(DataTable table, string tableName)
    {
        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            connection.Open();
 
            using (MySqlTransaction tran = connection.BeginTransaction(IsolationLevel.Serializable))
            {
                using (MySqlCommand cmd = new MySqlCommand())
                {
                    cmd.Connection = connection;
                    cmd.Transaction = tran;
                    cmd.CommandText = $"SELECT * FROM " + tableName + " limit 0";
 
                    using (MySqlDataAdapter adapter = new MySqlDataAdapter(cmd))
                    {
                        adapter.UpdateBatchSize = 10000;
                        using (MySqlCommandBuilder cb = new MySqlCommandBuilder(adapter))
                        {
                            cb.SetAllValues = true;
                            int nb = adapter.Update(table);
                            tran.Commit();
                        }
                    };
                }
            }
        }
    }

Mais cela ne semble pas fonctionner. nb = 0

Le code complet :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
 
  public void Start(string tableName, List<ClsLink> linkList)
    {            
        DataTable table = new DataTable();
 
        // Getting datatable layout from database
        table = GetDataTableLayout(tableName);
 
        // Pupulate datatable
        foreach (ClsLink link in linkList)
        {
            DataRow row = table.NewRow();                
            //row["LinkURL"] = link.LinkURL;
            //row["CreateDate"] = link.CreateDate;
            //row["Titel"] = link.Titel;
            table.Rows.Add(row);
        }
 
        BulkInsertMySQL(table, tableName);
        // Enjoy
    } 
 
 
    public DataTable GetDataTableLayout(string tableName)
    {
        DataTable table = new DataTable();
 
        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            connection.Open();
            // Select * is not a good thing, but in this cases is is very usefull to make the code dynamic/reusable 
            // We get the tabel layout for our DataTable
            string query = $"SELECT * FROM " + tableName + " limit 0";
            using (MySqlDataAdapter adapter = new MySqlDataAdapter(query, connection))
            {
                adapter.Fill(table);
            };
        }
 
        return table;
    }
 
    public void BulkInsertMySQL(DataTable table, string tableName)
    {
        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            connection.Open();
 
            using (MySqlTransaction tran = connection.BeginTransaction(IsolationLevel.Serializable))
            {
                using (MySqlCommand cmd = new MySqlCommand())
                {
                    cmd.Connection = connection;
                    cmd.Transaction = tran;
                    cmd.CommandText = $"SELECT * FROM " + tableName + " limit 0";
 
                    using (MySqlDataAdapter adapter = new MySqlDataAdapter(cmd))
                    {
                        adapter.UpdateBatchSize = 10000;
                        using (MySqlCommandBuilder cb = new MySqlCommandBuilder(adapter))
                        {
                            cb.SetAllValues = true;
                            adapter.Update(table);
                            tran.Commit();
                        }
                    };
                }
            }
        }
    }

Ce que j'ai fait pour les tests, récupérer les données de la table dans une datatable puis supprimer les données de la table et envoi de la datatable directement dans BulkInsertMySQL, mais c'est vide. nb = 0

23/01/2023, 15h22
Artemus24

Salut à tous.

Citation:

Envoyé par noureddine1967

Bonjour,

Est ce que vous avez essayé : LOAD DATA INFILE ???

C'est pour charger un fichier texte vers une table MySql.

Megamario recherche un moyen de transférer d'une manière automatique, le contenu d'une table sous MySql vers une autre table sous Oracle.
Manuellement, on utilise l'export avec MySqldump sous MySql et je suppose un import sous Oracle.
Mais il faudrait que le fichier soit compréhensible par Oracle, ce qui n'est pas certain.

Sinon, il existe la méthode qui consiste à créer dans un langage un traitement qui va consister à lire la table MySql et à la recopier ligne par ligne vers une table Oracle.

J'ai déjà fait cela en PHP, mais c'était pour une mise à niveau ponctuelle, et non un traitement quotidien.

Cordialement.
Artemus24.
@+
23/01/2023, 15h34
megamario

Citation:

Envoyé par Artemus24

Salut à tous.

C'est pour charger un fichier texte vers une table MySql.

C'est ce qui me semblait effectivement, mais c'est pas ce que je souhaite faire effectivement.

Citation:

Envoyé par Artemus24

Megamario recherche un moyen de transférer d'une manière automatique, le contenu d'une table sous MySql vers une autre table sous Oracle.

Pas tout à fait, mais oui pour mon test effectivement. Le but étant de remonter les données de la table local en SQlite, vers la base Oracle OU MySQL, j'y suis arrivé avec la base Oracle à envoyer 338000 lignes en 4 secondes environ et j'aimerais faire la même chose ou du moins dans des temps plus raisonnable.
Si j'envoie cela par:
INSERT INTO table_name (column_list)
VALUES
(value_list_1),
(value_list_2),
...
(value_list_n);

J'en ai pour plus d'une heure. Je pense qu'il y a surement une optimisation à faire quand même.

Citation:

Envoyé par Artemus24

Manuellement, on utilise l'export avec MySqldump sous MySql et je suppose un import sous Oracle.
Mais il faudrait que le fichier soit compréhensible par Oracle, ce qui n'est pas certain.

Sinon, il existe la méthode qui consiste à créer dans un langage un traitement qui va consister à lire la table MySql et à la recopier ligne par ligne vers une table Oracle.

J'ai déjà fait cela en PHP, mais c'était pour une mise à niveau ponctuelle, et non un traitement quotidien.

Cordialement.
Artemus24.
@+

Du coup ce n'est pas vraiment cela que je souhaite faire.
24/01/2023, 11h40
Artemus24

Salut megamario.

Est-ce que tous tes SGBDR se trouvent sur le même serveur ?

Je connais trois solutions :

1) celui que j'ai déjà exposé, pour des tous petits transferts.
Je crée un programme (en php) qui va se connecter au deux bases de données, dont l'un est local, en lecture et l'autre distant, en écriture.
Je lis une ligne de la première base de données que j'envoie vers la seconde, et ainsi de suite.
Cela peut s'automatiser par "crontab" sous linux ou "planificateur de tâches" sous windows.
Tu peux le lancer plusieurs fois par jour, pourquoi pas toutes les 1/2 heures.

2)La solution que j'applique quand je désire faire un gros transfert, se fait en trois étapes :
a) déchargement de la base de données en utilisant "mysqldump". C'est pareil qu'un export sous phpmyadmin.
b) transfert en utilisant la commande "pscp.exe" de Putty sous windows vers mon serveur distant.
c) chargement de la base de données en utilisant "mysql". C'est pareil que l'import sous phpmyadmin.

3) L'autre solution que propose noureddine1967, est de décharger la table sous forme d'un fichier séquentiel de type excel (ou csv) et d'utiliser "load local data infile" pour le recharger après l'avoir transférer vers l'autre serveur distant.

Dans ces trois solutions, cela prend du temps en fonction de la volumétrie ainsi que de la vitesse du transfert.

Cordialement.
Artemus24.
@+
24/01/2023, 13h49
megamario

Merci Artemus24,

Les BDD :
- Il y a une BDD SQlite en local sur chaque borne (PC Windows).
- Il y a soit Oracle, soit MySQL sur un serveur type Windows qui est fait pour synchroniser tout ça.

Le Logiciel :
- Seule les logiciels des bornes se synchronisent.
- La synchronisation s'effectue en général toutes les 5 minutes.
- Un logiciel est présent sur le serveur pour ajouter des paramètres (ajout d'un utilisateur par exemple) ou pour consulter les mesures.
- Le plus gros parc en activité avec Oracle possède 70 bornes environ.
- Il y a beaucoup plus de sites sous MySQL que sous Oracle. Mais je n'en connais pas les détails et les quantité, mais il peut y avoir encore plus de bornes.
- Les tables en local n'ont pas exactement la même structure que la même table distante. Donc je ne peux pas faire de "simples" copier-coller de tables.
- Certaines tables sont montantes et descendantes, d'autres uniquement dans un sens.
-----Par exemple:
---------- Les données des utilisateurs vont de la base distante, vers toutes les bases locales.
---------- Les données des entrées sorties, vont dans les 2 sens, car un utilisateur peut utiliser une borne pour entrer et en utiliser une autre pour sortir.
---------- Les données des mesures vont uniquement dans le sens local vers distante.

- Normalement il n'y aura jamais énormément de données à transmettre. Mais en cas de panne du serveur, cela doit pouvoir fonctionner, avec des restrictions quand même. Les mesures seront remontées lorsque le serveur sera de nouveau présent, et c'est là qu'il y aura le plus de données, mais surement pas 300 000 lignes ça c'est pour les tests.

- J'aimerais vraiment optimiser les transferts de données pour limiter au maximum les concurrences.
Même si elles seront inévitables, vu que certaines bornes ne pourront pas être mises à jour.
Ce qui me surprend entre 1 heure sur MySQL et 4 secondes sur Oracle, il y a une énorme différence. Attention je dit ça en tant que novice dans ce domaine. Même avec SQlite, j'envoie une requête paramétrée avec un dictionnaire contenant les 300 000 lignes et cela va presque aussi vite qu'Oracle.

Je vais regarder vos propositions avec attention en tout cas.