Introduction
Les Sparses Columns sont une nouvelle fonctionnalité introduite par SQL SERVER 2008.
Si on se réfère à la traduction littérale de Sparse Column, on obtiendrait quelque chose comme « Colonne à faible densité ».
Pour être plus précis, les Sparse Columns représentent une solution de stockage efficace (en terme base de données !) des colonnes dites de faible densité (ou faible usage).
Faisons simple : Il s’agit ici d’économiser de l’espace disque en diminuant drastiquement le nombre de pages de données des tables sous SQL SERVER …
Ainsi, on peut dire qu’une colonne peu utilisée, acceptant le NULL, et de type SPARSE va :
Il est vrai qu’aujourd’hui, le support de stockage est loin d’être cher, par rapport à ce que l’on a pu connaître dans le passé, ce qui va donc nous intéressé n’étant finalement pas temps le gain de place, quoique bien réel, mais surtout le gain de performances sur les opérations d’IO, qui représentent le goulet d’étranglement majeur d’une base de données que l’on veut performante.
Mais avant de rentrer dans le vif du sujet, faisons un petit point sur les différentes techniques permettant de gérer les colonnes à faible densité, aujourd’hui :
SQL SERVER 2005 : Entity-Attribute-Value, XML
EAV
Le mode Entity Attribute Value (EAV) est utilisé lorsque le nombre d’attributs (disons colonnes !) qui peuvent être utilisés pour décrire une entité (disons table !) est vaste, mais dans les faits, le nombre réel de données décrivant la ligne restant trés modeste vis à vis des dites colonnes.
Ce constat donne des tables avec beaucoup (trop) de colonnes ayant un taux de remplissage inférieur à 5-10% (et donc 90-95% de valeurs nulles)
Un EAV est une représentation sous forme de paire clé-valeur d’attributs nécessaires.
Les deux EAV les plus utilisés sont :
Le principe est donc de capturer les informations de faible densité, sans se préoccuper de celles non renseignées.
Solution de départ
Nous allons utiliser une table Client, toute simple où nous supposons les données présentes de forte densité (NOM, PRENOM) et où nous allons vouloir stocker des attributs de faible densité (poids, taille, couleur des yeux)
La table est simplissime au possible:
Nous allons maintenant tenter via les fonctionnalités de SQL SERVER 2005 dans un premier temps, puis de SQL SERVER 2008 (et les sparses columns) de rajouter des données de faible densité
EAV via une table et utilisation du PIVOT
Une première solution va consister à produire une table qui va contenir ces informations volatiles.
On peut imaginer quelque chose du genre:
1: CREATE TABLE [dbo].[ ClientAttributeExemple1](
2: [ClientID] [int] NOT NULL,
3: [AttributeID] [int] NOT NULL,
4: [Value] [nvarchar](50) NULL,
5: CONSTRAINT [PK_SparseTable] PRIMARY KEY CLUSTERED
6: (
7: [ClientID] ASC,
8: [AttributeID] ASC
9: )
Il ne reste plus qu’à stocker les informations de faible densité pour chaque client dans cette table.
Trois inconvénients majeurs :
Justement, comment requéter ces informations, pour chaque client ?
Nous allons utiliser le mécanisme de PIVOT mis à notre disposition depuis SQL SERVER 2005
1: Select ClientExemple1.ClientId,
2: ClientExemple1.Nom,
3: ClientExemple1.Prenom,
4: [1001] as 'Taille',
5: [1002] as 'Poids',
6: [1003] as 'Yeux'
7: From ClientAttributeExemple1 Pivot ( Max(Valeur)
8: for AttributeID in ([1001], [1002], [1003])) as PV
9: Inner Join ClientExemple1 on PV.ClientID = ClientExemple1.ClientId
Deux remarques :
Le résultat attendu :
Ce modèle d’EAV est le plus répandu aujourd’hui. Il est rattrapé par le modèle XML, mais reste encore le plus commun et intuitif (SI vous connaissez bien le principe du Pivot 
).
Note: Beaucoup de puristes SQL considèrent ce modèle d’EAV comme inadéquate, car non relationnel.
Pour ma part, c’est le coté « typage de données» qui me gène le plus.
A vous de voir !
EAV via le type XML
Une autre solution consiste à utiliser XML, comme EAV de notre table Client :
Solution robuste et efficace, permettant de ne pas passer par une table auxiliaire, mais une simple colonne de type XML.
Attention deux choses à prendre directement en compte avant de commencer :
Création du Schéma XML.
Nous allons créer un schéma XML qui contiendra l’ensemble des attributs de faible densité.
Pourquoi permettre cette largesse avec XML que nous nous refusons d’utiliser avec nos colonnes ?
Tout simplement car le champ XML ne stocke aucune information sur un élément qui contiendrait une valeur nulle !
Un schéma type pourrait donc être :
1: DECLARE @mySchema xml
2:
3: SET @mySchema =
4: '<xsd:schema xmlns:xsd=http://www.w3.org/2001/XMLSchema
5: xmlns:sqltypes=http://schemas.microsoft.com/sqlserver/2004/sqltypes
6: elementFormDefault="qualified">
7: <xsd:import
8: namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes"
9: schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
10: <xsd:element name="ClientAttributes">
11: <xsd:complexType>
12: <xsd:sequence>
13: <xsd:element name="Poids" minOccurs="0">
14: <xsd:simpleType>
15: <xsd:restriction base="sqltypes:int">
16: <xsd:minInclusive value="0" />
17: </xsd:restriction>
18: </xsd:simpleType>
19: </xsd:element>
20: <xsd:element name="Yeux" minOccurs="0">
21: <xsd:simpleType>
22: <xsd:restriction base="sqltypes:varchar">
23: <xsd:maxLength value="100" />
24: </xsd:restriction>
25: </xsd:simpleType>
26: </xsd:element>
27: <xsd:element name="Taille" minOccurs="0">
28: <xsd:simpleType>
29: <xsd:restriction base="sqltypes:numeric">
30: <xsd:totalDigits value="3" />
31: <xsd:fractionDigits value="2" />
32: <xsd:minExclusive value="0" />
33: </xsd:restriction>
34: </xsd:simpleType>
35: </xsd:element>
36: </xsd:sequence>
37: </xsd:complexType>
38: </xsd:element>
39: </xsd:schema>'
40:
41: CREATE XML SCHEMA COLLECTION ClientAttributesSchema AS @mySchema
Trois informations à noter :
Nous pouvons vérifier que ce schéma existe bien dans SQL SERVER 2005 :
Création de notre colonne de type XML associé à ce schéma :
Nous rajoutons une colonne de type XML, qui doit se conformer à notre schéma :
Au choix, via le designer :
Ou par code SQL :
1: ALTER TABLE dbo.Client ADD
2: XmlAttributes xml(CONTENT dbo.ClientAttributesSchema) NULL
Il ne reste plus qu’à utiliser notre colonne XML, représentant notre EAV, comme vous avez (miantenant) l’habitude de le faire !
Insertion des données
Remarquez sur chacun des cas, les données sont insérées si seulement elles sont connues :
1: INSERT INTO ClientExemple2 (Prenom, Nom, XmlAttributes)
2: SELECT 'Julien', 'Duprat',
3: N'<ClientAttributes>
4: <Poids>70</Poids>
5: <Yeux>Bleu</Yeux>
6: <Taille>1.85</Taille>
7: </ClientAttributes>'
8:
9: INSERT INTO ClientExemple2 (Prenom, Nom, XmlAttributes)
10: SELECT 'Philippe', 'Lonvaud',
11: N'<ClientAttributes>
12: <Yeux>Noir</Yeux>
13: </ClientAttributes>'
Récupération des données
La requête de sélection, simple :
Chacun des champs XML contient uniquement les données de faible densité, nécessaires :
Cas 1 :
1: <ClientAttributes>
2: <Poids>70</Poids>
3: <Yeux>Bleu</Yeux>
4: <Taille>1.85</Taille>
5: </ClientAttributes>
Cas 2 :
1: <ClientAttributes>
2: <Yeux>Noir</Yeux>
3: </ClientAttributes>
Notez bien que dans le cas N°2 les attributs « Poids » et « Taille » sont bien omis.
Si nous voulons récupérer les données sous le même format que précédemment (une ligne, et par colonne la valeur correspondante), il va nous falloir faire une requête du type :
1: Select ClientExemple2.ClientID, ClientExemple2.Nom, ClientExemple2.Prenom,
2: ClientExemple2.XmlAttributes.value('(//ClientAttributes/Poids)[1]', 'int')
3: as Poids,
4: ClientExemple2.XmlAttributes.value('(//ClientAttributes/Yeux)[1]', 'varchar(100)')
5: as Yeux,
6: ClientExemple2.XmlAttributes.value('(//ClientAttributes/Taille)[1]', 'numeric(3,2)')
7: as Taille
8: FROM ClientExemple2
Le résultat donnant :
Vous avez vu ici les deux façons de faire de l’EAV avec SQL SERVER, sans passer par les Sparses Columns.
Aujourd’hui encore, j’utilise et conseille ce type d’EAV via XML, si les Sparses columns ne peuvent pas être utilisées.
SQL SERVER 2008 : Sparse Columns
Présentation
Sparse Column fait parti de SQL SERVER 2008.
Cette fonctionnalité vient combler le manque comblé par les EAV, et s’impose donc comme la solution la plus homogène.
Bien entendu, tout l’acquis des méthodes précédentes a été judicieusement implémenté (tout du moins la partie XML) et pourra donc être aussi utilisé ici.
En résumé, les sparses columns c’est :
Par contre, à noter quelques restrictions :
- Un entier (int) de 4 octets passe donc à 8 octets
- Un Uniqueidentifier passe de 16 octets à 20 octets
- text, ntext, image
- udt
- geometry
- geography
- varbinary(max) Filestream.
- Pas de valeur par défaut, ce qui parait logique.
- La réplication par merge ne fonctionne pas.
- La compression ne fonctionne pas.
- L’accès en écriture et lecture est plus couteux en terme de CPU
Quand utiliser des Sparses Columns ?
Quand la densité de remplissage peut elle justifier l’utilisation des Sparse Column ? Tout dépendra de la taille de la colonne ainsi que sa densité de remplissage.
Tiré de la documentation MSDN, un tableau sur l’utilité des SPARSE COLUMNS, suivant leur type et leur taux de remplissage :
On note bien que les types les plus importants comme bigint, float, money, uniqueidentifier ou les types de longueur non fixes comme numeric(38,s) datetime2(x) ou encore varchar(x) sont les plus concernés.
Création d’une table avec SPARSE COLUMNS
Puisque la limite de colonnes n’est plus un problème, ni la perte de place dans les pages de données, nous allons donc créer une table avec l’ensemble de nos colonnes nécessaires !
La syntaxe reste simple : Ajout de l’attribut SPARSE à une colonne nulle :
Par code :
1: CREATE TABLE dbo.ClientExemple3Sparse
2: (
3: ClientID int NOT NULL IDENTITY (1, 1),
4: Nom varchar(50) NOT NULL,
5: Prenom varchar(50) NOT NULL,
6: Poids int SPARSE NULL,
7: Yeux varchar(50) SPARSE NULL,
8: Taille numeric(3, 2) SPARSE NULL
9: )
Ou Via le designer :
Insertion et récupération des données
A partir de maintenant, tout se passe comme une table classique, que ce soit l’insertion, la mise à jour ou la récupération des données :
Insertion des données :
1: INSERT INTO ClientExemple3Sparse
2: ([Nom], [Prenom], [Yeux], [Taille])
3: VALUES ('PERTUS' ,'Sébastien', 'Marron', 1.90),
4: ('RIELH' ,'Jean Pierre', 'Marron', 1.80),
5: ('DUPRAT' ,'Julien', 'Bleu', 1.84)
6:
7: INSERT INTO ClientExemple3Sparse
8: ([Nom], [Prenom])
9: VALUES ('LONVAUD', 'Philippe'),
10: ('LAMARCHE', 'Patrice')
Récupération des données
1: SELECT [ClientID],[Nom],[Prenom],[Poids],[Yeux],[Taille]
2: FROM ClientExemple3Sparse
Avec un résultat bien entendu correct :
Index filtrés
Là où tout ceci commence à devenir intéressant, c’est lors de l’utilisation d’un grand jeu de données, en association avec un index filtré.
Tout d’abord insérons un petit peu plus de données !
1: Declare @cpt int = 0;
2: while (@cpt < 100000)
3: Begin
4:
5: Declare @Nom varchar(50) = 'Nom_' + CONVERT(Varchar, @cpt);
6: Declare @Prenom varchar(50) = 'Prénom_' + CONVERT(Varchar, @cpt);
7:
8: Declare @randomNumber float = Rand();
9:
10: Declare @Poids int = null;
11: Declare @Yeux varchar(50) = null;
12: Declare @Taille numeric(3,2) = null;
13:
14: if (@randomNumber <= 0.15) Set @Taille = @randomNumber * 10;
15: if (@randomNumber <= 0.10) Set @Yeux = 'Marron';
16: if (@randomNumber <= 0.05) Set @Poids = 30 * (@randomNumber * 100);
17:
18: INSERT INTO [ClientExemple3Sparse] ([Nom] ,[Prenom], [Poids] ,[Yeux] ,[Taille])
19: VALUES (@Nom, @Prenom, @Poids, @Yeux, @Taille);
20:
21: Set @cpt = @cpt + 1;
22:
23: End
Sans rentrer dans les détails, le script insère 100 000 enregistrements et pour chaque Sparse column, applique un taux de remplissage de 15, 10 et 5%
Nous allons requêter pour le moment un jeu d’enregistrements sur un critère défini sur une SPARSE COLUMN, non indexée, puis indexée.
La requête :
1: Select * from ClientExemple3Sparse where Poids = 100
La requête remonte 1600 enregistrements.
Le plan d’exécution est, comme on pouvait s’y attendre, un CLUSTERED INDEX SCAN :
Nous avons une colonne à faible densité, contenant la plupart du temps une valeure NULLE, nous allons donc utilisé un index sur cette colonne, mais celui ci sera filtré !
Nous ajoutons un index filtré sur la colonne Poids, comme suit (pour un article un peu plus détaillé sur les index filtrés, je vous renvoie sur un post que j’ai fais il y a déjà quelques temps )
1: CREATE NONCLUSTERED INDEX [IX_SparsePoids] ON [dbo].[ClientExemple3Sparse]
2: (
3: [Poids] ASC
4: )
5: WHERE Poids is not null
Le plan d’exécution devient :
Analyse du taux de remplissage des pages
Beaucoup de données dans peu de colonnes
Pour voir la différence entre une table avec SPARSE COLUMNS et sans, j’ai créé volontairement la même table que précédemment, sans les SPARSES COLUMNS et je l’ai remplie de la même façon
Nous allons analyser le nombre de pages et le taux moyen de remplissage des pages :
1: SELECT i.name
2: ,s.in_row_data_page_count
3: ,s.in_row_reserved_page_count
4: ,s.in_row_used_page_count
5: ,s.row_count
6: FROM sys.dm_db_partition_stats AS s
7: INNER JOIN sys.indexes AS i ON s.index_id = i.index_id AND s.object_id = i.object_id
8: WHERE s.object_id = OBJECT_ID('ClientExemple3Sparse', 'U');
Et
1: SELECT i.name
2: ,s.in_row_data_page_count
3: ,s.in_row_reserved_page_count
4: ,s.in_row_used_page_count
5: ,s.row_count
6: FROM sys.dm_db_partition_stats AS s
7: INNER JOIN sys.indexes AS i ON s.index_id = i.index_id AND s.object_id = i.object_id
8: WHERE s.object_id = OBJECT_ID(‘ClientExemple3Normal’, 'U');
Différence finalement assez minime, on économise quelques pages de données, dans ce cas là environ 14 – 15%
Beaucoup de colonnes et peu de données
Deuxième test, nous créons deux tables avec, non pas beaucoup de données, mais beaucoup de colonnes (1000). Toujours pareil, une table avec 97 SPARSES COLUMNS et une table, sans.
Je passe rapidement sur les scripts de création, pour la forme voici la création de la table AVEC sparse columns et son remplissage (la version SANS sparse columns diffère de 2 mots : le nom de la table et l’attribut SPARSE en moins) :
1: Declare @strCreate varchar(max);
2:
3: Set @strCreate = '
4: CREATE TABLE [dbo].[ClientExemple4Sparse](
5: [ClientID] [int] IDENTITY(1,1) NOT NULL,
6: [Nom] [varchar](50) NOT NULL,
7: [Prenom] [varchar](50) NOT NULL,';
8:
9: Declare @cpt int = 0;
10: while (@cpt < 1000)
11: Begin
12:
13: Declare @NomColumn varchar(50) = 'Column_' + CONVERT(Varchar, @cpt);
14:
15: Set @strCreate = @strCreate + '[' + @NomColumn + '] int Sparse null, ';
16:
17: Set @cpt = @cpt + 1;
18:
19: End
20:
21:
22: Set @strCreate = @strCreate + ' CONSTRAINT [PK_ClientExemple4Sparse] PRIMARY KEY CLUSTERED
23: (
24: [ClientID] ASC
25: ) ON [PRIMARY]
26: ) ON [PRIMARY]
27: '
28:
29: exec(@strCreate);
Le remplissage :
1: Insert into SparseClientExemple2 (Nom, Prenom, Column_1)
2: Values ('PERTUS', 'Sébastien', 1);
3: Insert into SparseClientExemple2 (Nom, Prenom, Column_1, Column_2)
4: Values ('RIELH', 'Jean pierre', 1, 2);
5: Insert into SparseClientExemple2 (Nom, Prenom, Column_1, Column_2, Column_3)
6: Values ('LONVAUD', 'Philippe', 1, 2, 3);
7: Insert into SparseClientExemple2 (Nom, Prenom, Column_1, Column_2, Column_3,
8: Column_4, Column_5, Column_6)
9: Values ('Duprat', 'Julien', 1, 2, 3, 4, 5, 6);
10: Insert into SparseClientExemple2 (Nom, Prenom, Column_1, Column_2, Column_3,
11: Column_4, Column_5, Column_6, Column_7, Column_8)
12: Values ('Courtois', 'Schtroumpf', 1, 2, 3, 4, 5, 6, 7, 8);
13: Insert into SparseClientExemple2 (Nom, Prenom, Column_1, Column_2, Column_3,
14: Column_4, Column_5, Column_6, Column_7, Column_8, Column_9)
15: Values ('Laut', 'Benoit', 1, 2, 3, 4, 5, 6, 7, 8, 9);
Au niveau de l’analyse on pointe les résultats suivants :
Un rapport de 4156 / 76 soit un gain de 180% sur le nombre d’octets occupés par record !
Le nombre de pages de différence est assez peu élevé vu que nous n’avons inséré que peu d’enregistrements, mais au vu du rapport, on peut imaginer ce que ça donnerait sur plusieurs millions de lignes !
Le rapport sur l’utilisation disque par table, nous conforte dans cette idée, avec un gain de l’ordre de 6 fois supérieur :
Récupération des données, sur un nombre conséquent de colonnes.
Supposons que nous voulions récupérer l’ensemble des données de faible densité (nous avons un vaste choix de colonnes allant de Colum_0 à Column_999 !)
Tentons un simple
1: Select * from dbo.MegaClient2008
Verdict sans appel : 51 secondes sur ma machine !
En effet le client va récupérer l’ensemble des métadatas de chaque colonne pour ensuite l’afficher.
Pour éviter ce problème, nous allons donc utiliser un concept associé aux Sparse Columns : les Columns Sets
Columns Set
Présentation
Une colonne ‘set’ est une représentation sous format XML de l’ensemble des colonnes SPARSE d’une ligne, dont la valeur est non nulle.
Pratique ! Grâce à cette colonne, nous allons pouvoir récupérer l’ensemble des colonnes non nulles de nos lignes, dans un format que nous connaissons bien : XML
Création d’une table avec Column Set
Pour créer une telle colonne, il suffit de créer une colonne de type XML, suivi de l’attribut
XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
Une table pour l’exemple:
1: CREATE TABLE [dbo].[ SparseClientExemple3](
2: [ClientID] [int] IDENTITY(1,1) NOT NULL,
3: [Nom] [varchar](50) NOT NULL,
4: [Prenom] [varchar](50) NOT NULL,
5: [Poids] [int] SPARSE NULL,
6: [Yeux] [varchar](50) SPARSE NULL,
7: [Taille] [numeric](3, 2) SPARSE NULL,
8: [SpecialColumnSet] XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
9: )
L’insertion des données reste très classique :
1: INSERT INTO SparseClientExemple3
2: ([Nom] ,[Prenom] ,[Poids] ,[Yeux] ,[Taille])
3: VALUES ('PERTUS' ,'Sébastien',94 ,'Marron' ,1.90 )
4: INSERT INTO SparseClientExemple3
5: ([Nom] ,[Prenom] ,[Yeux] ,[Taille])
6: VALUES ('RIELH' ,'Jean Pierre','Marron' ,1.80 )
7: INSERT INTO SparseClientExemple3
8: ([Nom] ,[Prenom] ,[Poids] ,[Yeux] )
9: VALUES ('DUPRAT' ,'Julien',94 ,'Bleu' )
10: INSERT INTO SparseClientExemple3
11: ([Nom] ,[Prenom] )
12: VALUES ('LONVAUD' ,'Philippe')
Là où les choses évoluent, c’est lors de la sélection de cette table :
Un Simple Select :
1: Select * from Client2008ColumnSet
Donne un résultat totalement .. inattendu :
Mais… Efficace !!
En effet, lors d’un select * standard, les colonnes de type SPARSE, non nulles, sont agrégées dans cette colonne XML pour donner un résultat que nous savons déjà requêter (cf. début de l’article)
Note : Si notre Select est plus précis, les données sont remontées comme nous nous y attendrions normalement :
Cette fonctionnalité va donc prendre tout son sens dans le cas précédent où notre requête s’effectuait en 51 secondes (et qui plus est, bien peu utilisable) :
Rappelons les faits : Faire un Select * sur une table contenant 1003 colonnes
Encore une fois, mais dans le bon sens, verdict sans appel : Durée de la requête : Avoisinant les 0 sec…
Conclusion
Les Sparses Columns, associées aux Columns Set permettent aujourd’hui de gérer, plus simplement les définitions de tables énormes où la plupart des colonnes sont nulles.
Bref, une petite case à cocher qui peut rapporter beaucoup !
Attention toutefois, les sparses columns sont une trés bonne piste pour économiser de la place et gagner en performances mais n’oubliez pas de bien vérifier que les contre-indications (Filestream, replication) ne sont pas utilisées sur votre base de données…
Bon Sparses Columns !