décembre
2010
Un article de zinzineti
Le choix du type de données constitue le point de départ de l’optimisation d’une base de données.
Le principe est simple : « Chaque octet gaspillé par un mauvais choix de type (ou un surdimensionnement) c’est des ressources (mémoire,disque) gaspillées et par conséquent c’est la performance du SGBD qui est dégradée ! « . Optimiser l’espace de stockage des données par un choix rigoureux des types est un bon début pour optimiser les performances.
Choisir le bon type et le bon size exigent avant tout une bonne connaisance :
¤ de ce qu’on veut stocker
¤ des différents types de données disponibles dans le SGBD
¤ de la capacité de stockage de chaque type
¤ de la précision relative à chaque type
¤ ….
Une petite démo ? allons-y 
Nous allons créer deux tables, une qu’on va appeler T_BigDataTypes possède des colonnes de type BIGINT et NVARCHAR.
L’autre table T_SmallDataTypes possède des colonnes de types INT et VARCHAR
USE [MaBase]
GO
DROP TABLE dbo.T_BigDataTypes
GO
CREATE TABLE dbo.T_BigDataTypes
(
idp BIGINT IDENTITY (1,1) PRIMARY KEY,
valp NVARCHAR(100) NOT NULL
)
GO
INSERT INTO T_BigDataTypes
SELECT REPLICATE('z', 100);
GO 100000
DROP TABLE dbo.T_SmallDataTypes
CREATE TABLE dbo.T_SmallDataTypes
(
idp INT IDENTITY (1,1) PRIMARY KEY,
valp VARCHAR(100) NOT NULL
)
GO
INSERT INTO dbo.T_SmallDataTypes
SELECT REPLICATE('z', 100);
GO 100000
=> Ouverture des compteurs de performances
SET STATISTICS TIME ON
SET STATISTICS IO ON
=> Test 1
SELECT idp, valp FROM T_BigDataTypes
SELECT idp, valp FROM T_SmallDataTypes
Résultat Test1
================
(100000 row(s) affected)
Table ‘T_BigDataTypes‘. Scan count 1, logical reads 2792SQL Server Execution Times:
CPU time = 94 ms, elapsed time = 2201 ms.(100000 row(s) affected)
Table ‘T_SmallDataTypes‘. Scan count 1, logical reads 1457SQL Server Execution Times:
CPU time = 93 ms, elapsed time = 2050 ms.
=> Test 2
SELECT idp FROM T_BigDataTypes WHERE idp > 3210
SELECT idp FROM T_SmallDataTypes WHERE idp > 3210
Résultat Test 2
================
(96790 row(s) affected)
Table ‘T_BigDataTypes‘. Scan count 1, logical reads 2703SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 1373 ms.(96790 row(s) affected)
Table ‘T_SmallDataTypes‘. Scan count 1, logical reads 1411SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 1296 ms.
=> Test 3
SELECT idp FROM T_BigDataTypes WHERE idp < 3210
SELECT idp FROM T_SmallDataTypes WHERE idp < 3210
Résultat Test 3
================
(3209 row(s) affected)
Table ‘T_BigDataTypes‘. Scan count 1, logical reads 93SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 145 ms.(3209 row(s) affected)
Table ‘T_SmallDataTypes‘. Scan count 1, logical reads 50SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 108 ms.
=> Test 4
SELECT idp FROM T_BigDataTypes WHERE idp = 3210
SELECT idp FROM T_SmallDataTypes WHERE idp = 3210
Résultat Test 4
================
(1 row(s) affected)
Table ‘T_BigDataTypes‘. Scan count 0, logical reads 3SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.(1 row(s) affected)
Table ‘T_SmallDataTypes‘. Scan count 0, logical reads 3SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
=> Fermeture des compteurs de performances
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
La règle d’or est donc de minimiser l’occupation des colonnes en choissant les types de données de façon à consommer juste l’espace nécessaire. Il ne faut donc pas tailler large les colonnes. Car ce gaspillage non seulement plombe les performances du serveur de base de données mais perturbe également le traffic réseau de l’entreprise !
— Pourquoi utiliser le type INT alors que la colonne attend des valeurs compris entre 0 et 20 ?!
— Pourquoi utiliser les types NCHAR, NVARCHAR alors qu’il n’y a pas ce besoin de stocker de l’unicode ?!
— Pourquoi utiliser le type DATETIME2 alors que vous avez besoin d’une précision de l’ordre de la minute ?!
voyez le gaspillage ?!
Dans notre démo nous avons vu l’influence de :
¤ INT par rapport à BIGINT
¤ VARCHAR par rapport à NVARCHAR
On peut allonger la liste de cette comparaison, mais le principe reste le même :
la plus petite taille pour la plus grande des valeurs possibles
Pour terminer voici une synthèse des différents types de données dans SQL SERVER 2008 R2 :
• Types de données de valeur élevée : varchar(max), nvarchar(max) et varbinary(max)
• Types de données d’objet volumineux : text, ntext, image, varchar(max), nvarchar(max), varbinary(max) et xml
Les types de données ntext, text et image seront retirés dans une prochaine version de Microsoft SQL Server .
Une requête pour obtenir la taille maximale des types pour chaque colonne des tables d'une base de données
SELECT c.TABLE_CATALOG + '.' + c.TABLE_SCHEMA + '.'+ c.TABLE_NAME + '.' + c.COLUMN_NAME as [Base.Schema.Table.colonne]
,typ.name as [Type]
,CASE typ.max_length
WHEN -1 THEN 2147483648
ELSE typ.max_length
END [Nombre Max Octets]
FROM INFORMATION_SCHEMA.COLUMNS c INNER JOIN INFORMATION_SCHEMA.TABLES t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_CATALOG = t.TABLE_CATALOG
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
INNER JOIN sys.types typ
ON c.DATA_TYPE = typ.name
–WHERE c.TABLE_NAME = 'maTable'
ORDER BY [Base.Schema.Table.colonne]
Synthèse sur les types de données numériques
=================================================================
 |
Synthèse sur les types de données chaînes de caractères
=================================================================
 |
Synthèse sur les types de données date et heure
=================================================================
 |
Synthèse sur les types de données chaînes binaires
=================================================================
 |
Autres types de données
=================================================================
 |
Merci à SQLPro pour m’avoir aider à comprendre la capacité de stockage des types Table/Geometry/Geography/
————————————————————————————————
— Etienne ZINZINDOHOUE
————————————————————————————————