SqlServer optimisation update sur Tables avec gros volumes

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.

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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 ?

@++ ;)

Answer 7

Bonjour,

 

Merci Thomas et Mike pour votre contribution.

 

Cordialement,

Alex

---

Alex Petrescu - MSFT

Answer 8

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

Answer 9

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

Answer 10

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