IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

SQL Procédural MySQL Discussion :

accélérer la comparaison de tables


Sujet :

SQL Procédural MySQL

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre confirmé
    Inscrit en
    Avril 2005
    Messages
    75
    Détails du profil
    Informations forums :
    Inscription : Avril 2005
    Messages : 75
    Par défaut accélérer la comparaison de tables
    Bonjour,


    j 'ai développé un pogramme en VB qui fait un back up quotidien de certaines bases sur plusieurs serveurs.
    Pour ce faire je dois comparer le contenu des tables que je vais répliquer avec celui des tables "back upés" afin de voir si les tables ont été modifiées durant la journée.
    Si c est le cas, alors le back up commence.

    tout d abord je decoupe ma table en "sections" d'enreg

    je fais donc un
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    SELECT INTO MonFichier FROM ... where enreg < X and enreg > X
    sur le serveur source et idem sur le serveur cible.

    ensuite je calcule le hash de ces fichiers. si ils sont identiques, je passe à la section suivante. Sinon cette section d enregistrement sera back upée.

    Mon programme marche correctement, mais la comparaison des données est parfois lente (surtout pour les tables de plusieurs millions d enreg...)

    ma question:
    connaissez vous une méthode plus rapide pour comparer et isolé les enregistrements?????


    par avance merci.

  2. #2
    Rédacteur
    Avatar de pcaboche
    Homme Profil pro
    Inscrit en
    Octobre 2005
    Messages
    2 785
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : Singapour

    Informations forums :
    Inscription : Octobre 2005
    Messages : 2 785
    Par défaut Re: accélérer la comparaison de tables
    Qu'est-ce que tu penses de ça? :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    SELECT matableA.*
    INTO fichier.txt
    FROM matableA
    LEFT OUTER JOIN matableB ON (matableA.id=matableB.id)
    WHERE matableA.champ1 != matableB.champ1
    OR matableA.champ2 != matableB.champ2
    OR ...
     
    LIMIT 0, 100
    Ici, on ne relève que les enregistrements qui ont changé et on fait un découpage de 100 en 100 (condition du LIMIT)
    "On en a vu poser les armes avant de se tirer une balle dans le pied..."
    -- pydévelop

    Derniers articles:

    (SQL Server) Introduction à la gestion des droits
    (UML) Souplesse et modularité grâce aux Design Patterns
    (UML) Le Pattern Etat
    Autres articles...

  3. #3
    Membre confirmé
    Inscrit en
    Avril 2005
    Messages
    75
    Détails du profil
    Informations forums :
    Inscription : Avril 2005
    Messages : 75
    Par défaut
    est ce que ca marche si mes bases sont sur 2 serveurs differents??

  4. #4
    Rédacteur
    Avatar de pcaboche
    Homme Profil pro
    Inscrit en
    Octobre 2005
    Messages
    2 785
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : Singapour

    Informations forums :
    Inscription : Octobre 2005
    Messages : 2 785
    Par défaut
    Dans 2 bases différentes, c'est jouable (tu fais mabaseA.matable et mabaseB.matable), mais sur 2 serveurs différents, ça me parait chaud.

    Après, il doit y avoir des méthodes de réplication de base (bases maître/esclave), mais il faut que je me renseigne (quelqu'un d'autre aura peut-être la solution).

    Edit: Visiblement, il faut s'en référer au chapitre 6 du manuel de MySQL (http://dev.mysql.com/doc/refman/5.0/en/replication.html). Je pense que ce sera beaucoup plus propre que de faire des scripts à la main.
    "On en a vu poser les armes avant de se tirer une balle dans le pied..."
    -- pydévelop

    Derniers articles:

    (SQL Server) Introduction à la gestion des droits
    (UML) Souplesse et modularité grâce aux Design Patterns
    (UML) Le Pattern Etat
    Autres articles...

  5. #5
    Membre confirmé
    Inscrit en
    Avril 2005
    Messages
    75
    Détails du profil
    Informations forums :
    Inscription : Avril 2005
    Messages : 75
    Par défaut
    j ai deja etudié la possibilité de la méthode maitre/esclave, mais elle a été écarté par mon boss...

  6. #6
    Rédacteur
    Avatar de pcaboche
    Homme Profil pro
    Inscrit en
    Octobre 2005
    Messages
    2 785
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : Singapour

    Informations forums :
    Inscription : Octobre 2005
    Messages : 2 785
    Par défaut
    Citation Envoyé par mick84m
    j ai deja etudié la possibilité de la méthode maitre/esclave, mais elle a été écarté par mon boss...
    Pourquoi?
    "On en a vu poser les armes avant de se tirer une balle dans le pied..."
    -- pydévelop

    Derniers articles:

    (SQL Server) Introduction à la gestion des droits
    (UML) Souplesse et modularité grâce aux Design Patterns
    (UML) Le Pattern Etat
    Autres articles...

  7. #7
    Membre confirmé
    Inscrit en
    Avril 2005
    Messages
    75
    Détails du profil
    Informations forums :
    Inscription : Avril 2005
    Messages : 75
    Par défaut
    car elle est trop compliquée a gérer parfaitement car il y a beaucoup de serveurs.
    de plus il est délicat de vérifier l avancement des réplications.

  8. #8
    Rédacteur
    Avatar de pcaboche
    Homme Profil pro
    Inscrit en
    Octobre 2005
    Messages
    2 785
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : Singapour

    Informations forums :
    Inscription : Octobre 2005
    Messages : 2 785
    Par défaut
    Parce que tu trouves que de gérer à la main la synchronisation et les accès concurrents en section critique c'est plus simple?
    "On en a vu poser les armes avant de se tirer une balle dans le pied..."
    -- pydévelop

    Derniers articles:

    (SQL Server) Introduction à la gestion des droits
    (UML) Souplesse et modularité grâce aux Design Patterns
    (UML) Le Pattern Etat
    Autres articles...

  9. #9
    Rédacteur
    Avatar de pcaboche
    Homme Profil pro
    Inscrit en
    Octobre 2005
    Messages
    2 785
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : Singapour

    Informations forums :
    Inscription : Octobre 2005
    Messages : 2 785
    Par défaut
    Ah et puis j'oublais, il faut considérer 3 cas:
    - les lignes modifées
    - les lignes ajoutées
    - les lignes supprimées

    Pour l'instant, on ne s'occuppe que des lignes modifiées et ajoutées. Et puis de toute façon, vu que c'est sur deux serveurs différents, pour faire une jointure, ça me parait difficile...
    "On en a vu poser les armes avant de se tirer une balle dans le pied..."
    -- pydévelop

    Derniers articles:

    (SQL Server) Introduction à la gestion des droits
    (UML) Souplesse et modularité grâce aux Design Patterns
    (UML) Le Pattern Etat
    Autres articles...

  10. #10
    Membre confirmé
    Inscrit en
    Avril 2005
    Messages
    75
    Détails du profil
    Informations forums :
    Inscription : Avril 2005
    Messages : 75
    Par défaut
    je trouve que oui, car je sait exactementce qui se passe, où ca se passe et quand ca se passe. j'ai un controle total des replications.
    de plus, les réplications ne se font pas toujours dans le meme sens selon nos besoins, donc pas de "maitres" fixes, de meme pour les "esclaves".
    je gère tres bien les enreg ajoutés, modifiés et supprimés. le probleme est que ma methode est parfois lente.

  11. #11
    Rédacteur
    Avatar de pcaboche
    Homme Profil pro
    Inscrit en
    Octobre 2005
    Messages
    2 785
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : Singapour

    Informations forums :
    Inscription : Octobre 2005
    Messages : 2 785
    Par défaut
    T'as pensé à une synchronisation avec des horloges logiques?

    - Tu créés une table pour stocker une horloge logique (un entier).

    - Dans tes tables, tu ajoutes un champs qui correspond à une horloge logique (ou alors tu fais une table séparée, l'important étant de savoir quelles lignes de quelle table ont été modifiées depuis la dernière horloge logique). A chaque insertion/modification/suppression, tu mets également à jour la valeur de l'horloge à laquelle la modif a été effectuée.

    - Au moment du backup entre 2 serveurs, tu sors la liste de lignes qui ont été insérées/modifiées/supprimées depuis la dernière horloge logique.

    - Une fois le back up effectué, tu mets à jour l'horloge logique des deux serveurs (nouvelleHorlogeLogique := MAX(HL1, HL2)+1 ).

    Bon, ce n'est qu'une idée mais ça pourrait te permettre de simplifier la synchronisation et de pouvoir redéfinir facilement les maîtres et esclaves en fonction des besoins.

    Après, il faut creuser un petit peu du coté de l'algorithmique distribuée.
    "On en a vu poser les armes avant de se tirer une balle dans le pied..."
    -- pydévelop

    Derniers articles:

    (SQL Server) Introduction à la gestion des droits
    (UML) Souplesse et modularité grâce aux Design Patterns
    (UML) Le Pattern Etat
    Autres articles...

  12. #12
    Rédacteur
    Avatar de pcaboche
    Homme Profil pro
    Inscrit en
    Octobre 2005
    Messages
    2 785
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : Singapour

    Informations forums :
    Inscription : Octobre 2005
    Messages : 2 785
    Par défaut
    Petite précision (j'ai peut-être pas été clair): sur chaque serveur, il faut que tu aies une table avec les horloges logiques des autres serveurs (pour savoir à quand remonte la dernière synchro) :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
     Serveur    | HL
    -----------------
     Fry        | 56
     Leila      | 58
     Bender     | 62
     Hermes     | 60
     Farnsworth | 48
     Amy        | 62
     Zoidberg   | 57
    Lors de la synchro entre Fry et Bender, tu compares leurs horloges logiques pour savoir qui est le plus à jour.
    "On en a vu poser les armes avant de se tirer une balle dans le pied..."
    -- pydévelop

    Derniers articles:

    (SQL Server) Introduction à la gestion des droits
    (UML) Souplesse et modularité grâce aux Design Patterns
    (UML) Le Pattern Etat
    Autres articles...

  13. #13
    Membre confirmé
    Inscrit en
    Avril 2005
    Messages
    75
    Détails du profil
    Informations forums :
    Inscription : Avril 2005
    Messages : 75
    Par défaut
    je ne connaissait pas cette methode de table avec horloge logique.
    ca a l air interessant, j vais me renseigner là dessus.

    merci pcaboch


    Si quelqu'un a d autre idées, cela m interesse aussi...

  14. #14
    Rédacteur
    Avatar de pcaboche
    Homme Profil pro
    Inscrit en
    Octobre 2005
    Messages
    2 785
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : Singapour

    Informations forums :
    Inscription : Octobre 2005
    Messages : 2 785
    Par défaut horloges logiques
    J'avais un peu d'algorithmique distribuée au programme de dernière année, mais j'ai pas eu l'occasion de mettre en pratique. J'ai un peu adapté à ton problème.

    Concernant ton problème, j'ai poussé la reflexion un peu plus loin:

    Pour la suite, nous appelerons "hl" la table contenant les horloges logiques pour cahque serveur. On a une table "hl" sur chaque serveur.

    Imaginons que l'on ait une table "table", avec comme identifiant un champ "id". On créé une table "hl_table" pour contenir les horloges logiques de modification de ligne:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    CREATE TABLE hl_table (
      id UNSIGNED INT NOT NULL,
      hl UNSIGNED INT NOT NULL, # Horloge logique
      PRIMARY KEY(id)
    ) ;
    A chaque modification/ajout d'une ligne de "table", on modifie l'enregistrement correspondant dans "hl_table". En cas de suppression de ligne, on supprime l'enregistrement de "table" et on met à jour "hl_table" (pas de suppression dans hl_table).

    ***

    Maintenant, imaginons que "Bender" veuille enregistrer ses modifications sur "Fry". La table "hl" de Bender nous indique que la dernière synchro avec Fry remonte à l'horloge logique 56. On sort la liste des lignes modifiées depuis cette l'horloge logique.

    Sur "Bender", on fait:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    SELECT hl_table.id, table.champ1, ...
    INTO outfile.txt
     
    FROM hl
    INNER JOIN hl_table ON (hl_table.hl > hl.hl)
     
    LEFT OUTER JOIN table
    ON (hl_table.id=table.id)
     
    WHERE (hl.serveur = "Fry")
    Logiquement, avec cette requête, les lignes qui ont été supprimées devraient avoir leurs champs à NULL (à cause du LEFT OUTER JOIN).

    Ensuite, on met à jour les horloges logiques de Fry et Bender, qui est égale à MAX(hlFry, hlBender) + 1 = 62+1 = 63.

    ***

    Maintenant, la plus petite horloge logique est 48. Il est donc inutile de garder la liste des lignes qui ont été modifiées avant cette date. Donc régulièrement, on fait:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    SELECT @minHL:=MIN(hl)
    from hl ;
     
    DELETE FROM hl_table
    WHERE hl<@minHL
    ***
    Comme tu auras pu le remarquer, toutes les tables servant à la gestion d'horloge logique ont leur nom commençant par "hl", ce qui permet de mieux les retrouver.

    ***
    Après, il reste un autre problème: quand les 2 serveurs modifient les mêmes données chacun de leur côté, laquelle prendre? Mais ça, ça dépend entièrement de ton problème (dont on n'a pas connaissance).

    Qu'est-ce que tu penses de tout cela?
    "On en a vu poser les armes avant de se tirer une balle dans le pied..."
    -- pydévelop

    Derniers articles:

    (SQL Server) Introduction à la gestion des droits
    (UML) Souplesse et modularité grâce aux Design Patterns
    (UML) Le Pattern Etat
    Autres articles...

  15. #15
    Membre confirmé
    Inscrit en
    Avril 2005
    Messages
    75
    Détails du profil
    Informations forums :
    Inscription : Avril 2005
    Messages : 75
    Par défaut
    merci beaucoup pour cette explication, je comprend mieux ce que tu appelles horloge logique !!

    c est tres interessant mais je ne sais pas si il va m etre possible de le mettre en place...car en faite chacune de mes bases de données correspond à une application client. nous avons plus de 75 bases...je ne sais pas si il va etre possible d integrer cette methode dans tous nos projets clients.

    en tout cas cette methode métire vraiment d etre étudier. je vais faire quelques tests et voir si c est envisageable.


    MERCI !! 8)

  16. #16
    Rédacteur
    Avatar de pcaboche
    Homme Profil pro
    Inscrit en
    Octobre 2005
    Messages
    2 785
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : Singapour

    Informations forums :
    Inscription : Octobre 2005
    Messages : 2 785
    Par défaut
    Ok, tiens nous au courant.
    "On en a vu poser les armes avant de se tirer une balle dans le pied..."
    -- pydévelop

    Derniers articles:

    (SQL Server) Introduction à la gestion des droits
    (UML) Souplesse et modularité grâce aux Design Patterns
    (UML) Le Pattern Etat
    Autres articles...

  17. #17
    Rédacteur
    Avatar de pcaboche
    Homme Profil pro
    Inscrit en
    Octobre 2005
    Messages
    2 785
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : Singapour

    Informations forums :
    Inscription : Octobre 2005
    Messages : 2 785
    Par défaut
    Citation Envoyé par mick84m
    c est tres interessant mais je ne sais pas si il va m etre possible de le mettre en place...car en faite chacune de mes bases de données correspond à une application client. nous avons plus de 75 bases...je ne sais pas si il va etre possible d integrer cette methode dans tous nos projets clients.
    C'est vrai que c'est un énorme problème: à chaque fois qu'une valeur dans une table est modifiée, il faut aussi modifier l'horloge logique correspondante. Cela implique une modification de l'appli qui utilise la base de données, et ce pour tes 75 clients. Bref, c'est assez ingérable.

    Il m'est venu une idée pour résoudre ce problème sans toucher à l'application: si tu es en MySQL 5.x, alors tu peux utiliser des triggers:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    DELIMITER |
     
    CREATE TRIGGER table__before_insert BEFORE INSERT ON table
      FOR EACH ROW BEGIN
        SELECT @hl:=hl FROM hl WHERE serveur="self" ;
        REPLACE INTO hl_table VALUES (NEW.id, @hl) ;
      END
    |
     
    #Autres triggers (BEFORE UPDATE, BEFORE DELETE)
    ...
     
    DELIMITER ;
    Dans la table "hl", on identifie le serveur sur lequel on est par la valeur "self" (comme ça, ça évite d'adapter le script pour chaque machine).

    Le REPLACE marche de la façon suivante:
    - si la ligne existe, elle est remplaçée
    - si la ligne n'existe pas, REPLACE se comporte exactement comme INSERT
    (Cela évite de tester l'existance de la ligne pour savoir si on fait un INSERT ou un UPDATE)

    Ce qui me chiffone, c'est qu'on récupère l'horloge logique courante à chaque insertion (problème de perfs). Peut-être qu'on pourrait mettre celle-ci directement dans une variable globale (à tester...).

    ***
    Maintenant, le problème, c'est qu'il faut passer en MySQL 5 (frayeurs garanties!).

    En guise de compensation, après, tu pourras être sûr que toutes tes applications clients tournent sur du MySQL 5 (ce qui évite de se poser la question: "Pourquoi ça tourne sur ma version de developpement mais ça ne marche pas sur le client? Merde, un problème de version! Comment le résoudre maintenant?").

    Voilà, j'essaye de trouver des solutions simples à ton problème (ou au moins de donner des idées et d'échanger des points de vue).
    "On en a vu poser les armes avant de se tirer une balle dans le pied..."
    -- pydévelop

    Derniers articles:

    (SQL Server) Introduction à la gestion des droits
    (UML) Souplesse et modularité grâce aux Design Patterns
    (UML) Le Pattern Etat
    Autres articles...

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Comparaison de table présent pas présents ?
    Par lolo_bob2 dans le forum Access
    Réponses: 2
    Dernier message: 30/06/2006, 09h32
  2. [debutant]comparaison de table
    Par manshoon dans le forum Langage SQL
    Réponses: 6
    Dernier message: 15/06/2006, 16h49
  3. Comparaison entre tables
    Par Marmouz dans le forum Access
    Réponses: 3
    Dernier message: 13/04/2006, 15h21
  4. [SQL] vs [VBA] comparaison de tables et mise à jour
    Par borisa dans le forum VBA Access
    Réponses: 4
    Dernier message: 09/02/2006, 10h15
  5. comparaison de tables
    Par amelie15 dans le forum MS SQL Server
    Réponses: 3
    Dernier message: 14/04/2005, 10h37

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo