Meilleur auteur de réponses
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.
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
-
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
-
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
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 -
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
-
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
-
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
-
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 -
-
-
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 -
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 ALLSELECT IdRq
FROM A_Rq
WHERE Ref3 = A_Ref.Ref
UNION ALL
SELECT IdRqFROM A_Rq
WHERE Ref4 = A_Ref.Ref
UNION ALL
SELECT IdRqFROM 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
-