Performance des triggers ensemblistes

Version imprimable

13/12/2011, 19h08
vinse51

Performance des triggers ensemblistes
Bonjour,

Je suis en train de mettre en place des triggers destinés à maintenir des compteurs, par exemple un trigger qui va incrémenter un champ [nb_messages] d'une table [forum_sujet] lorsqu'un nouveau message est inséré dans une table [forum_message].

Par exemple j'ai un trigger de la sorte sur la table [forum_message] :
Code:

1 2 UPDATE forum_sujet SET nb_messages = nb_messages + 1 FROM inserted i WHERE id = i.sujet_id
Jusque là tout va bien mais je viens de me rendre compte grâce au plan d'exécution que peu importe si l'enregistrement existe ou pas dans la table [forum_sujet], l'instruction "Clustered Index Update" sur cette table est quand même exécutée (avec un "actual number of rows" = 0) et prend 40% du coût de la requête !

J'ai bien pensé utiliser une instruction IF EXISTS mais ce n'est pas possible car non ensembliste.

Y-a-t-il un moyen de ne pas exécuter l'update si la ligne n'existe pas, mais d'une manière ensembliste ?

Merci d'avance pour vos réponses.

Code:

1
2
3
4
5
6
7
UPDATE F
SET F.nb_messages = F.nb_messages + I.nbr
FROM forum_sujet F 
   INNER JOIN (SELECT sujet_id,COUNT(*)  as nbr
                    FROM inserted 
                    GROUP BY sujet_id) I
   ON I.sujet_id=F.ID

14/12/2011, 10h24
vinse51

Bonjour et merci pour cette réponse.

En fait ce code optimise effectivement ma requête dans le sens où s'il y a plusieurs messages du même sujet inséré en même temps le compteur ne sera mis à jour qu'une seule fois. C'est donc une optimisation en effet indispensable.

Par contre cela ne résout pas mon problème initial, à savoir que peu importe le fait qu'il y ait ou pas une correspondance de ligne dans la jointure, il y a toujours un "Clustered Index Update" qui s'exécute et c'est ça que je ne comprend pas.

Pourquoi le moteur effectue cette exécution alors qu'il n'y a pas de ligne à mettre à jour et pour quelle raison cela a un coût ?
Y-a-t-il un verrou de posé sur la table même si le nombre de ligne à mettre à jour est égal à 0 ?
14/12/2011, 10h24
SQLpro

Quelle que soit la manière d'aborder la chose il faut d'abord lire les données, même si c'est pour ne rien mettre à jour ! C'est pourquoi votre UPDATE coute de la lecture même si aucune ligne n'est mise à jour.

La seule chose qui pourra améliorer l'ensemble est de voir s'il y a un index adéquat... La solution d'berseek, n'améliorera pas les choses... Elle risque même d'être plus couteuse...

A +
14/12/2011, 10h35
vinse51

Je suis d'accord le moteur doit impérativement lire les données en premier pour effectuer une éventuelle mise à jour.

Mais ce n'est pas cette lecture qui me pose problème mais plutôt l'instruction update qui intervient après la lecture, une fois que le moteur sait qu'il n'y a pas de ligne à mettre à jour.

Je ne pense pas que cette instruction ne fasse d'entrées/sorties par contre elle a un coût processeur non négligeable et je ne comprends pas pourquoi le moteur n'arrête pas l'instruction lorsqu'il sait qu'il n'y a pas de ligne à mettre à jour.
14/12/2011, 13h03
SQLpro

Je pense que vous vous méprenez sur la compréhension de ce qu'est un plan de requête. C'est une estimation... pas la vérité. La vérité sera l'exécution...

A +
14/12/2011, 13h12
vinse51

Hum effectivement cela pourrait être l'explication.

Mais pourtant j'ai bien inclus le "plan d’exécution réel".
Est-ce que même le plan réel ne représente pas forcément la réalité de l'exécution ?
14/12/2011, 16h21
SQLpro

