Vue partitionnée

imageDepuis SQL Server 2005, il est possible de partitionner une table. Cela permet de réduire les scans ou de les répartir sur différents disques.

Cette fonctionnalité n’est disponible que dans la version Enterprise ; comment peut-on faire pour en bénéficier en version Standard ?

Je renvoie à la question suivante : comment faisait-on avant 2005 ?

Sous SQL Server 2000 (et précédents), on utilisait le concept de vue partitionnée. Et on l’utilise toujours pour partitionner des données en édition Standard Clignement d'œil.

Le principe est simple :

  • on crée plusieurs tables pour représenter nos partitions
  • on crée une vue qui sélectionne toutes les tables par le biais d’un UNION ALL

CREATE VIEW dbo.Data    
AS
   SELECT id, label, PartitionKey
   FROM dbo.Data0

   UNION ALL

   SELECT id, label, PartitionKey
   FROM dbo.Data1

   UNION ALL

   SELECT id, label, PartitionKey
   FROM dbo.Data2
GO    

En faisant un SELECT sur la vue, on tape dans toutes tables sans connaitre les tables sous-jacentes. Sauf qu’on ne bénéficie en rien des avantages du partitionnement (réduction des lectures ou parallélisation).

image

Evidemment, il y a un moyen d’en profiter tout de même.

Lorsque vous avez créé votre partitionnement (ie. vos différentes tables),  vous connaissiez ou avez défini un critère de partitionnement (autrement appelé clé de partitionnement). C’est souvent une date, une année, un identifiant d’agence, etc.

Il suffit donc d’indiquer la clé de partitionnement en statique à la création de la vue et le parseur SQL saura déterminer sur quel(s) table(s) se trouvent les données.

CREATE VIEW dbo.Data    
AS
   SELECT id, label, 1 as PartitionKey
   FROM dbo.Data0

   UNION ALL

   SELECT id, label, 2 as PartitionKey
   FROM dbo.Data1

   UNION ALL

   SELECT id, label, 3 as PartitionKey
   FROM dbo.Data2
GO    

On peut voir ce comportement dans le plan d’exécution

image

 

Sans ce critère de partitionnement dans la vue, la notion de partition est totalement inutile et les performances en seront même dégradées (car SQL doit lire toutes les partitions).

Donc attention aux conseils rapides trouvés sur internet. Mal utilisés, il ne servent à rien.

Une réflexion sur “Vue partitionnée

  1. Les vues partitionnées ont bien été introduites avec SQL 7, en lecture seulement. A partir de SQL 2000, elles ont supporté les Update/Insert/Delete.
    Attention cependant au nombre de tables. Les cas où j’ai mis en pratique les vue partitionnées (voire fédérées) ont montré leurs limites au delà de 10 tables dans la vue. Chaque jour une nouvelle table était crée. 10 000 0000 d’insert et 40 000 000 000 d’upate sur la table au bout de 24h. Les perf s’écroulaient passé 8 ou 10 tables dans la vues (les reads remontaient en flêche pour les inserts).

    Ca doit te rappeller bien des choses cette BD …🙂

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s