IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Développement SQL Server Discussion :

Clause Where sur champs XML, passer de la méthode value à la méthode exist [2016]


Sujet :

Développement SQL Server

  1. #1
    Membre à l'essai
    Inscrit en
    juillet 2009
    Messages
    39
    Détails du profil
    Informations personnelles :
    Âge : 43

    Informations forums :
    Inscription : juillet 2009
    Messages : 39
    Points : 19
    Points
    19
    Par défaut Clause Where sur champs XML, passer de la méthode value à la méthode exist
    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.

  2. #2
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    mai 2002
    Messages
    20 779
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 20 779
    Points : 49 221
    Points
    49 221
    Billets dans le blog
    1
    Par défaut
    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  3. #3
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    mai 2002
    Messages
    20 779
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 20 779
    Points : 49 221
    Points
    49 221
    Billets dans le blog
    1
    Par défaut
    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  4. #4
    Membre à l'essai
    Inscrit en
    juillet 2009
    Messages
    39
    Détails du profil
    Informations personnelles :
    Âge : 43

    Informations forums :
    Inscription : juillet 2009
    Messages : 39
    Points : 19
    Points
    19
    Par défaut
    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).

  5. #5
    Membre à l'essai
    Inscrit en
    juillet 2009
    Messages
    39
    Détails du profil
    Informations personnelles :
    Âge : 43

    Informations forums :
    Inscription : juillet 2009
    Messages : 39
    Points : 19
    Points
    19
    Par défaut
    Citation Envoyé par SQLpro Voir le message
    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.

  6. #6
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    mai 2002
    Messages
    20 779
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 20 779
    Points : 49 221
    Points
    49 221
    Billets dans le blog
    1
    Par défaut
    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  7. #7
    Membre à l'essai
    Inscrit en
    juillet 2009
    Messages
    39
    Détails du profil
    Informations personnelles :
    Âge : 43

    Informations forums :
    Inscription : juillet 2009
    Messages : 39
    Points : 19
    Points
    19
    Par défaut
    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.

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Clause Where sur un champ "calculé"
    Par papouuu dans le forum MS SQL Server
    Réponses: 3
    Dernier message: 09/03/2011, 17h12
  2. Clause where sur liste de champs
    Par SelectEtoile dans le forum Développement
    Réponses: 3
    Dernier message: 04/02/2010, 22h41
  3. Clause WHERE sur tous les champs du dataAdapter.XxxCommand
    Par Graffito dans le forum Accès aux données
    Réponses: 1
    Dernier message: 26/06/2008, 17h31
  4. Clause WHERE sur deux champs
    Par mouchkar dans le forum Langage SQL
    Réponses: 3
    Dernier message: 09/01/2008, 16h03
  5. Clause Where sur une Date
    Par Zebulonn dans le forum Installation
    Réponses: 31
    Dernier message: 20/10/2005, 11h56

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo