Discussion :

[nivsql]

Bonjour,

Dans une application pour laquelle j'essaye de proposer des amélioration de performances j'ai trouver la requête suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT COUNT(i.DevisID) 
FROM Devis as i  
WHERE  (  i.Redacteur=176 OR ( UserAttr.value('(/root/ListeUserAttr/UserAttr[@Name="userCodeUtilisateurSignataire"]/part0)[1]', 'nvarchar(100)')='' 
AND  UserAttr.value('(/root/ListeUserAttr/UserAttr[@Name="userCodeUtilisateurSignataire"]/part1)[1]', 'nvarchar(100)')='HCARIOU' 
AND  UserAttr.value('(/root/ListeUserAttr/UserAttr[@Name="userCodeUtilisateurSignataire"]/part2)[1]', 'nvarchar(100)')='') )  
AND ( (i.Etat='0') OR  (i.Etat='2' AND i.Etat2='0'))

Je sais que la méthode value dans la clause where n'est pas performante et qu'il faut privilegier la methode exist, j'ai donc essayer de modifier les 3 prédicats avec mes maigres connaissances. Pour l'un d'eux ca fonctionne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

AND  UserAttr.exist('/root/ListeUserAttr/UserAttr[@Name="userCodeUtilisateurSignataire"]/part1[(text()[1]) = "HCARIOU"]') = 1

mais pour les 2 autres je n'arrive pas a trouver la syntaxe appropriée

Voici la structure tu champs XML tel que stocké en base (pour la partie qui nous intéresse pour cette requête) :

Code XML :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
<root>
	<ListeUserAttr>
		<UserAttr Name="userCodeUtilisateurSignataire" Type="100">
			<part0 />
			<part1>HCARIOU</part1>
			<part2 />
		</UserAttr>
	</ListeUserAttr>
</root>

Est ce que vous pourriez m'aider a trouver les bons prédicats avec la méthode exist pour les valeurs vide de part0 et part2 ?

Merci pour votre aide.

[SQLpro]

pour part0 et part2 il faut que vous testiez que la balise existe, mais que la valeur n'existe pas.

Queque chose comme :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT 1 
WHERE  @XML.exist('/root/ListeUserAttr/UserAttr[@Name="userCodeUtilisateurSignataire"]/part1[(text()[1]) = "HCARIOU"]') = 1
 
AND    @XML.exist('/root/ListeUserAttr/UserAttr[@Name="userCodeUtilisateurSignataire"]//.[local-name()="part0"]') = 1
AND    @XML.exist('/root/ListeUserAttr/UserAttr[@Name="userCodeUtilisateurSignataire"]/part0[(text()[1]) = ""]') = 0
 
...

A +

[SQLpro]

Mais pour aller plus vite, avez vous pensé à indexer votre colonne XML ?

Moi je ferais d'abord un index XML primaire puis teste si un index sur path ou value ou property, voire les 3 sont intéressant

A +

[nivsql]

L'indexation de ce champs XML est problématique et contre performante. Sa structure peut varier d'une ligne a l'autre car ce sont les utilisateurs qui déterminent librement les "champs" qui vont y etre stockés (c'est un héritage d'une ancienne version du logiciel qui ne tournait pas sur SQL Server mais sur un moteur propriétaire non SQL et non ensembliste).

[nivsql]

pour part0 et part2 il faut que vous testiez que la balise existe, mais que la valeur n'existe pas.

Queque chose comme :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT 1 
WHERE  @XML.exist('/root/ListeUserAttr/UserAttr[@Name="userCodeUtilisateurSignataire"]/part1[(text()[1]) = "HCARIOU"]') = 1
 
AND    @XML.exist('/root/ListeUserAttr/UserAttr[@Name="userCodeUtilisateurSignataire"]//.[local-name()="part0"]') = 1
AND    @XML.exist('/root/ListeUserAttr/UserAttr[@Name="userCodeUtilisateurSignataire"]/part0[(text()[1]) = ""]') = 0
 
...

A +

Merci beaucoup pour votre aide ! cela semble parfaitement fonctionner, je vais soumettre cette amélioration aux développeurs afin qu'ils l'intègrent au plus vite, la requête passe de 13 secondes a moins d'une seconde.

[SQLpro]

Quand tu dis contre performant, asu tu essayé toutes les combinaisons ?

Est-ce contre performant au niveau de l'INSERT ?

s tu mis tes LOBs dans une stockage à part avec le directive TEXTIMAGE_ON ???

A +

[nivsql]

C'est une base hérité, tout est dans primary, en vrac, j'essaye beaucoup de chose en collaboration avec les devs mais peu de chose fonctionne. Cette application vous ferrait avoir une jaunisse tant elle n'est pas avec une logique adaptée à SQL (requete et logique non ensembliste, spam de requete unitaire sur liste d'ID, méconnaissance du SQL de la part des devs originaux). Et malheureusement l'entreprise ne veux pas investir beaucoup dessus car elle la considère en fin de vie (ce qui veut dire que je vais me la trainer encore 5 à 10 ans mais sans budget), je ne peux donc gratter que quelques quickwin par-ci par-la.