Il n’y pas longtemps sur le forum msdn, un internaute rencontrait un problème de performance concernant une vue partitionnée qu’il avait mis en place. Lorsqu’il interrogeait sa vue selon un critère bien précis, l’ensemble des tables de la vue étaient concernées au lieu d’une seule comme il le prévoyait. Nous allons voir pourquoi.

Pour bien comprendre le problème de l’internaute, commençons par créer nos tables et y insérer un jeu de données

CREATE TABLE test_date_2007
(
id INT NOT NULL IDENTITY(1,1),
evenement VARCHAR(50) NOT NULL ,
date DATETIME NOT NULL
);
GO

CREATE TABLE test_date_2008
(
id INT NOT NULL IDENTITY(1,1),
evenement VARCHAR(50) NOT NULL,
date DATETIME NOT NULL
);
GO

CREATE TABLE test_date_2009
(
id INT NOT NULL IDENTITY(1,1),
evenement VARCHAR(50) NOT NULL ,
date DATETIME NOT NULL
);
GO

ALTER TABLE test_date_2007
ADD CONSTRAINT PK_test_date_2007 PRIMARY KEY (id);
GO

ALTER TABLE test_date_2008
ADD CONSTRAINT PK_test_date_2008 PRIMARY KEY (id);
GO

ALTER TABLE test_date_2009
ADD CONSTRAINT PK_test_date_2009 PRIMARY KEY (id);
GO

DECLARE @i INT;
DECLARE @j INT
SET @i = 0;
SET @j = 1;

WHILE @i < 120000
BEGIN
    IF @j = 363 SET @j = 1;

    INSERT INTO test_date_2007 (evenement, date) VALUES (‘EVENT’ + CAST(@i AS VARCHAR(6)),DATEADD(dd,@j,’20070101′));

    SET @i = @i + 1;
    SET @j = @j + 1;
END;

SET @i = 0;
SET @j = 1;

WHILE @i < 120000
BEGIN
    IF @j = 363 SET @j = 1;

    INSERT INTO test_date_2008 (evenement, date) VALUES (‘EVENT’ + CAST(@i AS VARCHAR(6)),DATEADD(dd,@j,’20080101′));

    SET @i = @i + 1;
    SET @j = @j + 1;
END;

SET @i = 0;
SET @j = 1;

WHILE @i < 120000
BEGIN
    IF @j = 363 SET @j = 1;

    INSERT INTO test_date_2009 (evenement, date) VALUES (‘EVENT’ + CAST(@i AS VARCHAR(6)),DATEADD(dd,@j,’20090101′));

    SET @i = @i + 1;
    SET @j = @j + 1;
END;

 

Créons ensuite notre vue :

CREATE VIEW test_date_view
AS

SELECT id, evenement, date
FROM test_date_2007
UNION ALL
SELECT id, evenement, date
FROM test_date_2008
UNION ALL
SELECT id, evenement, date
FROM test_date_2009

A ce stade précis, nous en sommes au même point que l’internaute. Notez au passag l’utilistion de la clause UNION ALL et non UNION. Comme nous avons à faire à des tables partitionnées il n’existe en principe aucun doublon. Nous évitons ainsi de demander à SQL Server de faire cette vérification. Ceci constitue la 1ère phase d’optimisation de notre vue. Interrogeons la maintenant avec un critère date, par exemple entre le 1er janvier 2008 et le 1er mars 2008 soit la requête suivante :

SELECT * FROM test_date_view
WHERE date BETWEEN ‘20080101’ AND ‘20080301’

 

Et voici le plan de d’exécution de la requête correspondante :

 

On pourrait s’attendre ici à n’avoir qu’une seule table : test_table_2008 car c’est la seule à contenir précisément les données demandées par notre requête. Pourquoi dans ce cas interroger toutes les tables ? La raison est simple : SQL Server ne sait pas où trouver les enregistrements qui concernent notre requête pour le moment. En d’autres termes rien ne lui permet de savoir que dans la table test_date_2008 on ne retrouve que les enregistrements de l’année 2008. Il faut donc faire connaître au moteur SQL notre schéma de répartition des données de nos tables. Il suffit pour cela de poser sur chaque table de notre vue sur la colonne de réparation des données (la colonne date dans notre cas) une contrainte de vérification (contrainte CHECK).

ALTER TABLE test_date_2007
ADD CONSTRAINT CHK_YEAR_2007 CHECK (date < ‘20080101’);
GO

ALTER TABLE test_date_2008
ADD CONSTRAINT CHK_YEAR_2008 CHECK (date < ‘20090101’);
GO

ALTER TABLE test_date_2009
ADD CONSTRAINT CHK_YEAR_2009 CHECK (date >= ‘20090101’);

Le plan d’exécution pour la même requête devient :

On voit maintenant qu’une seule table est concernée . Nous sommes donc arrivé au résultat voulu. Nous pouvons encore optimiser notre requête en demandant au moteur SQL d’utiliser la recherche dans un index plus qu’un scan d’index. Pour cela il faut inclure dans la clé primaire de chaque table notre colonne de partition. Comme les recherches se feront le plus souvent par tranche de date et qu’un index est vectorisé, il faut placer la colonne date en 1er :

ALTER TABLE test_date_2007
DROP CONSTRAINT PK_test_date_2007;
GO

ALTER TABLE test_date_2007
ADD CONSTRAINT PK_test_date_2007 PRIMARY KEY (date,id);
GO

ALTER TABLE test_date_2008
DROP CONSTRAINT PK_test_date_2008;
GO

ALTER TABLE test_date_2008
ADD CONSTRAINT PK_test_date_2008 PRIMARY KEY (date,id);
GO

ALTER TABLE test_date_2009
DROP CONSTRAINT PK_test_date_2009;
GO

ALTER TABLE test_date_2009
ADD CONSTRAINT PK_test_date_2009 PRIMARY KEY (date,id);
GO

 

Le plan d’exécution pour la même requête devient :

Nous avons maintenant un parcours d’index. Le tour est joué !! Attention cependant pour un intervalle de date plus conséquent il se peut que vous vous retrouviez de nouveau avec un scan d’index. Ce comportement est normal car l’optimisateur de SQL Server est basé sur le coût. Celui estimera qu’il est plus performant de procéder à un scan de la table (donc l’index cluster dans notre cas) plutôt que de parcourir l’index.

Il existe bien entendu d’autres axes d’améliorations tel que permettre les mises à jour au travers de la vue. Dans ce cas précis, il faudrait créer un trigger INSTEAD OF pour réaliser cette opération. Je conclurais par le fait que les vues partitionnées restent parfois difficile à implémenter. Heureusement à partir de la version 2005 de SQL Server il existe le partitionnement des données. Que les choses sont bien faites quelques fois

Bon partitionnement !!

David BARBARIN (Mikedavem)
Elève ingénieur CNAM Lyon