|
Le modèle d’une base de données est important car il définit grandement les performances de cette dernière. Mais, malgré tout cela ne suffit pas car nos requêtes ont leurs propres besoins qui nécessitent toute notre attention.
Voici une table toute simple :
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'Test') AND type in (N'U'))
DROP TABLE Test
CREATE TABLE Test
(
iIdentifier INTEGER IDENTITY(1,1) NOT NULL,
sValue NVARCHAR (64) NOT NULL
)
|
On va la remplir comme avec ce script :
|
DECLARE
@L_iIdentifier INTEGER
SET
@L_iIdentifier = 0
TRUNCATE TABLE Test
WHILE
@L_iIdentifier < 10000
BEGIN
SET @L_iIdentifier =
@L_iIdentifier + 1
INSERT INTO Test (sValue) VALUES (N'VALEUR N°' + CAST (@L_iIdentifier AS NVARCHAR (8)))
END
INSERT INTO Test (sValue) SELECT sValue FROM
Test
INSERT INTO Test (sValue) SELECT sValue FROM
Test
INSERT INTO Test (sValue) SELECT sValue FROM
Test
INSERT INTO Test (sValue) SELECT sValue FROM
Test
INSERT INTO Test (sValue) SELECT sValue FROM
Test
INSERT INTO Test (sValue) SELECT sValue FROM
Test
|
Sur mon serveur cette opération prend deux minutes et quatre secondes. Tentons une simple requête sur cette table :
|
SELECT * FROM Test WHERE sValue = N'VALEUR N°7998'
|
Voyons ce que fait SQL Server en regardant son plan d’exécution :
Globalement, il passe son temps à faire un « Table Scan ». Qu’est-ce ? Il s’agit juste de la dénomination de SQL Server pour indiquer qu’il lit chaque enregistrement pour vérifier la clause du WHERE. Dans ce cas, on peut facilement déduire que le temps d’exécution sera proportionnel au nombre d’enregistrements.
Pour pallier à cette faiblesse structurelle, il existe une parade : INDEX. SQL Server utilise ces derniers comme ceux des livres afin de trouver plus vite les enregistrements. Créons donc un simple index sur la colonne « sValue » :
|
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'Test') AND name = N'IX_sValue')
DROP INDEX
IX_sValue ON Test WITH
( ONLINE
= OFF )
CREATE INDEX IX_sValue ON
Test
(
sValue
)
|
Voyons ce que fait SQL Server en regardant son plan d’exécution avec cet index:
On observe un plan d’exécution complètement différent puisqu’interviennent « Nested Loops », « Index Seek » et « RID Lookup ». L’apparition du « RID Lookup » n’est pas bon signe, s’il apparaît c’est que le schéma de la base ne respecte pas quelques règles :
- Chaque table doit avoir un index « CLUSTERED », sauf quelques rares exceptions.
- Un index « NON-CLUSTERED » a été créé avec un tri qui a un sens pour son créateur.
- La requête utilise au moins une colonne qui n’est pas référencée dans un index « NON-CLUSTERED »
Alors, commençons par créer notre index « CLUSTERED » sur la colonne « iIdentifier »:
|
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'Test') AND name = N'IX_iIdentifier')
DROP INDEX
IX_iIdentifier ON Test WITH ( ONLINE = OFF )
CREATE UNIQUE CLUSTERED INDEX IX_iIdentifier ON
Test
(
iIdentifier
)
|
Voyons ce que fait SQL Server en regardant son plan d’exécution avec cet index:
Nous y voilà, notre requête utilise exclusivement notre index.
Cependant, il faut garder à l’esprit que tout a un prix, et si notre index nous assure un haut niveau de performance pour les requêtes, on doit bien payer son actualisation quelque part. En ce qui nous concerne, cela se produit pendant les INSERT, UPDATE et SELECT.
Recréons notre table avec une clé primaire (et son index CLUSTERED) et notre index sur « sValue » :
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'Test') AND type in (N'U'))
DROP TABLE Test
CREATE TABLE Test
(
iIdentifier INTEGER IDENTITY(1,1) NOT NULL,
sValue NVARCHAR(64) NOT
NULL,
CONSTRAINT PK_Test PRIMARY
KEY CLUSTERED
(
iIdentifier
ASC
)
)
CREATE INDEX IX_sValue ON
Test
(
sValue ASC
)
|
Le remplissage de la base prend maintenant deux minutes et trente-quatre secondes ce qui représente trente secondes de plus. Comme vous le voyez, tout a un coût et comme toujours notre plus-value consiste à calculer le ratio bénéfice/coût.
|