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

 

  1. #1
    B&B
    B&B est déconnecté
    Membre à l'essai
    Profil pro
    Inscrit en
    juin 2004
    Messages
    20
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : juin 2004
    Messages : 20
    Points : 14
    Points
    14

    Par défaut Optimiser une requete xpath

    Bonjour,

    Je cherche des pistes pour optimiser l’exécution d'une requete qui extrait des nœuds xml à l'aide de la fonction xpath

    Ma table frm est composé de :
    - id, type bigint ( primary key )
    - numero, type varchar(12)
    - xml, type xml
    - dt_creation, type timestamp

    Le numero est composé de lettre et de chiffre
    Le fichier xml stocké pèse entre 2 et 3ko.
    La date de creation est la date d'enregistrement de la ligne en base. ( de 2014 à aujourd'hui )

    La table contient 11.5 Millions de lignes.

    Le but est de récupérer les valeurs d'une trentaine de noeuds xml, pour toutes les lignes, et de les stocker dans une nouvelle table, avec une colonne par noeud extrait.

    Je travaille sur une copie d'une base de production, je peux donc utiliser n'importe quelle instruction et modifier le fichier postgresql.conf si nécessaire.
    Ma seule contrainte est le temps de la requête, j'ai environ 10h.

    J'ai commencé en tout innocence avec ca :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
     
    DROP TABLE IF EXISTS matable;
    CREATE TABLE matable AS (
    	SELECT 
    array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Plafond/P10/text()',xml),'') as P10,
    array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Plafond/P15/text()',xml),'') as P15,
    array_to_string(xpath('/MY-XML/Branche/Dossier/Meuble/Tirroir/T1/text()',xml),'') as T1,
    array_to_string(xpath('/MY-XML/Branche/Dossier/Meuble/Tirroir/T2/text()',xml),'') as T2,
    array_to_string(xpath('/MY-XML/Branche/Dossier/Meuble/Poignee/PO1/PO1.1/text()',xml),'') as PO11,
    array_to_string(xpath('/MY-XML/Branche/Dossier/Meuble/Poigne/PO1/PO1.2/text()',xml),'') as  PO12,
    array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Plafond/P2/text()',xml),'') as P2,
    array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Etage/E1/E1.3/text()',xml),'') as E133,
    array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Etage/E1/E1.4/text()',xml),'') as E134,
    array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Etage/E1/E1.5/text()',xml),'') as E135,
    array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Etage/E1/E1.6/text()',xml),'') as E136,
    array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Etage/E1/E1.7/text()',xml),'') as E137,
    array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Etage/E1/E1.8/text()',xml),'') as E138,
    array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Etage/E1/E1.9/text()',xml),'') as E139,
    array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Etage/E1/E1.10/text()',xml),'') as E110,
    array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Etage/E1/E1.11/text()',xml),'') as E111,
    array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Etage/E1/E1.12/text()',xml),'') as E112,
    array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Etage/E1/E1.13/text()',xml),'') as E113,
    array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Etage/E1/E1.14/text()',xml),'') as E114,
    array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Piece/Salle/S3/S31/text()',xml),'') || array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Piece/Salle/S3/S32/text()',xml),'') as  S3
    array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Piece/Salle/S2/text()',xml),'') as S2,
    array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Piece/Salle/S2/S2.3/text()',xml),'') as S23,
    array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Piece/Salle/S2/S2.4/text()',xml),'') as S24,
    array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Piece/Salle/S2/S2.5/text()',xml),'') as S25,
    array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Piece/Salle/S2/S2.6/text()',xml),'') as S26,
    array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Piece/Salle/S2/S2.7/text()',xml),'') as S27,
    array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Piece/Salle/S2/S2.8/text()',xml),'') as S28,
    array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Piece/Salle/S2/S2.9/text()',xml),'') as S29,
    array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Piece/Salle/S2/S2.10/text()',xml),'') as S210,
    array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Piece/Salle/S2/S2.11/text()',xml),'') as S211,
    array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Piece/Salle/S2/S2.12/text()',xml),'') as S212,
    array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Piece/Salle/S2/S2.13/text()',xml),'') as S213,
    array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Piece/Salle/S2/S2.14/text()',xml),'') as S214	
    FROM frm;
     
    );
    J'ai coupé la requete au bout de 15h, elle n'était pas terminée. J'ai filtré sur la date, après l'avoir indexée, pour avoir un quart de la volumétrie attendue : 5h pour 3.2 Millions de liasses.

    Je seche un peu sur la manière de l'optimiser.
    - Les fonctions array_to_string > xpath sont elles les plus pertinentes pour lire une valeur ?
    - Le découpage de la requete d'alimentation en plusieurs requetes serait elle plus efficace ?
    --- Plusieurs requetes avec moins de noeud à lire par requete ?
    --- Plusieurs requetes avec le meme nombre de noeud mais un filtre sur une colonne ?
    - ??

    Merci pour votre aide

  2. #2
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    18 077
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 18 077
    Points : 42 285
    Points
    42 285

    Par défaut

    Malheureusement PostgreSQL ne propose pas de méthode d'indexation des chemins des données XML, contrairement à d'autres SGBDR comme SQL Server qui permet d'indexer par VALUE, PATH ou PROPERTY l'ensemble des éléments des documents XML contenus dans une colonne de la table de type XML.
    À lire :https://wiki.postgresql.org/wiki/XML_Indexing
    La seule chose à faire est de vous débarrasser de ce XML qui n'aurait jamais dû rentrer dans votre base de données, puis que apparemment c'est de la données structurée...

    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...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

Discussions similaires

  1. [XPath] Probleme dans une requete XPath
    Par kalmless21 dans le forum XSL/XSLT/XPATH
    Réponses: 6
    Dernier message: 03/07/2006, 22h22
  2. [9i][Optimisation] Optimiser une requete
    Par nuke_y dans le forum Oracle
    Réponses: 53
    Dernier message: 27/02/2006, 11h34
  3. Experts Mysql : Optimiser une requete sur codes postaux
    Par El Riiico dans le forum Requêtes
    Réponses: 6
    Dernier message: 20/01/2006, 18h00
  4. Aide pour optimiser une requete
    Par Akanath dans le forum MS SQL-Server
    Réponses: 1
    Dernier message: 15/09/2005, 11h05
  5. Optimiser une Requetes SQL sous ASP
    Par NeHuS dans le forum ASP
    Réponses: 8
    Dernier message: 18/04/2005, 16h26

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