DotMim

Je viens de faire un petit test sur les colonnes XML

On se doute tous que l’ajout d’index sur une colonne XML peut améliorer les performances, pour peu que bien sur vous ayez besoin de requêter la colonne XML elle même que ce soit via un PATH ou une VALUE (Xquery power)

Si ce n’est pas le cas, un bon vieux VarChar(max) est bien plus performant. N’oubliez pas qu’une colonne xml est en fait une bonne vielle table stockée dans une table système de votre base

Deuxième chose, la performance d’une colonne XML soumise à un schéma de validation XSD.
ON sait qu’un schéma va permettre de valider les données contenu dans notre champ XML. Déjà rien que ça justifie pleinement le fait de mettre une colonne XML Validée par un schéma XSD

Pour notre test, j’ai créé un schéma XSD, qui me permettra de valider ma colonne XML :

-- Création du schéma

CREATE XML SCHEMA COLLECTION AdditionalInfos AS '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">

  <xs:simpleType name="addressType">

    <xs:restriction base="xs:string">

      <xs:enumeration value="Home" />

      <xs:enumeration value="Office" />

      <xs:enumeration value="Travel" />

      <xs:enumeration value="Undefined" />

    </xs:restriction>

  </xs:simpleType>



  <xs:element name="additionalOrder">

    <xs:complexType>

      <xs:sequence>

        <xs:element name="orderDate" type="xs:dateTime"/>


        <xs:element name="additionalAddress"  maxOccurs="unbounded">

          <xs:complexType>

            <xs:sequence>

              <xs:element name="name" type="xs:string"/>

              <xs:element name="address" type="xs:string"/>

              <xs:element name="city" type="xs:string"/>

              <xs:element name="country" type="xs:string"/>

            </xs:sequence>

            <xs:attribute name="name" type="xs:string" use="required"/>

            <xs:attribute name="type" type="addressType" use="required"/>

          </xs:complexType>

        </xs:element>


        <xs:element name="complementOrder" maxOccurs="unbounded">

          <xs:complexType>

            <xs:sequence>

              <xs:element name="name" type="xs:string" />

              <xs:element name="quantity" type="xs:positiveInteger" />

              <xs:element name="price" type="xs:decimal"/>

            </xs:sequence>

            <xs:attribute name="orderId" type="xs:positiveInteger" use="required"/>

          </xs:complexType>

        </xs:element>

      </xs:sequence>

      <xs:attribute name="additionalOrderId" type="xs:positiveInteger" use="required"/>

    </xs:complexType>

  </xs:element>

</xs:schema>

'

Puis j’ai créé mes colonnes XML

1. Première colonne : Colonne XML validée par mon schéma avec un index dessus et 2 index supplémentaires (VALUE et PATH)
2. Deuxième colonne : Colonne XML sans schéma avec un index dessus et 2 index supplémentaires (VALUE et PATH)
3. Troisième colonne : Colonne XML sans schéma sans index

Voilà la définition de ces colonnes:

-- Modification de la table

ALTER TABLE Sales.Customer ADD

	AdditionnalInformations xml(DOCUMENT dbo.AdditionalInfos) NULL

GO

-- Création de l'index primaire XML

CREATE PRIMARY XML INDEX PXML_AdditionnalInformations on Sales.Customer (AdditionnalInformations)

-- Création d'un index secondaire de type Path

CREATE XML INDEX PXML_AdditionnalInformations_Path ON Sales.Customer (AdditionnalInformations)

USING XML INDEX PXML_AdditionnalInformations

FOR PATH

-- Création d'un index secondaire de type Value

CREATE XML INDEX PXML_AdditionnalInformations_Value ON Sales.Customer (AdditionnalInformations)

USING XML INDEX PXML_AdditionnalInformations

FOR VALUE


-- Modification de la table

ALTER TABLE Sales.Customer ADD	AdditionnalInformationsWithoutSchema xml NULL

GO

-- Création de l'index primaire XML

CREATE PRIMARY XML INDEX PXML_AdditionnalInformations_WithoutSchema on Sales.Customer (AdditionnalInformationsWithoutSchema)

-- Création d'un index secondaire de type Path

