Introduction :
Il y a eu beaucoup de générateurs de code SQL dans les outils de développement, mais la qualité du SQL généré laissait parfois à désirer, obligeant le développeur à reprendre tout ou partie du code à la main.
Qu'en est-il de LINQ ?
C'est ce que je vais étudier dans ce post.
Requêtes mono table :
Commençons par des requêtes sur une seule table pour voir l'effet de la génération sur les colonnes, mais aussi, pus fondamental pour les performances , sur les critères de requêtes.
J'ai choisi d'espionner les requêtes par le profiler SQL, même si on peut obtenir le code au moyen du log de LINQ : de cette manière je suis sûr d'avoir la requête, ses paramètres et les valeurs des paramètres.
Voici une requête basique avec une clause WHERE sur une colonne :
et voici la requête générée :
exec
sp_executesql N'SELECT COUNT(*) AS [value]
FROM [dbo].[Article] AS [t0]
WHERE [t0].[NoOnglet] = @p0',N'@p0 int',@p0=3
qui correspond au count
exec
sp_executesql N'SELECT TOP (1) [t0].[NoArticle], [t0].[NoOnglet], [t0].[NoUsrAuteur], [t0].[NoNiveau],
[t0].[NoCours], [t0].[Libelle], [t0].[DtCreation], [t0].[DtModification], [t0].[DtMajUsr], [t0].[NoUsrRelecteur],
[t0].[TagDescription], [t0].[TagKeywords], [t0].[TagRobots], [t0].[Description], [t0].[TypeMaj]
FROM [dbo].[Article] AS [t0]
WHERE [t0].[NoOnglet] = @p0
ORDER BY [t0].[Libelle]',N'@p0 int',@p0=3
qui correspond au First
Une 1ère conclusion : comme en ADO, le count exécute une deuxième fois la requête on évitera donc de faire cela sur des grosses requêtes....
par ailleurs, et c'est le plus important, la clause WHERE est bien passée comme un paramètre typé en int
Beaucoup d'avantages à cela :
- Les performances : l'optimiseur de requête y trouvera son compte
- Le blocage de l'injection de SQL
Compliquons un peu la question en invoquant des méthodes sur des critères de recherche :
et là surprise agréable :
exec
sp_executesql N'SELECT COUNT(*) AS [value]
FROM [dbo].[Article] AS [t0]
WHERE [t0].[Libelle] LIKE @p0',N'@p0 varchar(4)',@p0='Les%'
le StartsWith a été traduit par un LIKE !
Appliquons maintenant des critères sur un type plus complexe : le datetime :
Encore une bonne surprise :
exec
sp_executesql N'SELECT COUNT(*) AS [value]
FROM [dbo].[Article] AS [t0]
WHERE (DATEPART(Year, [t0].[DtCreation]) = @p0) AND
(DATEPART(Month, [t0].[DtCreation]) = @p1) AND
(DATEPART(Day, [t0].[DtCreation]) = @p2)',
N'@p0 int,@p1 int,@p2 int',@p0=2008,@p1=3,@p2=8
Et sur une date/heure complète :
qui donne :
exec
sp_executesql N'SELECT COUNT(*) AS [value]
FROM [dbo].[Article] AS [t0]
WHERE [t0].[DtCreation] = @p0',N'@p0 datetime',@p0='2006-07-25 13:10:57:000'
Rien à dire, je n'aurais pas écrit autrement cette requête....
C'est un sans faute : les clauses WHERE sont parfaitement gérées !
Requêtes de partitionnement (ranking) :
Ces requêtes visent à extraire certaines lignes parmi d'autres et sont très utiles pour avoir le 1er élément d'une séquence, le dernier, ou une plage dans une séquence : cette dernière fonction servira en particulier pour la pagination et on la retrouvera quand je parlerai des GridView.
Comment extraire les n premières lignes ?
qui génère la requête SQL suivante :
exec
sp_executesql N'SELECT COUNT(*) AS [value]
FROM (
SELECT TOP (3) NULL AS [EMPTY]
FROM [dbo].[Article] AS [t0]
WHERE [t0].[NoOnglet] = @p0
ORDER BY [t0].[Libelle]
) AS [t1]',N'@p0 int',@p0=8
Eh oui ! Un TOP 3 a bien été généré pour prendre les 3 1ères lignes ....
Et maintenant comment extraire les lignes d'un rang donné ?
exec
sp_executesql N'SELECT [t1].[NoArticle], [t1].[NoOnglet], [t1].[NoUsrAuteur], [t1].[NoNiveau], [t1].[NoCours],
[t1].[Libelle], [t1].[DtCreation], [t1].[DtModification], [t1].[DtMajUsr], [t1].[NoUsrRelecteur], [t1].[TagDescription],
[t1].[TagKeywords], [t1].[TagRobots], [t1].[Description], [t1].[TypeMaj]
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [t0].[Libelle]) AS [ROW_NUMBER], [t0].[NoArticle], [t0].[NoOnglet],
[t0].[NoUsrAuteur], [t0].[NoNiveau], [t0].[NoCours], [t0].[Libelle], [t0].[DtCreation],
[t0].[DtModification], [t0].[DtMajUsr], [t0].[NoUsrRelecteur], [t0].[TagDescription],
[t0].[TagKeywords], [t0].[TagRobots], [t0].[Description], [t0].[TypeMaj]
FROM [dbo].[Article] AS [t0]
WHERE [t0].[NoOnglet] = @p0
) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p1 + 1 AND @p1 + @p2
ORDER BY [t1].[ROW_NUMBER]',N'@p0 int,@p1 int,@p2 int',@p0=8,@p1=2,@p2=3
Extra ! il a utilisé la fonction de RANKING ROW_NUMBER() OVERpour extraire la plage de lignes demandées !
Nous retrouverons cette fonction de pagination plus tard ...
Projections :
Jusqu'ici j'ai travaillé avec la table complète des articles : c'est une pratique que l'on évitera en développement pour des raisons de performances.
En effet, ramener systématiquement l'ensemble des données n'est pas une bonne chose, en particulier s'il y a du volume !
Il faut donc extraire certaines colonnes : c'est une projection dont voici un exemple :
Et la requête qui en découle :
SELECT [t0].[NoArticle], [t0].[Libelle]
FROM [dbo].[Article] AS [t0]
ORDER
BY [t0].[Libelle]
La projection utilise un type anonyme, instancié par new : c'est une nouveauté du C#.
Bon, maintenant comment utiliser ce type anonyme ?
Comme ceci :
Il suffit de déclarer une variable de type
var, Intellisense fait le reste et comprend ce qu'il y a dans ArticleCourt !
Requêtes Multi tables :
Voici une opération de jointure interne :
Et la requête SQL qui a été générée :
exec
sp_executesql N'SELECT [t0].[NoArticle], [t1].[NoOrdre] AS [Rang]
FROM [dbo].[Article] AS [t0]
INNER JOIN [dbo].[LigneArticle] AS [t1] ON [t0].[NoArticle] = [t1].[NoArticle]
WHERE [t0].[NoOnglet] = @p0
ORDER BY [t0].[Libelle]',N'@p0 int',@p0=2
Là encore pas de problème : le INNER JOIN est impeccable : rien à redire.
Et maintenant une opération d'intersection :
exec
sp_executesql N'SELECT DISTINCT [t0].[NoArticle], [t0].[NoOnglet], [t0].[NoUsrAuteur], [t0].[NoNiveau],
[t0].[NoCours], [t0].[Libelle], [t0].[DtCreation], [t0].[DtModification], [t0].[DtMajUsr], [t0].[NoUsrRelecteur],
[t0].[TagDescription], [t0].[TagKeywords], [t0].[TagRobots], [t0].[Description], [t0].[TypeMaj]
FROM [dbo].[Article] AS [t0]
WHERE (EXISTS(
SELECT NULL AS [EMPTY]
FROM [dbo].[Article] AS [t1]
WHERE ([t0].[NoArticle] = [t1].[NoArticle]) AND ([t1].[Libelle] LIKE @p0)
)) AND ([t0].[Libelle] LIKE @p1)',N'@p0 varchar(6),@p1 varchar(7)',@p0='%2005%',@p1='%index%'
Il est passé par une sous requête corrélée.
Ceux qui creuseront la question verront que des limites sur les types anonymes m'ont empêché de les utiliser dans les 'sous requêtes'.
Et maintenant une Union :
qui a généré :
exec
sp_executesql N'SELECT [t2].[NoArticle], [t2].[Libelle]
FROM (
SELECT [t0].[NoArticle], [t0].[NoOnglet], [t0].[NoUsrAuteur], [t0].[NoNiveau], [t0].[NoCours], [t0].[Libelle],
[t0].[DtCreation], [t0].[DtModification], [t0].[DtMajUsr], [t0].[NoUsrRelecteur], [t0].[TagDescription],
[t0].[TagKeywords], [t0].[TagRobots], [t0].[Description], [t0].[TypeMaj]
FROM [dbo].[Article] AS [t0]
WHERE [t0].[Libelle] LIKE @p0
UNION
SELECT [t1].[NoArticle], [t1].[NoOnglet], [t1].[NoUsrAuteur], [t1].[NoNiveau], [t1].[NoCours], [t1].[Libelle],
[t1].[DtCreation], [t1].[DtModification], [t1].[DtMajUsr], [t1].[NoUsrRelecteur], [t1].[TagDescription],
[t1].[TagKeywords], [t1].[TagRobots], [t1].[Description], [t1].[TypeMaj]
FROM [dbo].[Article] AS [t1]
WHERE [t1].[Libelle] LIKE @p1
) AS [t2]',N'@p0 varchar(7),@p1 varchar(6)',@p0='%index%',@p1='%2005%'
Cette fois, il est bien passé par une UNION du Transact SQL.
Et, pour finir, une petite différence ensembliste :
tout ce qui parle de 2005 sans parler des index .
Voyons le SQL :
exec
sp_executesql N'SELECT DISTINCT [t0].[NoArticle], [t0].[NoOnglet], [t0].[NoUsrAuteur], [t0].[NoNiveau],
[t0].[NoCours], [t0].[Libelle], [t0].[DtCreation], [t0].[DtModification], [t0].[DtMajUsr], [t0].[NoUsrRelecteur],
[t0].[TagDescription], [t0].[TagKeywords], [t0].[TagRobots], [t0].[Description], [t0].[TypeMaj]
FROM [dbo].[Article] AS [t0]
WHERE (NOT (EXISTS(
SELECT NULL AS [EMPTY]
FROM [dbo].[Article] AS [t1]
WHERE ([t0].[NoArticle] = [t1].[NoArticle]) AND ([t1].[Libelle] LIKE @p0)
))) AND ([t0].[Libelle] LIKE @p1)',N'@p0 varchar(7),@p1 varchar(6)',@p0='%index%',@p1='%2005%'
là encore, une requête corrélée.
Conclusion :
Les développeurs de LINQ ont fait de l'excellent travail : vous pouvez donc utiliser sans crainte LINQ au dessus de SQL Server (mon étude s'est limitée à lui pour le moment).
Il ne faudra bien évidemment pas oublier les bons principes :
- Limiter le nombre de colonnes en retour
- Ne jamais (sauf cas rare bien sûr) extraire toutes les lignes des tables
- Travailler avec les like (maintenant contains...) avec prudence
- etc. ...
En un mot génial !