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 :

image

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

image

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)

image

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

image

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)

image

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()

image

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 …

A propos Christophe

Consultant SQL Server Formateur certifié Microsoft MVP SQL Server MCM SQL Server 2008
Cet article, publié dans Mauvaises pratiques, SQL Server, est tagué . Ajoutez ce permalien à vos favoris.

Un commentaire pour Mauvaises pratiques : le SELECT *

  1. Tony dit :

    Enfin un article bien complet et bien explicatif pour stopper l’utilisation de l’étoile lors d’un SELECT. En ce qui me concerne j’avais du mal à trouver les bons exemples pour justifier la non-utilisation de ce caractère lors de simples requêtes.

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