Un plan représente ce que le SGBDR va faire, mais pas ce qu'il exécute réellement... La différence est subtile...
Le plan "avant" est tiré du cache celui après est un plan recalculé, même s'il en existe un en cache, au cas ou certains paramètres ont évolué (volumétrie, changement de distribution statistique, résolution de nom...).
Mais le plan ne détaille pas ce qui est réellement exécuté.

Si vous voulez savoir plus finement ce qui est exécuté, vous pouvez dans SSMS activer la directive SET STATISTICS PROFILE ON; dans laquelle les seules information de la réalité sont Rows et Executes.
EN définitive, vous ne pouvez pas savoir quelle est la consommation réelle des ressources de chaque partie du plan, sauf pour le nombre de ligne et le nombre d'exécution, mais pas pour la durée ou l'usage de la ram, ce serait beaucoup trop fin et pèserait lourdement sur les exécutions....

Il est dommage que vous ne soyez pas venu aux journées SQL Server chez MS organisé par Guss (lundi et mardi dernier - gratuit), car Frédéric Pichaut le responsable de la hot line SQL nous a fait une belle démonstration sur ce sujet !!!

A +
14/12/2011, 17h09
vinse51

Merci pour ces explications c'est très intéressant !

J'ai testé avec PROFILE et j'obtiens exactement la même chose que le plan, à savoir Rows = 0 et Executes = 1 sur l'instruction "Clustered Index Update".

Je pense que du moment que la requête commence par UPDATE le moteur exécute obligatoirement cette instruction.

Par contre cela reste un mystère s'il utilise réellement du processeur, des entrées/sorties et s'il place un verrou sur la table.
14/12/2011, 18h22
SQLpro

Citation:

Envoyé par vinse51

Par contre cela reste un mystère s'il utilise réellement du processeur, des entrées/sorties et s'il place un verrou sur la table.

Oui, Oui et Oui...

N'oubliez pas qu'une lecture pose un verrou (partagé) dans la cas de l'update c'est un verrou d'update....

Le seul cas ou ni IO ni verrou est posé est le cas ou sémantiquement il sait que la requête ne peut en aucun cas produire un effet.
C'est le but des contraintes !

Par exemple augmenter le prix d'un produit dont le prix est < 0 s'il existe une contrainte indiquant qu'un prix est toujours >= 0 conduira à un plan simplissime (constant scan....).
Il est dommage de ne pas avoir profité des 2 journées de formation gratuites que consistait les journées SQL Server de lundi/mardi ou j'ai mis en évidence cette chose là.

A +
15/12/2011, 08h12
iberserk

Bonjour Frédéric,

Il n'y avait malheureusement pas de la place pour tout le monde :?

Pour la requête que j'ai proposé, c'est surtout une correction de la sienne...
16/12/2011, 17h03
vinse51

Oui c'est dommage en effet mais Paris ça fait un peu loin...

Sinon je comprends bien que le moteur doit poser des verrous pour les lectures et j'avais déjà remarqué qu'il était capable de détecter au niveau sémantique quand il pouvait ne pas éxecuter du tout la requête en la remplaçant par un constant scan.

Je reste tout de même étonné qu'il ne soit pas capable de faire de même lorsqu'on lui demande de faire un UPDATE sans aucune ligne à mettre à jour...
16/12/2011, 17h41
SQLpro

Mais dans votre cas pour savoir qu'il n'y a pas de ligne à mettre à jour comment fait-il. Il sort sa boule de cristal ? Il lit dans le marc de café ??
Sérieusement j'ai l'impression que vous êtes un peu bouché !
Un UPDATE c'est :
1) une lecture positionnelle
2) une écriture si la ligne est trouvé.
LES 1 et 2 sont UN SEUL ORDRE UPDATE et commence par un verrou d'UPDATE.
Car imaginez la situation si trouvant la ligne par un verrou de lecture et devant ensuite mettre à jour il ne le pouvait pas car quelqu'un d'autre est en train de lire.
Donc pour toute mise à jour, même si 0 lignes il y a un verrou exclusif.
Et le temps de l'UPDATE est donc celui global de la mise à jour elle même et de la manipulation des verrous. il y a donc bien du travail de fait !!!

A +