none
SqlServer optimisation update sur Tables avec gros volumes RRS feed

  • Question

  • Bonjour,
    je travaille sur 3 tables dont voici les définitions :
    A_Ref(Ref, Id)
    A_Rq(IdRq, Ref1, Ref2, Ref3, Ref4, Ref5)
    A_Tx (IdTx, NbRef)

    je précise que A_Ref.Id=A_Tx.IdTx
    je doit compter le nombre de fois qu'une Référence Apparait dans la table A_Rq, et mettre cette valeur dans le champs A_Tx.NbRef pour la Référence correspondant à a_Tx.IdTx.
    je Récapitule, Pour chaque A_Tx.IdTx, je cherche la Référence correspondante dans la table A_Ref,
    et je compte le nombre de fois que cette référence apparait dans les champs Ref1, Ref2, Ref3, Ref4, Ref5. ce nombre est donc la valeur à attribuer à A_Tx.NbRef.

    voici ce que j'ai fait, mais ca prend beaucoup de temps et de ressources: (je rappelle que la table A_Rq contient environs 300 000 lignes et A_Ref environs 5 000 lignes)
    UPDATE AptTx 
    SET A_Tx.NbRef =  (SELECT     COUNT(DISTINCT IdRq) AS Expr1  FROM A_Rq
                                  WHERE  (Ref1 = A_Ref.Ref) OR
                                  (Ref2 = A_Ref.Ref) OR (Ref3 = A_Ref.Ref) OR 
                                  (Ref4 = A_Ref.Ref) OR (Ref5 = A_Ref.Ref))
    FROM        A_Tx INNER JOIN A_Ref ON A_Tx.IdTx = A_Ref.Id

    Merci de m'aider à optimiser cette requete et à réduire le temps de traitement de cette mise à jour.
    mardi 9 février 2010 17:14

Réponses

  • Bonsoir,

    Votre requête n'est pas performante à cause de votre prédicat dans la sous requête destinée à compter votre nombre d'occurences de références .. Le fait d'implémenter des OR supprime tout emploi d'un quelconque index sur les colonnes de références. Ceci étant dit et si vous avez implémenté des index sur ces colonnes il faut également se poser la question concernant leur sélectivité ...

    Personnellement je remplaçerais votre logique en OR par des UNION ALL et implémenterait les index adéquates :

    -- Logique OR
    SELECT     COUNT(DISTINCT IdRq) AS Expr1  FROM A_Rq
                                  WHERE  (Ref1 = A_Ref.Ref) OR 
                                  (Ref2 = A_Ref.Ref) OR (Ref3 = A_Ref.Ref) OR 
                                  (Ref4 = A_Ref.Ref) OR (Ref5 = A_Ref.Ref)) 
    
    -- Remplacement par UNION ALL 
    SELECT COUNT(*)
    FROM 
    (         
     SELECT IdRq
     FROM A_Rq
     WHERE Ref1 = A_Ref.Ref
     UNION ALL
     SELECT IdRq
     FROM A_Rq
     WHERE Ref2 = A_Ref.Ref
     SELECT IdRq
     FROM A_Rq
     WHERE Ref3 = A_Ref.Ref
     SELECT IdRq
     FROM A_Rq
     WHERE Ref4 = A_Ref.Ref
     SELECT IdRq
     FROM A_Rq
     WHERE Ref5 = A_Ref.Ref
    ) AS T


    ++

    MCDBA | MCITP SQL Server 2005 | MCTS SQL Server 2008 | LPI Linux 1
    • Marqué comme réponse Alex Petrescu lundi 15 février 2010 14:19
    mercredi 10 février 2010 21:26
    Modérateur
  • Bonjour,

    Pour rebondir sur la réponse de Mike.
    Dans le SELECT avec les unions, ne pas oublier de continuer à garder le DISTINCT(IdRq) dans le COUNT.

    Sinon, oui, si on peut avoir des index très sélectifs (NbRef faible par référence), le UNION ALL est une excellente option.
    Si le NbRef par contre est élevé, les index n'auront pas d'intêret.
    Avec la courbe de répartition des résultats, on saura si cette solution peut être mise en place.

    Cordialement,

    Thomas
    Thomas Aimonetti - C# - Sharplog Engineering - http://www.sharplog.fr
    • Marqué comme réponse Alex Petrescu lundi 15 février 2010 14:19
    jeudi 11 février 2010 11:24
  • Merci a vous tous pour vos réponses.

    Pour répondre aux questions de "Thomas Aimonetti" :
    je travaille sur sql Server 2005, et mes tables sont indexées.
    Par contre, pour ce qui est des calculs des statistiques sur les tables, rien n'est encore pensé.
    Mes tables ont un taux d'évolution d'environs 2 pour 1000.
    Quand je dis beaucoup de temps, c'est peut-etre environs 1 à 2 minutes, mais pendant ce laps de temps, je bloque tous les autres utilisateurs qui n'ont plus accès aux ressources.

    je vous remercie tous pour vos réponses, mais j'ai essayé de trouver une tout autre solution.
    Plutot que de lancer tous les matins ma grosse update qui bloque tous les autres,
    je l'ai lancé une seule fois, et à chaque fois que des valeurs sont insérées dans la table A_Rq, j'incrémente le NbRef pour la Ref concernée.
    Ainsi, je fais plusieurs updates, mais seulement sur les éléments qui m'interessent et au moment où elles surviennent.

    Merci les gars...

    Cordialement,
    Chantal
    • Marqué comme réponse Alex Petrescu lundi 15 février 2010 14:19
    jeudi 11 février 2010 16:26