CREATE XML INDEX PXML_AdditionnalInformations_WithoutSchema_Path ON Sales.Customer (AdditionnalInformationsWithoutSchema)

USING XML INDEX PXML_AdditionnalInformations_WithoutSchema

FOR PATH

-- Création d'un index secondaire de type Value

CREATE XML INDEX PXML_AdditionnalInformations_WithoutSchema_Value ON Sales.Customer (AdditionnalInformationsWithoutSchema)

USING XML INDEX PXML_AdditionnalInformations_WithoutSchema

FOR VALUE


ALTER TABLE Sales.Customer ADD

	AdditionnalInformationsWithoutSchemaWithoutIndexes xml NULL

Y’a plus qu’à remplir les données, via un bon vieil INSERT ou UPDATE

Vous trouverez le script en PJ pour vous faire une idée

Voici les résultats de l’insertion

Alors oui, on est d’accord, l’insertion est bien plus performante sur une colonne XML sans index, un Blob quoi.

On Imagine bien que le parsing de chaque valeur XML et la création d’une table système avec une insertion à la volée peut être consommateur de ressources !

Passons aux requêtes de Sélection sur une requête Xquery PATH

-- Using PATH index

select Top 10  C.CustomerID, C.AdditionnalInformations.query('/additionalOrder/additionalAddress[@type = "Home"]')

from Sales.Customer C

select Top 10  C.CustomerID, C.AdditionnalInformationsWithoutSchema.query('/additionalOrder/additionalAddress[@type = "Home"]')

from Sales.Customer C

select Top 10  C.CustomerID, C.AdditionnalInformationsWithoutSchemaWithoutIndexes.query('/additionalOrder/additionalAddress[@type = "Home"]')

from Sales.Customer C

Je ne mesure pas le temps ici, il faudrait avoir un champ XML totalement énorme pour avoir une différence notable. J’ai donc noté le % d’exécution donné par le plan d’exécution

Bon Ok, la requête sur le BLOB sans index écroule tout. Je refais le test uniquement sur les colonnes indexés :

Bon le gain sur une requête PATH n’est pas notable qu’on soit ou non validé par un schéma

Passons aux requêtes de Sélection sur une requête XQuery VALUE

-- Using VALUE index

Select C.CustomerID, C.AdditionnalInformations

from Sales.Customer C

where C.AdditionnalInformations.exist('/additionalOrder/@additionalOrderId[.=100]') = 1

Select C.CustomerID, C.AdditionnalInformations

from Sales.Customer C

where C.AdditionnalInformationsWithoutSchema.exist('/additionalOrder/@additionalOrderId[.=100]') = 1

Select C.CustomerID, C.AdditionnalInformations

from Sales.Customer C

where C.AdditionnalInformationsWithoutSchemaWithoutIndexes.exist('/additionalOrder/@additionalOrderId[.=100]') = 1

Les premiers résultats en comparant avec une colonne non indexée sont identiques à l’exemple précédent. Je passe donc directement à la comparaison des deux colonnes indexées :

Wow … là oui y’a différence !

Examinons le plan d’éxécution :

Que se passe t’il : dans le premier cas, on a un schéma, donc une table fortement typée, où SQL SERVER sait que quelque soit la ligne, les données (enfin le type) sera identique :
Il peut donc faire une requête directement sur l’index

Sur la deuxième requête, la colonne est “permissive”. On peut stocker n’importe quel champ xml.
Pour pouvoir récupérer toutes les valeurs ‘@type’ il est donc obligé de faire un bon vieux gros SCAN de l’index XML PRIMARY

D’où l’intérêt du schéma !

Conclusion :

Les schémas c’est bien, une colonne XML indexé avec un schéma c’est performant et secure.

Par contre, vous perdez en Insertion. Attention à l’intéret d’une colonne XML si vous ne requêtez jamais l’arbre XML !

Quelques liens utiles :

• http://msdn.microsoft.com/en-us/library/ms345121(SQL.90).aspx
• http://msdn.microsoft.com/en-us/library/ms184277.aspx
• http://technet.microsoft.com/fr-fr/library/ms345118(SQL.90).aspx

Voilà voilà, bon indexation, bon XML tout ça tout ça !