Pourquoi indexer les types de données XML ?

Ces données contiennent des éléments et des attributs (voir article XML dans SQLServer2005) et on pourra chercher à optimiser les recherches parmi ces attributs ou éléments.

A défaut d'index, SQLServer ne pourra que faire des scans de la table, ce qui générera beaucoup d'entrées/sorties sur le disque.

Comme d'habitude, en terme de performances, on cherchera à diminuer ces entrés/sorties, sans que cela se fasse au détriment du temps de réponse global (Compromis CPU / IO)

Principes d'indexation

Dans l'idée cela ressemble aux index full text (voir l'article consacré à ce sujet) car
SQLServer cherche à constituer des couples (valeur cherchée, clé primaire) afin d'éviter d'aller dans la structure XML chercher les données nécessaires.

Avant toute chose, le moteur a besoin d'un index dit PRIMARY sur la colonne XML.

Cet index contient des couples (primary key de la table, identifiant de nœud XML).

La syntaxe de la création de cet index est :

    create [ primary ] XML INDEX NomIndex

    ON Nomtable ( NomColonneXml )

Exemple d'utilisation de colonne de type XML

    use AdventureWorks

    go

    -- Partons de la table CV de AdventureWorks

    select Resume from HumanResources.JobCandidate

    go

La base de données AdventureWorks est fournie dans les exemples de SQLServer2005 et contient différentes données de démonstration des fonctionnalités, en particulier des colonnes de type XML dans la table HumanResources.JobCandidate qui contient les CV des candidats au format XML.





Il nous faut du volume pour faire des tests, nous allons donc créer une table de test et la remplir par un produit cartésien, ce qui est une astuce classique pour obtenir du volume lors des tests de performances.

    create table HumanResources.Resume(

    EmployeeID int IDENTITY(1,1) NOT null,

    Title nvarchar(50) collate SQL_Latin1_General_CP1_CI_AS NOT null,

    Resume xml(CONTENT HumanResources.HRResumeSchemaCollection) null,

    BirthDate datetime NOT null,

    CONSTRAint PK_Resume_EmployeeID primary key clustered

    (

    EmployeeID ASC

    )WITH (PAD_INDEX = OFF, IGNORE_DUP_key = OFF)

    )

    go

    -- Un petit produit cartésien fera l'affaire

    insert into [HumanResources].[Resume]

    (Title,Resume,Birthdate)

    select 'M.',Resume,BirthDate

    from HumanResources.JobCandidate ,

    HumanResources.Employee

Et maintenant des requêtes sur les CV :

    select Resume.query('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";

    /ns:Resume/ns:Name')

    from [HumanResources].[Resume]

qui nous donne :

…. Et autres personnes….

Mesurons les entrées/sorties sur certaines requêtes avec XML :

    set statistics io on

    go

    select Resume.value('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";

    /ns:Resume[1]/ns:Employment[1]/ns:Emp.JobTitle[1]','varchar(50)')

    from [HumanResources].[Resume]

qui nous donne un scan de la table, et donc, sans surprise, la totalité des pages lues :

Créons maintenant le premier index sur la colonne XML :

    create primary XML INDEX PResume

    ON HumanResources.Resume (Resume)

et mesurons l'effet obtenu :

    select Resume.value('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";

    /ns:Resume[1]/ns:Employment[1]/ns:Emp.JobTitle[1]','varchar(50)')

    from [HumanResources].[Resume]

Gloups !!

Le nombre d'IO a considérablement augmenté, montrant la taille de l'index (nœud XML, clé primaire)


Créons maintenant un index XML pour les chemins PATH
Cet index favorisera les requêtes sur des éléments dont on donne le chemin.

    create XML INDEX PResumePath ON HumanResources.Resume (Resume)

    USING XML INDEX PResume

    FOR PATH

    go

De même, créons un index sur les valeurs, qui favorisera les requêtes sur les recherches de valeurs d'attributs dans la structure XML :

    create XML INDEX PResumeValue ON HumanResources.Resume (Resume)

    USING XML INDEX PResume

    FOR VALUE

    go

Et enfin, créons un index sur les propriétés qui favorisera les recherches sur les couples chemin + valeur :

    create XML INDEX PResumeProperty ON HumanResources.Resume (Resume)

    USING XML INDEX PResume

    FOR PROPERTY

    go

Voyons cela avec une requête simple :

    select Resume.value('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";

    /ns:Resume[1]/ns:Employment[1]/ns:Emp.JobTitle[1]','varchar(50)')

    from [HumanResources].[Resume]

Toujours beaucoup d'ES, l'index est plus coûteux que le scan complet de la table en termes d'IO disque.

Voyons maintenant une requête conditionnelle :


    select * from HumanResources.Resume

    where Resume.exist('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume";

    /ns:Resume/ns:Name/ns:Name.First [.="Shai"]') = 1

.

Cette fois-ci, le gain est important (plus de 3 fois) ce qui permet de valider que SQLServer se sert bien de l'un des index

Conclusion :

Il en est des index XML comme des index classiques : il y a un compromis à trouver pour les choisir.

Mais, d'une manière générale, je ne pense pas qu'il soit très opportun de stocker des données XML volumineuses dans une base relationnelle, mais ceci est un autre débat ....