Discussion :

[megamario]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[Artemus24]

Salut 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.

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.
@+

[megamario]

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.

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 ....

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à.

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).

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.

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.

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 !!!!

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).

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é.

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,

[noureddine1967]

Bonjour,

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

[megamario]

Bonjour,

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

Pour le moment j'essaie ça:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[Artemus24]

Salut à tous.

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.
@+

[megamario]

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.

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.

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.

[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.
@+

[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.