Toutes les réponses

  • Bonjour,

    J'ai un lot de questions préliminaires avant de pouvoir te répondre qui permettront peut-être d'y voir plus clair.

    Pour commencer, tu as quelle version de SQL Server ?
    Ensuite, outre la requête, qu'as tu fait d'autre pour optimiser ton traitement ? Par exemple, tes tables sont-elles indexées, le calcul des statistiques sur tes tables est-il souvent lancé ? Est-ce que les données de tes tables A_Ref et surtout A_Rq évoluent souvent, et si oui, dans quelles mesures ? Peux-tu créer la courbe de répartition de tes résultats et nous la donner (ou au moins le NbRef max, le NBRef min, et le NBRef moyen) ? 
    Enfin, quand tu dis beaucoup de temps et de ressources, tu peux nous donner un ordre d'idée ? Il nous faudrait ça pour le 1er passage dit "à froid" et aussi pour un 2ème passage successif dit "à chaud" de la même requête.

    Cordialement,

    Thomas
    Thomas Aimonetti - C# - Sharplog Engineering - http://www.sharplog.fr
    mercredi 10 février 2010 10:27
  • Bonsoir,

    Votre requête n'est pas performante à cause de votre prédicat dans la sous requête destinée à compter votre nombre d'occurences de références .. Le fait d'implémenter des OR supprime tout emploi d'un quelconque index sur les colonnes de références. Ceci étant dit et si vous avez implémenté des index sur ces colonnes il faut également se poser la question concernant leur sélectivité ...

    Personnellement je remplaçerais votre logique en OR par des UNION ALL et implémenterait les index adéquates :

    -- Logique OR
    SELECT     COUNT(DISTINCT IdRq) AS Expr1  FROM A_Rq
                                  WHERE  (Ref1 = A_Ref.Ref) OR 
                                  (Ref2 = A_Ref.Ref) OR (Ref3 = A_Ref.Ref) OR 
                                  (Ref4 = A_Ref.Ref) OR (Ref5 = A_Ref.Ref)) 
    
    -- Remplacement par UNION ALL 
    SELECT COUNT(*)
    FROM 
    (         
     SELECT IdRq
     FROM A_Rq
     WHERE Ref1 = A_Ref.Ref
     UNION ALL
     SELECT IdRq
     FROM A_Rq
     WHERE Ref2 = A_Ref.Ref
     SELECT IdRq
     FROM A_Rq
     WHERE Ref3 = A_Ref.Ref
     SELECT IdRq
     FROM A_Rq
     WHERE Ref4 = A_Ref.Ref
     SELECT IdRq
     FROM A_Rq
     WHERE Ref5 = A_Ref.Ref
    ) AS T


    ++

    MCDBA | MCITP SQL Server 2005 | MCTS SQL Server 2008 | LPI Linux 1
    • Marqué comme réponse Alex Petrescu lundi 15 février 2010 14:19
    mercredi 10 février 2010 21:26
    Modérateur
  • Bonjour,

    Pour rebondir sur la réponse de Mike.
    Dans le SELECT avec les unions, ne pas oublier de continuer à garder le DISTINCT(IdRq) dans le COUNT.

    Sinon, oui, si on peut avoir des index très sélectifs (NbRef faible par référence), le UNION ALL est une excellente option.
    Si le NbRef par contre est élevé, les index n'auront pas d'intêret.
    Avec la courbe de répartition des résultats, on saura si cette solution peut être mise en place.

    Cordialement,

    Thomas
    Thomas Aimonetti - C# - Sharplog Engineering - http://www.sharplog.fr
    • Marqué comme réponse Alex Petrescu lundi 15 février 2010 14:19
    jeudi 11 février 2010 11:24
  • Merci a vous tous pour vos réponses.

    Pour répondre aux questions de "Thomas Aimonetti" :
    je travaille sur sql Server 2005, et mes tables sont indexées.
    Par contre, pour ce qui est des calculs des statistiques sur les tables, rien n'est encore pensé.
    Mes tables ont un taux d'évolution d'environs 2 pour 1000.
    Quand je dis beaucoup de temps, c'est peut-etre environs 1 à 2 minutes, mais pendant ce laps de temps, je bloque tous les autres utilisateurs qui n'ont plus accès aux ressources.

    je vous remercie tous pour vos réponses, mais j'ai essayé de trouver une tout autre solution.
    Plutot que de lancer tous les matins ma grosse update qui bloque tous les autres,
    je l'ai lancé une seule fois, et à chaque fois que des valeurs sont insérées dans la table A_Rq, j'incrémente le NbRef pour la Ref concernée.
    Ainsi, je fais plusieurs updates, mais seulement sur les éléments qui m'interessent et au moment où elles surviennent.

    Merci les gars...

    Cordialement,
    Chantal
    • Marqué comme réponse Alex Petrescu lundi 15 février 2010 14:19
    jeudi 11 février 2010 16:26
  • Bonjour Chantal,

    En effet, le calcul de NbRef peut être lancé par trigger on insert ou on update.
    Si les tables ne sont pas beaucoup modifiées, c'est une solution tout à fait viable.
    En général, on se méfie de ce genre de pratique lorsque la table évolue beaucoup, car afin de calculer un export à un moment donné, on grève les performances de l'application tout le temps.

    Bonne continuation !

    Cordialement,

    Thomas
    Thomas Aimonetti - C# - Sharplog Engineering - http://www.sharplog.fr
    jeudi 11 février 2010 17:00
  • Bonjour Thomas,

    Dans le SELECT avec les unions, ne pas oublier de continuer à garder le DISTINCT(IdRq) dans le COUNT => dans ce cas-là ne vaut-il mieux pas enlever le ALL des UNION ?

    @++ ;)
    vendredi 12 février 2010 06:42
  • Bonjour,

     

    Merci Thomas et Mike pour votre contribution.

     

    Cordialement,

    Alex


    Alex Petrescu - MSFT
    lundi 15 février 2010 14:20
  • Bonjour,

    Concernant la question de Nicolas ...
    Je n'avais même pas fait gaffe à la syntaxe UNION ALL de Mike, je ne l'ai jamais utilisée jusqu'ici, j'ai toujours fait des UNION entre chaque requête.
    J'ignore si cela change quelque chose donc je me renseigne, et je repasse plus tard.
    Dans le cas ou c'est strictement équivalent à mettre des UNION partout, je ne vois pas trop Nicolas où tu veux en venir, mais dans tous les cas je suis intéressé pour que tu développes ce point.

    Cordialement,

    Thomas
    Thomas Aimonetti - C# - Sharplog Engineering - http://www.sharplog.fr
    vendredi 19 février 2010 09:14
  • UNION ALL permet de récupérer l'ensemble des données avec doublon alors que UNION les élimine.
    Nicolas a fait une remarque très pertinente.

    Si je prends votre exemple :

    SELECT COUNT(DISTINCT IdRq)

    FROM

    (

     SELECT IdRq

     FROM A_Rq

     WHERE Ref1 = A_Ref.Ref

     UNION ALL

     SELECT IdRq

     FROM A_Rq

     WHERE Ref2 = A_Ref.Ref
     UNION ALL

     SELECT IdRq

     FROM A_Rq

     WHERE Ref3 = A_Ref.Ref

     UNION ALL
     
    SELECT IdRq

     FROM A_Rq

     WHERE Ref4 = A_Ref.Ref

     UNION ALL 
     
    SELECT IdRq

     FROM A_Rq

     WHERE Ref5 = A_Ref.Ref

    ) AS T

    Cette requête récupère l'ensemble des IdRq de votre table A_Rq en fonction du prédicat défini. Avec UNION ALL les doublons ne seront pas éliminés. C'est pour cela que le DISTINCT est mis en place.

    Remplacer les UNION ALL par UNION permet d'éliminer le DISTINCT puisque cette instruction élimine déjà les doublons. Ceci revient donc à écrire la requête suivante qui est équivalente en terme de résultat :

    SELECT COUNT(IdRq)

    FROM

    (

     SELECT IdRq

     FROM A_Rq

     WHERE Ref1 = A_Ref.Ref

     UNION

     SELECT IdRq

     FROM A_Rq

     WHERE Ref2 = A_Ref.Ref

     UNION

     SELECT IdRq

     FROM A_Rq

     WHERE Ref3 = A_Ref.Ref

     UNION

     SELECT IdRq

     FROM A_Rq

     WHERE Ref4 = A_Ref.Ref

     UNION

     SELECT IdRq

     FROM A_Rq

     WHERE Ref5 = A_Ref.Ref

    ) AS T


    ++


    MCDBA | MCITP SQL Server 2005 | MCTS SQL Server 2008 | LPI Linux 1
    • Proposé comme réponse SQLpro mercredi 10 mars 2010 17:00
    vendredi 19 février 2010 09:32
    Modérateur
  • Plutôt que de faire un UPDATE ne serait-il pas nettement plus judicieux de ne pas redonder vos données calculées en faisant par exemple une vue indexée ?

    A +

    Expert SQL et SGBDR
    mercredi 10 mars 2010 17:01