octobre
2010
Un article de zinzineti
On ne le dira jamais assez : une mauvaise écriture de requête SQL et surtout la méconnaissance du fonctionnement des SGBD peuvent conduire à une catastrophe ! tenez, une requête du genre SELECT * FROM T_TEST WHERE id LIKE @id peut mettre à genou votre serveur. Voici une petite démo. Cette démo est inspirée d’un cas concret de requête lancée par une application commerciale sur un serveur SQL de production.
–> Création de la table
SET NOCOUNT ON
IF OBJECT_ID('dbo.T_TEST') IS NOT NULL
DROP TABLE dbo.T_TEST
CREATE TABLE dbo.T_TEST
(
id int identity(1,1),
val varchar(10),
creation_date datetime
)
--> Chargement d'1 million de lignes dans la table
DECLARE @counter int;
SET @counter = 1;
WHILE @counter <= 1000000
BEGIN
INSERT INTO T_TEST(val,creation_date) VALUES(convert(varchar(10),(LEFT(convert(bigint,RAND()*10000000),6))),getdate());
SET @counter = @counter + 1
END;
–> Etape 0 : Activons les compteurs de performances IO
SET STATISTICS IO ON
–> Etape 1 : Requête sans index sur la table
DECLARE @id int
SET @id = 101
SELECT * FROM dbo.T_TEST WHERE id LIKE @id
Résultat
——–
– Table SCAN
–Table ‘T_TEST’. Nombre d’analyses 1, lectures logiques 3827
–> Etape 2 : Créons un index sur la colonne id puis exécutons la requête
CREATE INDEX NC_IX_id ON T_TESTBIS(id);
DECLARE @id int
SET @id = 101
SELECT *
FROM dbo.T_TEST
WHERE id LIKE @id
Résultat
——–
– Table SCAN : L’index n’est pas utilisé
— Table ‘T_TEST’. Nombre d’analyses 1, lectures logiques 3827
–> Etape 3 : Remplaçons « * » par la colonne id
DECLARE @id int
SET @id = 101
SELECT id
FROM dbo.T_TEST
WHERE id LIKE @id
Résultat
——–
– Index Scan : L’index est mal utilisé
— Table ‘T_TEST’. Nombre d’analyses 1, lectures logiques 1863
–> Etape 4 : Gardons « * » et remplaçons « LIKE » par « = »
DECLARE @id int
SET @id = 101
SELECT *
FROM Optimisation.dbo.T_TEST
WHERE id = @id
Résultat
——–
–Index SEEK , l’index est bien utilisé mais avec la présence des opérateurs suivants : Compute scalar, RID Lookup(Heap), Nested Loops (inner join),Compute
–Table ‘T_TEST’. Nombre d’analyses 1, lectures logiques 4
–> Etape 5 : Remplaçons « * » par la colonne id et « LIKE » par l’opérateur « = »
DECLARE @id int
SET @id = 101
SELECT id
FROM dbo.T_TEST
WHERE id = @id
Résultat
——–
– INDEX SEEK : l’index est bien utilisé. Et n’y QUE INDEX SEEK pas d’autre opérateur !
— Table ‘T_TEST’. Nombre d’analyses 1, lectures logiques 3
Conclusion
———————
Lorqu’une requête du genre SELECT * FROM T_TEST WHERE id LIKE @id est exécutée avec une fréquence élevée sur un serveur SQL, elle va :
– Stresser à fond les disques durs
– Pousser à bout l’utilisation du CPU
Pour enfin tuer les performances en mettant à genou votre serveur. à bon entendeur, salut.
Et je me demande :
Pour une application commerciale, faut-il toujours laisser l’écriture de requêtes SQL à un développeur qui a une connaissance superficielle du fonctionnement du moteur de base de données ? Quel est votre avis ?
—————————————————————-
Auteur : Etienne ZINZINDOHOUE
—————————————————————-
> Il ne faut pas se leurrer, on ne peut pas demander à tout les développeurs d’avoir de solides connaissances en SQL.
Euh…
Ne pas faire un select * where id like, tu appelles ca de solides connaissances en SQL ?
Si on part par la, faire un trigger, ajouter des indexs, tuner une fonction, tout ca, un développeur ne doit pas savoir faire ?
> Pour une application commerciale, faut-il toujours laisser l’écriture de requêtes SQL à un développeur qui a une connaissance superficielle du fonctionnement du moteur de base de données
Pour moi oui. Il est normal qu’un développeur est des connaissances limités en SQL, ce n’est pas son métier premier. Il ne faut pas se leurrer, on ne peut pas demander à tout les développeurs d’avoir de solides connaissances en SQL. Par contre il est impératif de demander à ses développeurs de faire un code qui tienne la route, et si ils mettent à genoux le serveur de BD, ce n’est plus le cas. Si leurs requêtes ne sont pas optmisés mais que les performances sont acceptables, c’est bon, c’est tout ce qu’on veut.
Pour moi l’erreur du développeur dans ce cas, n’est pas de ne pas connaitre le SQL (ici c’est clair que c’est le cas) mais de ne pas avoir détecter les performances atroces de ses requêtes.
Salut,
sympa comme article, j’en connais plus d’un qui feraient mieux de le lire avec attention
> « Pour une application commerciale, faut-il toujours laisser l’écriture de requêtes SQL à un développeur qui a une connaissance superficielle du fonctionnement du moteur de base de données »
mon avis personnel, c’est qu’un développeur ne devrait pas avoir une connaissance *superficielle* du fonctionnement du moteur de base de données…
Que ce soit au niveau du code, de l’accès à la BDD ou autre, ce qu’on demande à un développeur, c’est d’avoir suffisamment de connaissance en terme de performances pour ne pas faire ce genre d’horreurs, et de suffisamment maitriser ses outils pour détecter les erreurs d’appréciation avant la mise en prod.