Mauvaises pratiques : le SELECT *

Souvent dans ce blog, je poste des informations sur les “bonnes” pratiques SQL Server. Ce billet initie une nouvelle série : les “mauvaises” pratiques.

Lors des formations ou bien lors d’audits chez des clients, je peste contre l’utilisation du “SELECT *”.

Utilisé de manière directe depuis l’application cliente, il fait courir un gros danger : en cas de changement de structure de la table, il faut mettre à jour l’application sur l’ensemble des postes clients. Ce qui peut s’avérer assez lourd et couteux, comme j’ai pu le constater lors de certaines missions, lorsqu’il y a plusieurs milliers de postes clients. Imaginez une grille de données qui affiche le résultat de votre SELECT *, de manière bien formatée … Est ce que votre code prend en compte la présence d’une nouvelle colonne ?

Un autre inconvénient est directement relié à la performance. Le fait de ne sélectionner que les colonnes qui sont réellement utiles à l’application cliente va aussi permettre de réduire le volume de données qui transite sur le réseau. Cela va aussi permettre à l’optimiseur de requête de SQL Server de trouver, peut-être, un plan d’exécution plus optimisé (index couvrant, jointure d’index plutôt que d’accéder aux données, …). Donc forcément des performances accrues…

Un autre point n’est pas toujours mis en valeur : quel comportement ont des vues, des procédures stockées et des fonctions qui contiennent un SELECT * lorsque la structure de la table sous-jacente change ?

Créons une base de test, avec une table et un niveau d’abstraction composé d’une vue, une procédure stockée et une fonction qui réalisent le même SELECT *.

CREATE DATABASE DemoDB
GO

USE DemoDB
GO

CREATE TABLE TestTable
(
	c1 INT IDENTITY(1,1) PRIMARY KEY,
	c2 VARCHAR(10),
	c3 VARCHAR(10)
)
GO

INSERT INTO TestTable  (c2,c3)
VALUES ('Val C2','Val C3'),('Val C2','Val C3'),('Val C2','Val C3')
GO

CREATE VIEW vTestTable
AS
	SELECT * FROM TestTable
GO

CREATE PROCEDURE usp_TestTable
AS
	SELECT * FROM TestTable
GO

CREATE FUNCTION dbo.fn_TestTable()
RETURNS TABLE
AS
	RETURN (SELECT * FROM TestTable)
GO

Requêtons à présent les objets :

SELECT * FROM TestTable
SELECT * FROM vTestTable
EXEC usp_TestTable
SELECT * FROM dbo.fn_TestTable()
GO

Le résultat est identique pour les 4 opérations :

Modifions a présent la structure de la table :

ALTER TABLE TestTable
ADD c4 varchar(10)

Et exécutons a nouveau notre lot de 4 requêtes :

SELECT * FROM TestTable
SELECT * FROM vTestTable
EXEC usp_TestTable
SELECT * FROM dbo.fn_TestTable()
GO

Le SELECT direct et la procédure stockée ont pris en compte la modification du schéma. Ce qui parait normal car il n’y a pas de metadata stockées pour la “sortie” de ces objets.

Il reste donc à traiter les problèmes posés par la vue et la fonction de type table.

La recompilation de la requête utilisant la vue ne donne pas un meilleur résultat :

SELECT * FROM vTestTable OPTION (RECOMPILE)

Il faut trouver une méthode pour rafraichir les metadata de la vue. Soit on recréé la vue (attention un drop / create va supprimer les droits alors qu’un alter va les conserver) ou bien utiliser la procédure stockées sp_refreshview.

SP_REFRESHVIEW vTestTable

A présent la colonne ajoutée dans la table devient “visible” au travers de la vue.

Concernant la fonction de type table, la recompilation n’a pas plus d’effet.

SELECT * FROM dbo.fn_TestTable() OPTION (RECOMPILE)

Il faut encore une fois rafraichir les metadata pour que la nouvelle colonne soit accessible :

EXEC SYS.SP_REFRESHSQLMODULE 'dbo.fn_TestTable';
GO
SELECT * FROM dbo.fn_TestTable()

Attention, il ne faut surtout pas conclure de cette démonstration que l’utilisation de vues, de procédures stockées ou de fonctions est à proscrire. Bien au contraire.

Les vues vont permettre un niveau d’abstraction de la base qui peut être utile et simplifier quelques requêtes. En aucun cas un vue “classique” s’accompagne de gain de performance. Seules les vues indexées permettent d’accélérer des traitements en lecture. Toutes les écritures (INSERT/UPDATE et DELETE) sont pénalisées. Par contre, imbriquer des vues peut engendrer une perte de performance.

Les procédures stockées peuvent permettre d’optimiser la gestion du cache et diminuer le temps d’exécution en réduisant le nombre de compilations. Je vous renvoie à un précédent billet. Par contre, ne les nommez pas en “sp_xxxx”. SQL Server présume que ces procédures sont des procédures système et va donc les rechercher dans la base master en priorité, entrainant des évènements de type SP:CacheMiss…

En résumé, utilisez les vues, les procédures et les fonctions, mais lorsque vous les codez, nommez explicitement les colonnes dont vous avez réellement besoin. L’économie de quelques secondes de codage peut entrainer une indisponibilité de votre application et des jours de maintenance (codage, test, déploiement) !

Si vous souhaitez un audit de vos bases ou bien une formation sur les bonnes pratiques et sur l’optimisation SQL Server, n’hésitez pas à me contacter …