Préambule
Cet article est la traduction d’un article d’un MVP suédois, Erland Sommarskog, qui traite de la problématique des recherches complexes, mettant en jeu de nombreux critères. Ce sujet, récurrent dans les applications de gestion, n’est pas des plus simples à gérer car il est difficile de rechercher tout et n’importe quoi, avec une fonction générique et ceci avec des performances optimums.
J’avais l’intention d’écrire sur un article sur cette problématique, synthétisant ainsi de nombreuses missions de consulting sur le sujet, mais j’ai préféré m’appuyer sur le travail qui a été souvent une de mes sources d’inspiration. C’est pourquoi j’ai préféré traduire un des articles d’Erland.
Je dis un des articles car Erland en a écrit de nombreux traitant des problématiques connexes. Certains sont référencés dans cet article mais vous pouvez les trouvez (en anglais) sur son site : http://www.sommarskog.se/. Je n’exclus pas d’en traduire d’autres car ils sont une véritable mine d’information sur l’écriture de code TSQL optimum.
Concernant cet article en particulier, je ne l’ai pas du tout altéré, il s’agit d’une traduction stricte. Il porte les traces du temps car il y a de nombreuses références et techniques ayant leur pertinence sur les version 2000 et antérieures de SQL Server. Cet article est d’ailleurs destiné à SQL Server 2005. On peut trouver ceci obsolète puisque la version 2008 est là depuis quelques mois mais il est toujours intéressant d’étudier les différentes techniques et reflexions sur le moteur SQL. Mieux on connait le fonctionnement du moteur et sa façon d’évoluer, mieux on est armé pour affronter des problèmes complexes de base de données. Ainsi, ne tournez pas tout de suite la page et prenez le temps d’y jeter un oeil. N’hésitez pas non plus à venir y puissez des techniques afin de compléter votre boite à outils. Cependant, il existe une version de l’article pour SQL 2008 qu’a fait récemment Erland. Vous pouvez le consulter directement, en anglais encore une fois, à cette adresse : version SQL Server 2008.
Et mon avis dans tout cela ? Evidemment, j’ai fait une traduction mais suis-je en phase avec Erland ? Globalement oui. Toutes les techniques présentées sont correctes et à connaître. Erland est très factuel dans son article, comme je l’aurai été moi même. Je reviendrai néanmoins sur la conclusion pour apporter ma pierre à l’édifice.
Bonne lecture.
Introduction
Une fonctionnalité très fréquente dans une système d’information est d’avoir une (ou plusieurs) fonctionnalité donnant la possibilité aux utilisateurs de rechercher dans les données en utilisant librement plusieurs critères. C’est un véritable challenge, parce que d’une part il faut fournir des données correctes mais il faut également garantir un temps de réponse acceptable, tout du moins pour les recherches les plus communes. Et par-dessus tout, la solution doit être maintenable de façon à supporter de nouveaux besoins et spécifications.
Dans cet article, nous allons voir quelques techniques pour traiter cette problématique. Il y a 2 grandes voies : utiliser du code SQL dynamique ou bien du code SQL statique. Il y a aussi quelques solutions hybrides qui mixent les deux.
Avec les versions 2005 et précédentes de SQL Server, dès l’instant où le nombre de critères de recherche reste raisonnable, la solution dynamique sera la plus pertinente en termes de performance, développement et maintenance. Jusqu’à la version 2000, pour utiliser du SQL dynamique, vous devrez donner aux utilisateurs des permissions de SELECT sur les tables impliquées et c’est souvent difficilement tolérable. La version 2005 offre quelques contournements possibles.
Avec SQL Server 2008, le stockage des tables a changé, ainsi, nous pouvons maintenant implémenter des solutions à base de SQL statique faciles à coder et donc plus crédibles. Il y a toujours un compromis important à faire entre statique et dynamique, mais les postulats de départ sont différents par rapports aux versions précédentes. Pour cette raison, cet article existe pour les versions 2008 et les autres (2005, 2000 et 7). Dans cet article, nous nous concentrerons sur les versions 2005 et précédentes.
Nous allons tout d’abord regarder du côté du SQL dynamique et tenter de pointer les différents pièges à éviter. Ensuite, nous couvrirons les techniques de SQL statique et passerons en revue les méthodes et astuces que l’on peut combiner pour implémenter une fonctionnalité de recherche. Et pour terminer, nous étudierons 2 solutions hybrides qui utilisent du code statique et dynamique et en conséquence supprime le problème de permissions.
Etude de cas : recherche de commandes
Tout au long de cet article, nous développerons des procédures stockées qui récupèrent des commandes mais aussi des clients et des produits dans les bases Northwind et Northgale. La base Northwind était la base d’exemple fournie avec SQL Server 7 et 2000. Elle n’est pas livrée avec les versions 2005 et 2008 mais elle est disponible sur le site de Microsoft. La base Northgale est une base plus importante en volumétrie, dérivée de la base Nothwind.
La fonctionnalité de recherche a la signature suivante :
CREATE PROCEDURE Commande_S_Recherche (
@no_commande int = NULL,
@datedebut datetime = NULL,
@datefin datetime = NULL,
@prixmini money = NULL,
@prixmax money = NULL,
@no_client nchar(5) = NULL,
@nom_client nvarchar(40) = NULL,
@ville nvarchar(15) = NULL,
@region nvarchar(15) = NULL,
@pays nvarchar(15) = NULL,
@no_produit int = NULL,
@nom_produit nvarchar(40) = NULL)
Les paramètres sont tous optionnels et produisent des résultats différents. Quand un paramètre n’est pas fourni, la condition de recherche ne doit pas être appliquée. Si aucun paramètre n’est passé, la procédure doit ramener l’ensemble des commandes.
|
Paramètre |
Utilisation |
|
@no_commande |
Retourne une commande unique |
|
@datedebut |
Retourne les commandes passées après une date donnée |
|
@datefin |
Retourne les commandes passées avant une date donnée |
|
@prixmini |
Retourne les commandes ayant un prix minimum |
|
@prixmax |
Retourne les commandes ayant un prix maximum |
|
@no_client |
Retourne les commandes d’un client donné |
|
@nom_client |
Retourne les commandes des clients dont le nom commence par une chaine de caractères précise |
|
@ville |
Retourne les commandes des clients habitant une ville précise |
|
@region |
Retourne les commandes des clients habitant une région précise |
|
@pays |
Retourne les commandes des clients habitant une pays précis |
|
@no_produit |
Retourne les commandes d’un produit précis |
|
@nom_produit |
Retourne les commandes de produits dont le nom commence par une chaine de caractères précise |
Le code de la procedure stockée va ressembler au code suivant :
SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
c.CustomerID, c.CompanyName, c.Address, c.City, c.Region,
c.PostalCode, c.Country, c.Phone, p.ProductID,
p.ProductName, p.UnitsInStock, p.UnitsOnOrder
FROM Orders o
JOIN [Order Details] od ON o.OrderID = od.OrderID
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN Products p ON p.ProductID = od.ProductID
WHERE ???
ORDER BY o.OrderID
Cet exemple est gardé simple puisque chaque condition peut être implémentée par les opérateurs = <= >= ou LIKE. Dans des conditions réelles, la fonctionnalité est souvent plus complexe avec des conditions quant à l’ordonnancement du résultat, des jointures conditionnelles, etc. De telles considérations ont volontairement été écartées pour se concentrer sur le sujet principal même si certaines conditions complexes seront abordées dans certains chapitres. Le but de cet article est de vous fournir une bonne base de compétences pour que vous soyez armés pour traiter des cas plus complexes.
Quelquefois, vous avez besoin de rechercher une liste de valeur comme @no_produit IN (1, 34, 89). La méthode pour traiter ce cas est de décomposer la liste pour la traiter comme une table. Ceci n’est pas traité ici mais est traité dans un article dédié : Tableaux et listes dans SQL Server (en anglais) - [NDT – vous trouverez aussi des informations en français dans cet article de Sébastien Pertus].
Il n’est pas inhabituel non plus d’avoir comme fonctionnalité le fait que l’utilisateur puisse spécifier comment le résultat doit être ordonné. Ce sujet ne sera pas non plus traité dans cet article. Si vous utilisez du SQL dynamique, vous pouvez biensur construire la clause ORDER BY. Si au contraire vous utilisez du SQL statique, jetez un œil aux différentes suggestions de cet article : SQL Dynamique (en anglais)
Dans cet article, différentes implémentations de la procédure Commande_S_Recherche sont proposées. Bien que vous trouviez la plupart directement dans le texte, vous pouvez les télécharger ici.
Concernant les aspects de performances, les procédures ont été testées avec 6 jeux de critères de recherche différents. Bien que l’article réfère aux résultats, aucun chiffre n’est avancé. La pertinence d’une solution dans une situation donnée dépend de nombreux paramètres comme les index disponibles, la distribution des données, etc. L’important pour considérer la performance est de réaliser vos propres tests sur votre volumétrie de production.
La base de données Nothgale
La base de données Northwind est très petite et il est donc difficile de tirer des conclusions sur les performances des requêtes. Une requête qui devrait normalement utiliser un index va en réalité faire un scan tout simplement parce que la volumétrie indique que le scan sera tout aussi couteux.
Pour cette raison, la base Northgale a été créée. Northgale dispose des mêmes tables et index que Northwind mais la volumétrie passe de 830 commandes à 344035. Pour installer Northgale, il suffit d’exécuter ce script. Il copie les données de Northwind donc cette base doit être au préalable installée. Pour l’exécution du script, vous aurez besoin de 4,6Go d’espace disque sur SQL Server 2005 et 2,6 sur SQL Server 2000. Une fois l’installation terminée, la base de données ne prend plus que 520Mo, l’espace du journal de transaction nécessaire pour les insertions est récupéré à la fin du script. Par défaut, la base est installée dans le même répertoire que celui de la base master mais vous pouvez modifier le script pour changer cette configuration.
Les données ont été démultipliées en operant une jointure croisée (CROSS JOIN). Pour les IDs, des combinaisons ont été faites, ainsi, il y a maintenant 6640 clients au lieu des 91 initiaux. De nouveaux noms de client et produits sont générés en permutant les noms actuels. Aussi, de nouvelles villes, pays et régions ont été créés, et une recherche sur une ville seule peut ramener beaucoup de résultats.
Gardez à l’esprit que la base Northgale est elle aussi une “petite” base de données par rapport à ce que l’on peut trouver en production dans les entreprises aujourd’hui. En l’état, elle monte complètement en mémoire sur une machine de développement standard. Ce qui fait que même une requête non optimisée qui utiliserait des scans retournera des résultats en quelques secondes. Mais c’est suffisant pour indiquer combien peut être bonne ou mauvaise une solution même s’il ne vaut mieux pas établir de conclusions avancées. Par exemple, il faut prendre en considération que la distribution des données n’est pas des plus réalistes de par la méthode de création de la base.
Quand vous implémentez une fonction de recherche dynamique, il faut toujours la tester sur votre base de données de production et bien étudier les plans d’exécution pour les cas les plus communs.
SQL Dynamique
Performances
Vous avez certainement entendu cette phrase : n’utilisez pas de SQL dynamique, c’est lent ! En effet, utilisé de la mauvaise façon, du SQL dynamique peut dégrader vos performances et quelquefois sévèrement. Par exemple, si vous utilisez sp_execute_sql et que vous référencez toujours les tables avec une notation en 2 parties (ex : dbo.Customers), l’impact du SQL dynamique est négligeable. En revanche, si vous utilisez EXEC() en référençant uniquement le nom de la table, il peut y avoir un surcoût, pas pour l’exécution de la requête elle-même, mais pour la construction du plan d’exécution. Avec des procédures stockées et du SQL dynamique correctement écrit, SQL Server pourra cacher le plan d’exécution et le réutiliser.
Pour des recherches dynamiques comme notre recherche de commandes, il n’y a pas un seul plan d’exécution qui couvre toutes les combinaisons des paramètres d’entrée. Un plan qui peut répondre en moins d’une seconde quand l’utilisateur précise un produit peut très bien prend 2 minutes si l’utilisateur précise un client et vice-versa. Dans ce cas, prendre 100ms de plus pour calculer un plan d’exécution à chaque nouvelle combinaison de paramètres est le prix à payer pour un gain de performances. Particulièrement si le plan reste en cache pour d’autres utilisateurs.
Ainsi, d’un point de vue performances, le SQL dynamique est souvent LA solution pour ce type de problème.
Sécurité
Avec SQL Server 2000, on doit tenir compte de l’aspect sécurité. Gardez à l’esprit qu’avec les procédures stockées, les utilisateurs n’ont pas besoin d’avoir des droits d‘accès directs aux tables ou aux vues référencées. Seule la permission d’exécuter les procédures est nécessaire. A l’exécution, les droits du propriétaire de la procédure seront appliqués. Cependant, du SQL dynamique dans une procédure ne fait pas partie de la procédure ; et quand il est exécuté, ce sont les permissions de l’utilisateur courant qui sont utilisées. Donc pour utiliser du SQL dynamique, il faut donner des droits de SELECT sur les tables aux utilisateurs ce qui est bien souvent inacceptable.
Mais ce n’est pas totalement vrai. Il existe des solutions hybrides où l’on met le SQL statique dans une vue ou dans une fonction. Cette dernière en particulier peut vous offrir toute la puissance du SQL dynamique sans avoir à exposer les tables directement aux utilisateurs. Ce point sera traité dans la section Solutions hybrides.
Avec SQL Server 2005, il y a 2 façons supplémentaires de contourner les problèmes de permissions. Vous pouvez signer une procédure avec un certificat et associer ce certificat à un utilisateur virtuel auquel vous donnez les bonnes permissions. Vous pouvez aussi utiliser la clause EXECUTE AS pour simuler un utilisateur. Cette dernière solution est moins compliquée à mettre en œuvre mais a des effets de bords qui peuvent casser la sécurité au niveau ligne et rendre la surveillance du système plus difficile. Ce point est traité en détail dans cet article : Granting Permissions through Stored Procedures (en anglais).
Pour aller plus loin
Il y a encore des choses à dire sur le SQL dynamique mais ce n’est pas le propos de cet article. Référez vous à l’article suivant : The Curse and Blessings of Dynamic SQL (en anglais) qui traite du SQL dynamique en général. Dans cet article, les problématiques de performances et de sécurité sont traitées plus en détail.
L’importance des tests
Parce que l’on a affaire à de la génération de code, il est très important de tester exhaustivement et en profondeur votre procédure. Sans ces précautions, les utilisateurs pourront recevoir des erreurs de syntaxe SQL de la part de votre procédure une fois la requête sera compilée.
Ne négligez pas non plus les tests de performance, en tenant bien compte de la volumétrie que vous aurez en production.
Les méthodes pour faire du SQL dynamique
Il y a 3 façons de faire :
- Du code T-SQL qui construit la requête et qui l’exécute avec sp_executesql
- Du code T-SQL qui construit la requête et qui l’exécute avec EXEC()
- Une procédure managée (SQLCLR) qui construit une requête paramétrée et qui l’exécute (SQL 2005)
De ces 3 méthodes, les choix 1 et 3 sont des bons choix. L’utilisation de EXEC() est moins pertinent car les possibilités de réutilisation des plans d’exécution sont plus infimes. Il y a néanmoins quelques cas spéciaux où EXEC() et les techniques s’y afférant seront utiles.
Nous allons discuter en detail d’une implementation de Commande_S_Recherche qui utilise sp_executesql. Nous verrons également une implémentation de la procédure avec EXEC() mais considérez cela comme du bonus car obsolète ; vous pouvez d’ailleurs passer cette section.
Et enfin, avant de passer au SQL statique, nous verrons aussi des situations où la mise en cache des plans d’exécution dégrade les performances
sp_executesql est une procédure système qui prend une instruction T-SQL comme premier paramètre, la déclaration des paramètres de cette instruction en second et ensuite lesdits paramètres. Voici la procédure Commande_S_Recherche_1 qui utilise sp_executesql :
1: CREATE PROCEDURE Commande_S_Recherche_1
2: @no_commande int = NULL,
3: @datedebut datetime = NULL,
4: @datefin datetime = NULL,
5: @prixmini money = NULL,
6: @prixmaxi money = NULL,
7: @no_client nchar(5) = NULL,
8: @nom_client nvarchar(40) = NULL,
9: @ville nvarchar(15) = NULL,
10: @region nvarchar(15) = NULL,
11: @pays nvarchar(15) = NULL,
12: @no_produit int = NULL,
13: @nom_produit nvarchar(40) = NULL,
14: @debug bit = 0 AS
15:
16: DECLARE @sql nvarchar(4000),
17: @paramlist nvarchar(4000)
18:
19: SELECT @sql =
20: 'SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
21: c.CustomerID, c.CompanyName, c.Address, c.City,
22: c.Region, c.PostalCode, c.Country, c.Phone,
23: p.ProductID, p.ProductName, p.UnitsInStock,
24: p.UnitsOnOrder
25: FROM dbo.Orders o
26: JOIN dbo.[Order Details] od ON o.OrderID = od.OrderID
27: JOIN dbo.Customers c ON o.CustomerID = c.CustomerID
28: JOIN dbo.Products p ON p.ProductID = od.ProductID
29: WHERE 1 = 1'
30:
31: IF @no_commande IS NOT NULL
32: SELECT @sql = @sql + ' AND o.OrderID = @xorderid' +
33: ' AND od.OrderID = @xorderid'
34:
35: IF @datedebut IS NOT NULL
36: SELECT @sql = @sql + ' AND o.OrderDate >= @xfromdate'
37:
38: IF @datefin IS NOT NULL
39: SELECT @sql = @sql + ' AND o.OrderDate <= @xtodate'
40:
41: IF @prixmini IS NOT NULL
42: SELECT @sql = @sql + ' AND od.UnitPrice >= @xminprice'
43:
44: IF @prixmaxi IS NOT NULL
45: SELECT @sql = @sql + ' AND od.UnitPrice <= @xmaxprice'
46:
47: IF @no_client IS NOT NULL
48: SELECT @sql = @sql + ' AND o.CustomerID = @xcustid' +
49: ' AND c.CustomerID = @xcustid'
50:
51: IF @nom_client IS NOT NULL
52: SELECT @sql = @sql + ' AND c.CompanyName LIKE @xcustname + ''%'' '
53:
54: IF @ville IS NOT NULL
55: SELECT @sql = @sql + ' AND c.City = @xcity'
56:
57: IF @region IS NOT NULL
58: SELECT @sql = @sql + ' AND c.Region = @xregion'
59:
60: IF @pays IS NOT NULL
61: SELECT @sql = @sql + ' AND c.Country = @xcountry'
62:
63: IF @no_produit IS NOT NULL
64: SELECT @sql = @sql + ' AND od.ProductID = @xprodid' +
65: ' AND p.ProductID = @xprodid'
66:
67: IF @nom_produit IS NOT NULL
68: SELECT @sql = @sql + ' AND p.ProductName LIKE @xprodname + ''%'' '
69:
70: SELECT @sql = @sql + ' ORDER BY o.OrderID'
71:
72: IF @debug = 1
73: PRINT @sql
74:
75: SELECT @paramlist = '@xorderid int,
76: @xfromdate datetime,
77: @xtodate datetime,
78: @xminprice money,
79: @xmaxprice money,
80: @xcustid nchar(5),
81: @xcustname nvarchar(40),
82: @xcity nvarchar(15),
83: @xregion nvarchar(15),
84: @xcountry nvarchar(15),
85: @xprodid int,
86: @xprodname nvarchar(40)'
87:
88: EXEC sp_executesql @sql, @paramlist,
89: @no_commande, @datedebut, @datefin, @prixmini,
90: @prixmaxi, @no_client, @nom_client, @ville, @region,
91: @pays, @no_produit, @nom_produit
Les numéros de ligne sont là pour y faire référence dans les explications suivantes.
Code général
Les lignes 19 à 29 servent à composer la base du SQL. La condition WHERE 1=1 de la ligne 29 permet d’appeler la procédure sans paramètre et par conséquent de ramener toutes les commandes.
Ensuite, sur les lignes 31 à 68 sont testés tous les paramètres (sauf @debug) et si un paramètre est non nul (donc fourni), la condition sur la colonne correspondante est ajoutée à la chaîne SQL. Et enfin, à la ligne 70, on ajoute la clause ORDER BY.
A la ligne 72, le paramètre @debug est testé. S’il est à 1, la chaîne SQL est affichée. Ceci est pratique dans le cas d’une erreur de compilation pour comprendre l’erreur dans la construction de la syntaxe SQL. L’erreur classique étant d’oublier un espace.
Sur les lignes 75 à 86 est déclarée la liste des paramètres du SQL dynamique et enfin l’exécution a lieu aux lignes 88 à 91
Des détails sur sp_executesql
Avec l’utilisation de sp_executesql, la requête paramétrée est placée en cache. Ainsi, si un second utilisateur effectue une recherche en utilisant le même jeu de paramètres, le plan d’exécution sera réutilisé, même si l’utilisateur fournit des valeurs différentes dans sa recherche.
La liste des paramètres passée à sp_executesql demeure la même que celle de la procédure elle-même. Les paramètres ont été préfixés avec un x. Il n’y a pas de raison technique à cela et nous aurions pu garder les noms originaux. Le but est de mettre en lumière le fait qu’il n’y ait pas de relation entre les paramètres de la procédure Commande_S_Recherche et les paramètres à l’intérieur du SQL dynamique. Ce dernier possède son propre scope de variables et paramètres et on peut le comparer à une procédure créée à la volée.
Le T-SQL et la liste des paramètres doit être une chaîne Unicode, en conséquence, les variables @sql et @paramlist sont de type nvarchar. N’oubliez pas de préciser N si vous passez ces paramètres sous la forme de littéraux.
EXEC sp_executesql N'SELECT * FROM Orders WHERE OrderID = @no_commande', N'@no_commande int', @no_commande = 10872
Vous pouvez aussi utiliser des paramètres OUTPUT avec sp_executesql.
Déclaration de la chaîne SQL
Vous pouvez voir à la ligne 16 la declaration suivante :
DECLARE @sql nvarchar(4000)
Avec les versions 7 et 2000, il n’y a pas moyen de travailler avec des variables plus grandes que 8000 octets, ce qui impose une limitation à sp_executesql bien que cette dernière accepte des paramètres de type ntext.
La version 2005 introduit un nouveau type de données, nvarchar(MAX) qui est illimité comme ntext mais sans ses restrictions. Vous pouvez donc déclarer la variable @sql comme étant un nvarchar(max) et ainsi ne plus se préoccuper des problèmes de taille de la chaîne SQL.
Le préfixe dbo
Les lignes 25 à 28 présentent un point important :
FROM dbo.Orders o
JOIN dbo.[Order Details] od ON o.OrderID = od.OrderID
JOIN dbo.Customers c ON o.CustomerID = c.CustomerID
JOIN dbo.Products p ON p.ProductID = od.ProductID
Comme vous pouvez le voir, toutes les tables sont référencées avec une notation en 2 parties, précisant le schema (propriétaire en SQL 7/2000). Sans le préciser, chaque utilisateur aurait eu sa propre version du plan d’exécution ce qui remplit inutilement le cache. Comme discuté dans l’article Curse and Blessings..., la requête fonctionnerait sans spécifier le schéma mais il est préférable de le spécifier s’il est connu, ce qui est majoritairement le cas.
Double condition
Vous pouvez vous demander pourquoi il y a 2 conditions placées sur les paramètres no_commande, no_client et no_produit (lignes 32-33, 48-49, 64-65), sur chaque table les utilisant. C’est tout simplement pour spécifier exhaustivement à l’optimiseur de requête toutes les conditions connues. Cette façon de faire trouvait son écho avec les anciennes versions de SQL Server mais depuis la version 2005, elle n’est plus nécessaire car l’optimiseur sait faire suivre les conditions sur les jointures. Cette ancienne habitude a été conservée dans les scripts de cet article.
Utilisation de différentes tables
Admettons, par exemple, qu’il y ait une table HistoriqueCommandes dans la base Northwind contenant les commandes envoyées et payées, et que les utilisateurs souhaitent chercher des commandes soit dans la table courante, soit dans l’historique. Comment résoudre ce point ?
On peut être tenté d’écrire quelque chose comme SELECT…FROM @table_commandes et d’ajouter @table_commandes en tant que paramètre. Mais le langage T-SQL ne permet pas de paramétrer la clause FROM (les raisons étant discutées dans Curse and Blessings...).
En fait, vous ne devriez jamais passer le nom de la table en tant que paramètre de procédure stockée ; une fois que l’on utilise des procédures stockées, toutes les références aux tables et colonnes doivent être dans les procédures. La bonne façon de faire est d’ajouter un autre paramètre à la procédure Commande_S_Recherche :
@historique bit DEFAULT = 0
Et la ligne 25 devient:
'FROM dbo.' +
CASE @historique
WHEN 0 THEN 'Commandes'
WHEN 1 THEN 'HistoriqueCommandes'
END + ' ...'
Tests de la procédure
Voici quelques cas de tests de :
EXEC Commande_S_Recherche_1 @no_commande = 11000
EXEC Commande_S_Recherche_1 @no_client = 'ALFKI'
EXEC Commande_S_Recherche_1 @no_produit = 76
EXEC Commande_S_Recherche_1 @no_produit = 76, @no_client = 'RATTC'
EXEC Commande_S_Recherche_1 @datedebut = '19980205', @datefin = '19980209'
EXEC Commande_S_Recherche_1 @ville = 'Bräcke', @no_produit = 76
Si vous essayez ces requêtes et que vous inspectez les plans d’exécutions, vous verrez que les index sur les colonnes de recherche sont utilisés dans la base Northgale à l’exception de l’index sur la ville dans le dernier cas mais ceci est parce que cette colonne est peu discriminante. Si vous lancez les requêtes sur la base Northwind, vous verrez beaucoup plus de scans qui sont dus aux petits volumes de cette base.
Using EXEC()
Comme précédemment mentionné, EXEC() est une solution inférieure à sp_executesql et la CLR. La raison est que EXEC() ne prend pas de paramètres et donc vous devez construire la requête avec les paramètres directement dans la chaîne (inline). Cela exclue la réutilisation des plans d’exécution. Cela rend aussi plus complexe le code de construction de la requête.
Néanmoins, il y a 3 situations où l’utilisation d’EXEC (ou tout du moins de paramètres en ligne) est nécessaire.
- Vous utilisez SQL 7 ou 2000 et votre requête dépasse les 4000 caractères. Comme vous pouvez écrire EXEC(@sql1 + @sql2 + @sql3 …), il n’y a pas de limite à la taille de la requête. Depuis 2005, avec l’apparition du type nvarchar(MAX), plus de limitation à l’exécution de sp_executesql.
- Vous utilisez une solution hybride avec une fonction de table inline, une solution décrite plus loin dans l’article
- Vous optez pour une valeur de paramètre insérée en ligne. Ce point sera abordé dans le chapitre Quand le cache n’est pas ce que nous voulons
Bien qu’il ne soit pas nécessaire de maîtriser EXEC() autant que sp_executesql, il est intéressant d’étudier cette technique pour présenter les pièges à éviter au cas où vous en auriez besoin. Vous pouvez toutefois passer cette section et y jeter un œil au besoin.
Commande_S_Recherche_2
A la difference de sp_executesql, EXEC() prends un unique paramètre de façon à ce que la requête ne puisse pas être paramétrée. A la place, il faut mettre les valeurs de la requête dans la chaîne SQL. A première vue, on peut trouver cela plus simple mais comme nous le verrons, c’est en fait plus compliqué.
Voici le code de la procédure Commande_S_Recherche_2:
1: CREATE PROCEDURE Commande_S_Recherche_2
2: @no_commande int = NULL,
3: @datedebut datetime = NULL,
4: @datefin datetime = NULL,
5: @prixmini money = NULL,
6: @prixmaxi money = NULL,
7: @no_client nchar(5) = NULL,
8: @nom_client nvarchar(40) = NULL,
9: @ville nvarchar(15) = NULL,
10: @region nvarchar(15) = NULL,
11: @pays nvarchar(15) = NULL,
12: @no_produit int = NULL,
13: @nom_produit nvarchar(40) = NULL,
14: @debug bit = 0 AS
15:
16: DECLARE @sql1 nvarchar(4000),
17: @sql2 nvarchar(4000),
18: @datedebutstr char(23),
19: @datefinstr char(23),
20: @prixministr varchar(25),
21: @prixmaxistr varchar(25)
22:
23: SELECT @datedebutstr = convert(char(23), @datedebut, 126),
24: @datefinstr = convert(char(23), @datefin, 126),
25: @prixministr = convert(varchar(25), @prixmini),
26: @prixmaxistr = convert(varchar(25), @prixmaxi)
27:
28: SELECT @sql1 =
29: 'SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
30: c.CustomerID, c.CompanyName, c.Address, c.City,
31: c.Region, c.PostalCode, c.Country, c.Phone,
32: p.ProductID, p.ProductName, p.UnitsInStock,
33: p.UnitsOnOrder
34: FROM dbo.Orders o
35: JOIN dbo.[Order Details] od ON o.OrderID = od.OrderID
36: JOIN dbo.Customers c ON o.CustomerID = c.CustomerID
37: JOIN dbo.Products p ON p.ProductID = od.ProductID
38: WHERE 1 = 1'
39:
40: IF @no_commande IS NOT NULL
41: SELECT @sql1 = @sql1 + ' AND o.OrderID = ' + str(@no_commande) +
42: ' AND od.OrderID = ' + str(@no_commande)
43:
44: IF @datedebut IS NOT NULL
45: SELECT @sql1 = @sql1 + ' AND o.OrderDate >= ' +
46: quotename(@datedebutstr, '''')
47:
48: IF @datefin IS NOT NULL
49: SELECT @sql1 = @sql1 + ' AND o.OrderDate <= ' +
50: quotename(@datefinstr, '''')
51:
52: IF @prixmini IS NOT NULL
53: SELECT @sql1 = @sql1 + ' AND od.UnitPrice >= ' + @prixministr
54:
55: IF @prixmaxi IS NOT NULL
56: SELECT @sql1 = @sql1 + ' AND od.UnitPrice <= ' + @prixmaxistr
57:
58: SELECT @sql2 = ''
59:
60: IF @no_client IS NOT NULL
61: SELECT @sql2 = @sql2 + ' AND o.CustomerID = ' +
62: quotename(@no_client, '''') +
63: ' AND c.CustomerID = ' +
64: quotename(@no_client, '''')
65:
66: IF @nom_client IS NOT NULL
67: SELECT @sql2 = @sql2 + ' AND c.CompanyName LIKE ' +
68: quotename(@nom_client + '%', '''')
69:
70: IF @ville IS NOT NULL
71: SELECT @sql2 = @sql2 + ' AND c.City = ' +
72: quotename(@ville, '''')
73:
74: IF @region IS NOT NULL
75: SELECT @sql2 = @sql2 + ' AND c.Region = ' +
76: quotename(@region, '''')
77:
78: IF @pays IS NOT NULL
79: SELECT @sql2 = @sql2 + ' AND c.Country = ' +
80: quotename(@pays, '''')
81:
82: IF @no_produit IS NOT NULL
83: SELECT @sql2 = @sql2 + ' AND od.ProductID = ' + str(@no_produit) +
84: ' AND p.ProductID = ' + str(@no_produit)
85:
86: IF @nom_produit IS NOT NULL
87: SELECT @sql2 = @sql2 + ' AND p.ProductName LIKE ' +
88: quotename(@nom_produit + '%', '''')
89:
90: IF @debug = 1
91: PRINT @sql1 + @sql2
92:
93: EXEC(@sql1 + @sql2 + ' ORDER BY o.OrderID')
Notes générales
En construisant une requête non-paramétrée, on doit être très discipliné dans l’écriture du code. On peut vite être perdu dans les guillemets et autres séparateurs de chaînes. En effet, on trouve souvent dans les newsgroups des exemples de code comme celui-ci :
EXEC('SELECT col1, col2, ...
FROM ...
WHERE ' + CASE @par1 IS NULL THEN ' + col = ''' + @par + ''' + ...)
Ce code est difficile à lire et à maintenir. S’il y a une erreur, vous n’avez bien souvent aucune idée du code SQL généré. Commande_S_Recherche2 agrège le code SQL dans 2 variables et un paramètre @debug permet d’afficher le code de la requête pour vérification.
L’utilisation de la fonction quotename() permet de réduire le besoin en guillemets simples au minimum.
Gardez à l’esprit que EXEC() ouvre un nouveau scope. Le SQL généré ne peut pas référencer des variables déclarées dans la procédure englobante. Aussi, si le SQL utilise une table temporaire, elle sera supprimée à la fin du scope.
L’utilisation de Quotename()
Comme vous pouvez le voir dès la ligne 46, l’utilisation de la function quotename() est fréquente. Cette function prend 2 paramètres : un chaîne de caractères et un délimiteur. Si le délimiteur est (, [, < ou {, l’élément fermant correspondant sera aussi pris en compte. La valeur retournée par la fonction est la chaîne entourée du délimiteur et si le délimiteur est trouvé au sein de la chaîne, il est doublé. Le délimiteur par défaut est [. Voici quelques exemples :
SELECT quotename('Order Details') -- => [Order Details]
SELECT quotename('A l' + char(39) + 'attaque', '''') -- => 'A l''attaque'
SELECT quotename('abc{}def', '{') -- => {abc{}}def}
De ces exemples, le second nous intéresse dans notre contexte. En T-SQL, on utilise un simple quote pour délimiter les chaînes de caractères. Si on doit en inclure dans la chaîne littérale, nous devons le doubler. Ainsi, '''' (4x ') correspond à un seul caractère exprimé de façon littérale : ('). Aussi, vous pouvez utiliser la valeur char(39) comme dans l’exemple ci-dessus.
Mais pourquoi utiliser tout cela ? Tout simplement cela rend le code plus facile à lire. Cela permet à un utilisateur d’entrer une valeur comme Let’s Stop N Shop comme nom de société (c’est une des valeurs de la base Northwind).
Cela augmente aussi la protection contre des attaques de type SQL Injection, une technique où l’intrus injecte un délimiteur puis des instructions SQL. Référez vous à cet article pour plus d’informations sur le sujet : Curse and Blessings...
Note : la fonction quotename() prend un paramètre de type nvarchar(128) donc si vous avez un paramètre plus long, vous ne pourrez pas l’utiliser. Pour contourner cette limitation, il faudra utiliser la fonction replace() ou bien la fonction présentée dans l’article Curse and Blessings…
Paramètres de type Datetime
Tous les paramètres qui ne sont pas des chaînes de caractères doivent être convertis en littéraux de type chaîne. Les lignes 23-24 montrent l’attention portée au type DateTime. L’expression utilisée ne sort pas du chapeau :
convert(char(23), @datedebut, 126)
Le résultat sera de la forme 2003-04-06T21:14:26.627, qui est le standard ISO 8601, utilisé notamment par la norme XML. Plus important, c’est l’un des 3 formats littéraux de date et heure dans SQL Server qui ne dépende pas du paramétrage régional.
SET DATEFORMAT mdy
SELECT convert(datetime, '02/07/09')
SET DATEFORMAT dmy
SELECT convert(datetime, '02/07/09')
go
SELECT convert(datetime, '2002-12-19') -- Erreur
go
SET LANGUAGE French
SELECT convert(datetime, 'Jun 12 2003') -- Erreur (C'est "Juin" en français, et non June.)
Le paramètre de style 126, ainsi que le format avec un T entre la date et l’heure, est uniquement disponible depuis la version 2000. Sur SQL Server 7, on utilisera 112 mais qui ne prend en charge les heures.
Pour plus d’information sur les différentes options que l’on peut préciser à la fonction convert(), jetez un œil à la section CAST and CONVERT de la documentation de SQL Server
Paramètres numériques
Sur les lignes 25-26, on fait attention aux 2 paramètres de type monétaire et sur les lignes 41-42 et 83-84 aux 2 paramètres de type entier (no_commande et no_produit). Comme vous pouvez le voir, nous utilisons une technique différente. Au lieu d’utiliser la méthode convert(), nous utilisons à la place la méthode str(), plus pratique pour les types numériques et monétaires. Mais attention, par défaut str() arrondit à l’entier le plus proche alors que convert() garde 2 décimales après la virgule, c’est pourquoi la méthode convert() est préférée pour les paramètres @prix_maximum/minimum.
Ici nous n’avons pas de types décimaux ou flottants. Référez-vous à la documentation de CAST, CONVERT et STR pour plus d’informations.
varchar and nvarchar
La base Northwind utilise des types nvarchar. La procédure de recherche n’en tient pas compte. Les données de la base Northwind sont restreintes aux caractères Windows-Latin-1, qui couvre les langues comme l’anglais, le français ou l’espagnol. Si vous avez une collation basée sur ce jeu de caractères, vous n’aurez jamais de résultat incorrect. Cependant un utilisateur russe, essayant :
EXEC Commande_S_Recherche_2 @ville = N'Bräcke'
ne renverra pas les commandes de Folk och Fä HB à Bräcke, parce que Bräcke sera converti en varchar (Bracke). Il faut donc corriger la procédure et ajouter un N devant le paramètre (ligne 71-72)
SELECT @sql2 = @sql2 + ' AND c.City = N' + quotename(@ville, '''')
Le résultat en SQL sera c.City = N'Bräcke'. Le N permet d’indiquer que la chaîne littérale est en UNICODE, correspondant au type nvarchar. On doit normalement l’utiliser pour tous les paramètres de ce type. Cela vaut pour le code de la procédure fourni en exemple.
Cache
Comme mentionné, EXEC() fait un usage très pauvre du cache puisque la chaîne SQL exacte est placée en cache. Cela veut dire qu’une recherche sur les clients ALFKI et VINET génèrere 2 plans d’exécution différents. Ainsi la réutilisabilité n’est possible que quand 2 utilisateurs font exactement la même recherche.
2 remarques pour compléter :
- Quelquefois, SQL Server ne cache pas du tout les requêtes non paramétrées donc il est possible qu’il n’y ait même pas de cache pour 2 recherches identiques.
- SQL Server peut auto-paramétrer des requêtes, mais cela n’est possible qu’avec des requêtes utlra-simples et on ne peut ainsi pas s’appuyer dessus. Sur SQL Server 2005, il y a une fonctionnalité qui permet de forcer le paramétrage automatique pour toutes les requêtes mais il vaut mieux ne pas trop en attendre car la contre-partie est sévère.
Résumé
Encore une fois, EXEC est une solution quasiment obsolète et je voudrais rappeler les avantages de la procédure sp_execute_sql :
- Pas besoin de gérer les simples guillemets (quotes)
- Risque d’injection SQL limité
- Pas de problème avec le formatage des dates et types numériques
- Pas besoin de se soucier des chaînes Unicode (en faisant toutefois attention à bien typer la liste de paramètre de sp_execute_sql).
- Les plans d’exécution sont stockés en cache
Quand le cache n’est pas une solution
Avant d’aborder le chapitre du SQL statique, nous allons discuter d’une situation problématique que vous pourriez rencontrer. Cet article a mis en valeur les vertues du cache. Si 2 utilisateurs recherchent sur les mêmes critères, ils utiliseront les mêmes plan d’exécution. Mais considérons ces 2 appels :
EXEC Commande_S_Recherche_1 @no_client = 'ERNTC',
@datedebut = '19980205', @datefin = '19980205'
EXEC Commande_S_Recherche_1 @no_client = 'BOLSR',
@datedebut = '19980101', @datefin = '19981231'
Dans la base Northgale, ERNTC est le plus gros client, avec 572 commandes tandis que BOLSR n’a qu’une unique commande. Comme vous pouvez le deviner, le meilleur plan d’exécution est différent pour chaque requête. Quand on recherche des commandes pour un client régulier dans une courte période, c’est plus efficace d’utiliser l’index sur la date de commande (OrderDate). Mais quand on recherche un utilisateur plus rare sur l’ensemble de l’échelle de temps, on préfèrera l’index sur le n° de client (CustomerId). Comment pouvons nous traiter ce point ? C’est le sujet de ce chapitre.
Avoir certaines valeurs “Inline”
Inline signifie que l’on injecte une valeur dans la chaîne SQL. Injecter toutes les valeurs dans la chaîne SQL est une mauvaise idée, comme nous avons pu le voir avec la méthode EXEC(). Cependant, cette technique peut nous aider.
Prenons comme exemple l’ajout d’un paramètre statut dans notre procédure de recherche. Supposons qu’il y ait 4 valeurs possibles dont l’une (« Terminé ») représenterait 95% des valeurs de la table. Une recherche classique serait celle des nouvelles commandes, moins de 1% de la table. Il serait donc intéressant d’inclure le paramètre @statut directement dans la chaîne SQL puisque chaque valeur différente produira un plan d’exécution différent, utilisant ou pas l’index sur ce critère.
Et si on reprend notre exemple qui combine client et intervalle de dates, injecter l’identifiant du client n’est pas judicieux car cela génèrerait un plan d’exécution par client et le grand nombre de clients réduit fortement l’intérêt. L’intervalle de dates est un meilleur candidat, en partant du principe que les mêmes intervalles sont récurrents dans la journée (ex : 7 jours glissants, la veille, etc.). Au fil du temps, cela peut générer beaucoup de plans d’exécutions… mais les plus récents remplaceront les anciens.
Changer la chaîne SQL
Comme décrit précédemment, SQL Server recherche dans le cache en hachant le texte de la requête. Cela veut dire que 2 requêtes avec des textes différents génèrent 2 entrées dans le cache, même si un seul espace les différencie et que le contenu reste logiquement identique. Il y plusieurs façons d’altérer la chaîne SQL, en voici une :
IF @datedebut IS NOT NULL AND @datefin IS NOT NULL
BEGIN
SELECT @sql = @sql +
CASE
WHEN @datedebut = @datefin THEN ''
WHEN datediff(DAY, @datedebut, @datefin) <= 7 THEN ' AND 2 = 2 '
WHEN datediff(DAY, @datedebut, @datefin) <= 30 THEN ' AND 3 = 3 '
...
END
On injecte une condition toujours vraie afin de ne pas affecter l’exécution. Cette technique a l’avantage, par rapport à l’injection des valeurs, de produire moins de plans d’exécution différents car on maîtrise les cas spéciaux, au risque de ne pas toujours avoir le plan le plus optimal. Il faut évidemment connaître ses données pour identifier les cas qui méritent le plus de tuning.
Dans cet exemple, on utilise l’ajout d’une condition toujours vraie mais la technique fonctionne aussi en ajoutant des espaces ou des commentaires (NDT : ce que je préconise en règle générale).
Mais est-ce que cette technique nous permet de gérer le fait qu’il y ait une grande différence dans le nombre de commandes par client ? Probablement pas puisqu’il faudrait calculer le nombre de commande avant de construire la requête, ce qui peut être encore moins performant.
Index Hints
Nous allons aborder cette technique car la question va poindre tôt ou tard dans vos esprits. Il y a surement des situations où l’utilisation d’un index Hint a du sens mais on oriente l’exécution sur cette situation précise, ce qui est préjudiciable pour toutes les autres. On peut évidemment ajouter le Hint en fonction des paramètres d’entrée mais la combinatoire fait qu’il est difficile d’être efficace de façon globale. Il est préférable de laisser faire l’optimiseur de SQL Server.
Pour rappel, l’utilisation des Hints n’est conseillée uniquement que quand on est face à une situation particulière pour laquelle l’optimiseur est dans le faux et qu’aucune solution de contournement n’est disponible.
OPTION (RECOMPILE)
Depuis SQL Server 2005, on peut ajouter cette option à la fin de la requête. Elle indique à SQL Server qu’il doit recompiler la requête à chaque exécution, ce qui désactive le cache.
Si vous considérez que les critères de votre recherche varient tellement que vous souhaitiez une recompilation à chaque fois tout en vous prémunissant des attaques d’injections SQL, cette option est un bon choix. Vous pouvez aussi l’ajouter conditionnellement en fonction des paramètres d’entrée.
SQL Statique
Introduction
Pour une fonctionnalité de recherche avec de nombreux paramètres comme la procédure Commande_S_Recherche, le SQL statique n’est pas un des meilleurs choix car on doit faire des choix entre performance et maintenabilité. Pour avoir toujours les meilleures performances, il faudrait utiliser l’instruction IF pour isoler les cas de recherche comme ceci :
IF @no_commande IS NOT NULL
SELECT ...
WHERE o.orderd IS NOT NULL
ELSE IF @datedebut IS NOT NULL AND @datefin IS NULL AND @no_client IS NULL ...
SELECT ...
WHERE o.OrderDate <= @datedebut
ELSE IF @datedebut IS NOT NULL AND @datefin IS NOT NULL AND @no_client IS NULL ...
Comme vous pouvez l’imaginer, ce code serait impossible à maintenir et surtout très barbant à écrire.
Cependant, sur SQL Server 7 et 2000, si vous ne pouvez pas donner les permissions de SELECT directement à vos utilisateurs, vous n’aurez pas d’autres choix. Dans les chapitres suivants, nous allons étudier différentes méthodes utilisables dans du SQL Statique. Ces méthodes ne sont pas mutuellement exclusives, et on obtient souvent le meilleur résultat en combinant 2 ou plus de ces méthodes.
Même si on est sur SQL 2005, on ne doit pas écarter les solutions statiques. Gardez à l’esprit que toutes les problématiques de recherche ne sont pas toutes aussi complexes. Si vous avez un petit nombre de critères, vous pouvez écrire une solution de recherche avec du SQL statique qui fonctionne de façon optimale et qui soit maintenable, même plus que du SQL dynamique (il faut insister sur le fait que le SQL dynamique ajoute une couche de complexité avec des risques d’erreurs de syntaxe à l’exécution). Bien souvent, la fonctionnalité de recherche « dynamique » n’est qu’un lookup sur des clés étrangères.
Comme pour le SQL dynamique, faite toujours attention de tester tous les paramètres d’entrées de votre procédure, autant pour les bugs que pour la performance. Pour rappel, pour les tests de performances, pensez à avoir une volumétrie similaire à ce que vous attendez en production.
x = @x OR @x IS NULL
C’est le point de départ des recherches conditionnelles avec le SQL statique (et ce sera aussi celui des solutions hybrides). Cette simple technique conduit à une requête compacte. Cependant, la performance pour des jeux de données conséquents ne sera pas au rendez-vous. Voici la procédure Commande_S_Recherche3 qui démontre l’idée générale :
CREATE PROCEDURE Commande_S_Recherche_3
@no_commande int = NULL,
@datedebut datetime = NULL,
@datefin datetime = NULL,
@prixmini money = NULL,
@prixmaxi money = NULL,
@no_client nchar(5) = NULL,
@nom_client nvarchar(40) = NULL,
@ville nvarchar(15) = NULL,
@region nvarchar(15) = NULL,
@pays nvarchar(15) = NULL,
@no_produit int = NULL,
@nom_produit nvarchar(40) = NULL
AS
SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
c.CustomerID, c.CompanyName, c.Address, c.City, c.Region,
c.PostalCode, c.Country, c.Phone, p.ProductID,
p.ProductName, p.UnitsInStock, p.UnitsOnOrder
FROM Orders o
JOIN [Order Details] od ON o.OrderID = od.OrderID
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN Products p ON p.ProductID = od.ProductID
WHERE (o.OrderID = @no_commande OR @no_commande IS NULL)
AND (o.OrderDate >= @datedebut OR @datedebut IS NULL)
AND (o.OrderDate <= @datefin OR @datefin IS NULL)
AND (od.UnitPrice >= @prixmini OR @prixmini IS NULL)
AND (od.UnitPrice <= @prixmaxi OR @prixmaxi IS NULL)
AND (o.CustomerID = @no_client OR @no_client IS NULL)
AND (c.CompanyName LIKE @nom_client + '%' OR @nom_client IS NULL)
AND (c.City = @ville OR @ville IS NULL)
AND (c.Region = @region OR @region IS NULL)
AND (c.Country = @pays OR @pays IS NULL)
AND (od.ProductID = @no_produit OR @no_produit IS NULL)
AND (p.ProductName LIKE @nom_produit + '%' OR @nom_produit IS NULL)
ORDER BY o.OrderID
Le but de toutes les clauses @x IS NULL est de rendre optionnels les paramètres d’entrée, de façon à ce que chaque condition AND soit toujours vraie. Ainsi, seules les conditions prises en considération sont celles qui ont un paramètre non nul.
Avec un objectif de maintenabilité, c’est un bon choix car il n’y a pas de complexité supplémentaire. Mais côté performance, la solution est catastrophique. Cette procédure n’utilisera jamais un index sélectif mais opérera comme si on n’avait passé aucun critère.
Cela peut surprendre les lecteurs qui sont accoutumés au « parameter sniffing ». Quand SQL Server construit le plan d’exécution pour une procédure, il regarde les valeurs des paramètres d’entrée et s’en sert comme guide pour le plan. Ainsi, pour cet appel :
EXEC Commande_S_Recherche_3 @no_commande = 11000
On serait en droit d’attendre à ce que le plan utilise le n° de commande pour cherche dans le clé primaire de la table des commandes. Alors pourquoi n’avons-nous pas ce comportement ? Gardez à l’esprit que l’optimiseur ne fait pas d’analyse du code à la volée. Il voit que @no_commande est égal à 11000 en entrée mais il ne peut pas exclure que ce paramètre ne passe pas à NULL pendant l’exécution de la procédure, auquel cas un plan utilisant la clé primaire des commandes engendrerait un résultat inacceptable en terme de performance.
Il y a un second problème avec le « parameter sniffing » que l’optimiseur doit traiter. Admettons que SQL Server ait construit un plan d’exécution qui prend bien en compte notre paramètre no_commande. Rappelons nous que les plans d’exécution sont mis en cache. Ainsi, la requête suivante, si elle recherche sur le no_produit par exemple aura un plan d’exécution totalement incorrect.
Forcer la recompilation à chaque fois
Puisqu’il n’y a pas de plan qui convienne à toutes les recherches, ce peut être une bonne idée que de forcer la recompilation à chaque exécution. Ceci peut être fait de 3 manières différentes :
- En ajoutant WITH RECOMPILE à la définition de la procédure
- En ajoutant WITH RECOMPILE à l’appel de la procédure
- Sur SQL 2005 en utilisant le Hint OPTION (RECOMPILE)
Essayons ce jeu d’essai :
exec Commande_S_Recherche_3 @no_commande = 11000 WITH RECOMPILE
exec Commande_S_Recherche_3 @no_client = 'ALFKI' WITH RECOMPILE
exec Commande_S_Recherche_3 @no_produit = 76 WITH RECOMPILE
exec Commande_S_Recherche_3 @no_produit = 76, @no_client = 'RATTC' WITH RECOMPILE
exec Commande_S_Recherche_3 @datedebut = '19980205', @datefin = '19980209' WITH RECOMPILE
exec Commande_S_Recherche_3 @ville = 'Bräcke', @no_produit = 76 WITH RECOMPILE
Avec SQL Server 2005, il y a bien un plan d’exécution différent à chaque invocation et le temps total d’exécution est bien plus bas que sans l’option WITH RECOMPILE. Donc la recompilation forcée est un bon compromis. Cependant, les plans ne sont pas les plus optimums et par rapport au SQL dynamique, les temps sont 4 fois moins bons.
Comme discuté plus haut, SQL Server n’analyse pas le code de la procédure donc il ne peut pas savoir si les paramètres retiendront leurs valeurs initiales jusqu’à leur utilisation. Ainsi, il n’y a pas vraiment de surprise sur ces pauvres performances. Mais quid de l’utilisation du hint OPTION (RECOMPILE) ? Ce hint, apparu avec SQL Server 2005, indique au moteur de recompiler la plan d’exécution de l’instruction à chaque fois. (Avec SQL Server 2000, la procédure est compilée dans sa globalité. Avec 2005, la recompilation peut avoir lieu au niveau d’une instruction). Cela veut dire que SQL Server doit être capable de vérifier les paramètres courants et d’aboutir à un plan aussi bon que ce qu’on aurait avec du SQL dynamique.
Cependant, cela n’arrive pas avec 2005. Quand on ajoute OPTION (RECOMPILE) dans la requête, on obtient exactement le même plan qu’avec l’option WITH RECOMPILE à l’instruction EXEC. Ce comportement a été corrigé dans SQL Server 2008, de façon à ce que l’option RECOMPILE fonctionne comme prévu, et cela change radicalement les postulats pour ce problème. C’est pourquoi il existe une version de cet article spécialement pour SQL Server 2008 (en anglais). Il est plus court car de nombreuses astuces n’ont pas d’intérêt avec SQL 2008. Mais cet article contient un chapitre où sont discutés des choix entre SQL statique et dynamique avec OPTION (RECOMPILE) en détail.
Ceci ne conclut en rien sur l’inutilité de OPTION (RECOMPILE). Si vous avez une longue procédure et que vous ayez une condition multicritère au milieu, OPTION (RECOMPILE) peut être utile. Aussi, si cette instruction inclut des variables locales ou si les paramètres d’entrée changent de valeur, SQL Server est capable d’utiliser ces valeurs pour construire le plan final.
Evidemment, si on force la recompilation à chaque appel, il n’y a pas de réutilisation du cache, en fait, le plan n’est même pas mis en cache. Mais c’est un compromis qu’il faudra faire. Le cache est une bonne chose car il permet de décharger le serveur mais il n’est pas bon s’il conduit à des requêtes avec des mauvais plans d’exécution.
Une méthode inutile ?
Donc, si cette méthode conduit toujours à un scan de la table, ne faudrait-il pas ne jamais l’utiliser ? Quelquefois, on sait que le volume de données dans lequel on recherche sera raisonnable et ne dépassera pas, disons, 200 lignes. Tant que les temps de réponse sont corrects pour les utilisateurs et qu’il n’y a pas de problème de verrous, il n’y a pas de raison de complexifier le code. Et il y a plein de cas où on doit ajouter un nouveau critère optionnel sur une colonne non indexée dans une requête qui fonctionne très bien ; dans ces cas, cette méthode est un excellent choix.
Dans la section x = @x AND @x IS NOT NULL, nous reviendrons sur un truc pour rendre l’utilisation des index effective dans certaines circonstances.
Variations sur ce thème
Plutôt que d’utiliser l’opérateur OR comme dans le code de la procédure 3, on voit souvent écrit :
o.orderID = coalesce(@no_commande, o.OrderID)
coalesce() est une fonction qui prend une liste de valeurs en paramètres et renvoie le premier paramètre non nul. Ainsi, dans cet exemple, si le paramètre @no_commande est nul, on obtient o.OrderID = o.OrderID qui est toujours vrai.
Cela conduit à un code plus compact qu’en utilisant OR mais cela ne résout rien concernant les performances : SQL Server n’est pas en mesure d’utiliser efficacement les index. Au contraire, ce genre de condition rend la tâche plus difficile à l’optimiseur, même avec un Hint de recompilation. Les tests montrent d’ailleurs une dégradation des performances sur SQL 2005(Commande_S_Recherche_3a).
Il faut toutefois être vigilant car il y a un piège avec coalesce dans lequel vous pouvez tomber. Essayons ceci :
EXEC Commande_S_Recherche_3 @no_commande = 10654
EXEC Commande_S_Recherche_3a @no_commande = 10654
Le premier appel retourne 3 enregistrements mais le second ne renvoie rien du tout. Tout simplement parce que certaines colonnes (Region en particulier) contient des valeurs nulles. Quand @region est nul, la condition
c.Region = coalesce(@region, c.Region)
devient c.Region = NULL. Mais en SQL, NULL n’est jamais égal à NULL. NULL signifie « absence de valeur » et savoir si 2 valeurs inconnues sont égales ou différentes est impossible à déterminer.
En fait, pour corriger Commande_S_Recherche_3a, nous aurions du écrire la condition de la région différemment car le problème était prévisible. Etant donné que coalesce n’apporte pas d’avantage particulier, on peut dire que
x = @x OR @x IS NULL
est à préférer à la solution coalesce. Mais libre à vous d’expérimenter cette solution, de la mixer, voire d’augmenter les options avec la fonction ISNULL.
Utilisation des conditions IF
Utiliser des conditions pour chaque combinaison n’est pas possible mais il est toujours possible d’utiliser une condition pour isoler des cas importants de recherche, où les colonnes sont correctement indexées et pour lesquels les utilisateurs attendent une réponse rapide. Une condition comme c.Country = @pays OR @pays IS NULL est en fait inutile puisqu’il n’y a aucun d’index sur Customers.Country.
Pour démontrer cette technique, vous trouverez la procédure Commande_S_Recherche_4. Dans la version simplifiée ci-dessous, on peut voir 3 grandes parties. La première quand @no_commande est fourni, la deuxième quand @no_client est fourni et enfin, par défaut, la méthode multicritère présentée plus haut.
IF @no_commande IS NOT NULL
BEGIN
SELECT ...
WHERE o.OrderID = @no_commande
AND od.OrderID = @no_commande
AND (od.UnitPrice >= @prixmini OR @prixmini IS NULL)
AND (od.UnitPrice <= @prixmaxi OR @prixmaxi IS NULL)
AND (od.ProductID = @no_produit OR @no_produit IS NULL)
AND (p.ProductName LIKE @nom_produit + '%' OR @nom_produit IS NULL)
ORDER BY o.OrderID
END
ELSE IF @no_client IS NOT NULL
BEGIN
SELECT ...
WHERE (o.OrderDate >= @datedebut OR @datedebut IS NULL)
AND (o.OrderDate <= @datefin OR @datefin IS NULL)
AND (od.UnitPrice >= @prixmini OR @prixmini IS NULL)
AND (od.UnitPrice <= @prixmaxi OR @prixmaxi IS NULL)
AND c.CustomerID = @no_client
AND o.CustomerID = @no_client
AND (od.ProductID = @no_produit OR @no_produit IS NULL)
AND (p.ProductName LIKE @nom_produit + '%' OR @nom_produit IS NULL)
ORDER BY o.OrderID
END
ELSE
BEGIN
SELECT ...
WHERE (o.OrderDate >= @datedebut OR @datedebut IS NULL)
AND (o.OrderDate <= @datefin OR @datefin IS NULL)
AND (od.UnitPrice >= @prixmini OR @prixmini IS NULL)
AND (od.UnitPrice <= @prixmaxi OR @prixmaxi IS NULL)
AND (c.CompanyName LIKE @nom_client + '%' OR @nom_client IS NULL)
AND (c.City = @ville OR @ville IS NULL)
AND (c.Region = @region OR @region IS NULL)
AND (c.Country = @pays OR @pays IS NULL)
AND (od.ProductID = @no_produit OR @no_produit IS NULL)
AND (p.ProductName LIKE @nom_produit + '%' OR @nom_produit IS NULL)
ORDER BY o.OrderID
END
Comme vous pouvez le voir, certaines conditions redondantes ont été retirées si un numéro de commande ou de client est fourni.
Dans cet exemple, la préférence est donnée aux critères @no_commande et @no_client car ils représentent les recherche les plus importantes. Les utilisateurs attendent une réponse immédiate quand ils fournissent directement un numéro de commande. Idem pour un numéro de client. D’un autre coté, une recherche uniquement sur le prix n’est pas fréquente.
Aurait-on pu créer des cas pour toutes les colonnes indexées ? Non, car la base Northwind est déjà bien indexée. Dans notre étude de cas, les seules colonnes non-indexées sont le pays et le prix unitaire. Comme vous pouvez l’imaginer, la complexité des instructions IF peut grandir rapidement au fur et à mesure que l’on ajoute des conditions. Par exemple, si on veut ajouter une branche pour gérer la date de commande, nous obtiendrons ce code :
ELSE IF @no_client IS NOT NULL AND (@datedebut IS NOT NULL OR @datefin IS NOT NULL)
...
ELSE IF (@datedebut IS NOT NULL OR @datefin IS NOT NULL)
...
ELSE IF @no_client IS NOT NULL
C’est à dire que nous avons besoin de 2 branches supplémentaires. Sans la première condition, un utilisateur qui recherche un client avec peu de commandes mais aussi qui donnerait des bornes sur les dates tomberait dans la mauvaise condition. En effet, il faut jouer avec la combinatoire des paramètres et leur priorisation. Avec cette première condition, l’optimiseur utilisera les bons critères pour faire son choix (sous-réserve qu’il y ait un WITH RECOMPILE quelque part).
Le problème avec Commande_S_Recherche_4 est que, si au premier appel vous passez un @no_commande puis au second un @no_client, le plan mis en cache pour la seconde branche se base sur la valeur nulle passée au premier appel ce qui ne permet pas un optimisation de ce dernier.
On peut contourner cela en forçant la recompilation mais s’il y a beaucoup de branches, chaque branche sera recompilée à chaque fois alors qu’une seule suffit. Avec SQL Server 2005, on peut adresser ce souci en ajoutant OPTION (RECOMPILE) après chaque SELECT de branche, de façon à ce que seul cette instruction soit recompilée.
Une autre solution est de découper les différents SELECT en sous-procédures. Vous pouvez voir cette solution dans la procédure Commande_S_Recherche_4a, résumée ci-dessous:
IF @no_commande IS NOT NULL
BEGIN
EXEC Commande_S_Recherche_4a_sub1 @no_commande = @no_commande, ...
END
ELSE IF @no_client IS NOT NULL
BEGIN
EXEC Commande_S_Recherche_4a_sub2 @datedebut = @datedebut, ...
@no_client = @no_client, ...
END
ELSE
BEGIN
EXEC Commande_S_Recherche_4a_sub3 @datedebut = @datedebut, ...
END
Chaque sous-procédure a son propre plan d’exécution mis en cache, et pour chaque, le plan est basé sur des valeurs d’entrée cohérentes. La procédure par défaut (catch-all) Commande_S_Recherche_4a_sub3, qui correspond finalement à Commande_S_Recherche_4 a toujours besoin d’un WITH RECOMPILE puisqu’elle gère un mix de nombreuses conditions.
En fonction du point de vue, on peut trouver cette façon de faire plus ou moins maintenable. En effet, on augmente le nombre de procédures à maintenir mais on simplifie grandement leur logique.
Les astuces d’Umachandar
Voici quelques astuces qui viennent d’un ancien MVP SQL Server, Umachandar Jaychandran, qui travaille maintenant dans l’équipe SQL Server.
Utilisation de BETWEEN
Plutôt que d’utiliser
o.OrderID = @no_commande OR @no_commande IS NULL
On peut écrire
o.OrderID BETWEEN coalesce(@no_commande, @MinInt) AND
coalesce(@no_commande, @MaxInt)
Ici, @MinInt et @MaxInt correspondent aux bornes du type entier mais peuvent correspondre aux limites possibles de vos valeurs (ex : @MinInt = 0). Le gain est que l’optimiseur peut utiliser l’index sur OrderId parceque même si @no_commande est nul, le plan d’exécution reste correct.
Notez toutefois que l’astuce ne fonctionne pas avec les colonnes nulles. De la même façon qu’avec l’opérateur égal expliqué plus haut, l’opérateur BETWEEN ne permet pas la comparaison en 2 valeurs nulles.
Comment obtenir les valeurs min et max ?
Comment peut-on obtenir les valeurs pour les variables @Min et @Max ? Pour certains types de données, vous pouvez évidemment les coder en dur. Cela fonctionne pour les types entiers, binaires, dates, et avec certaines précautions pour les types flottants, décimaux et monétaires.
Toutefois, pour des données textuelles, on ne peut pas décemment s’appuyer sur des valeurs en dur, car elles sont dépendantes des collations. Une des méthodes est donc de récupérer les valeurs directement dans la table en utilisant les agrégats MIN et MAX. Mais, surtout, à ne faire que sur des colonnes indexées !
On peut même zapper les variables intermédiaires et écrire directement les requêtes calculant les bornes dans la requête principale :
o.CustomerID BETWEEN
isnull(@no_client, (SELECT MIN(CustomerID) FROM Customers)) AND
isnull(@no_client, (SELECT MAX(CustomerID) FROM Customers))
isnull est l’équivalent de coalesce sauf qu’il n’accepte que 2 paramètres. Alors pourquoi utiliser isnull ? Umachandar a découvert que l’utilisation de coalesce entraine des scans de table alors que le phénomène ne se produit pas avec isnull. Cela vient du comportement de l’optimiseur de requête. Pour être pragmatique, il est recommandé d’évaluer les 2 solutions si vous avez à l’utiliser.
Utilisation de LIKE
Comme alternative, Umachandar suggère d’utiliser l’opérateur LIKE pour les données texte :
c.City LIKE coalesce(@ville, '%')
Encore une fois, c’est une astuce qui ne fonctionne qu’avec des colonnes non nulles. (City est en fait nulle dans Northwind mais aucune valeur nulle ne se trouve dans la table). Aussi, soyez vigilant avec les types char/nchar.
c.Customer LIKE coalesce(@no_client, '%')
Dans cet exemple, @no_client est de type nchar(5), le signe pourcent est donc interprété en 5 espaces et aucun client ne sera remonté.
Un autre problème potentiel est si vous permettez à l’utilisateur de saisir des valeurs comme ‘%york’, ce qui peut être une bonne ou une mauvaise chose. Sachez que l’index sur la ville n’aidera pas ce type de recherche.
Commande_S_Recherche_5
En utilisant les astuces d’Umachandar, on obtient la procédure Commande_S_Recherche_5, qui se résume ainsi.
SELECT
@minint = convert(int, 0x80000000),
@maxint = convert(int, 0x7FFFFFFF)
IF @no_client IS NULL
SELECT @mincustid = MIN(CustomerID), @maxcustid = MAX(CustomerID)
FROM Customers
SELECT ...
WHERE
o.OrderID BETWEEN
coalesce(@no_commande, @minint) AND
coalesce(@no_commande, @maxint)
AND od.OrderID BETWEEN
coalesce(@no_commande, @minint) AND
coalesce(@no_commande, @maxint)
AND o.OrderDate >= coalesce(@datedebut, '17530101')
AND o.OrderDate <= coalesce(@datefin, '99991231')
AND od.UnitPrice >= coalesce(@prixmini, 0)
AND od.UnitPrice <= coalesce(@prixmaxi, 1E18)
AND o.CustomerID BETWEEN
coalesce(@no_client, @mincustid) AND
coalesce(@no_client, @maxcustid)
AND c.CustomerID BETWEEN
coalesce(@no_client, @mincustid) AND
coalesce(@no_client, @maxcustid)
AND c.CompanyName LIKE coalesce(@nom_client + '%', '%')
AND c.City LIKE coalesce(@ville, '%')
AND (c.Region = @region OR @region IS NULL)
AND (c.Country = @pays OR @pays IS NULL)
AND od.ProductID BETWEEN
coalesce(@no_produit, @minint) AND
coalesce(@no_produit, @maxint)
AND p.ProductID BETWEEN
coalesce(@no_produit, @minint) AND
coalesce(@no_produit, @maxint)
AND p.ProductName LIKE coalesce(@nom_produit + '%', '%')
ORDER BY o.OrderID
Pour fixer les bornes des valeurs entières, on utilise une chaîne binaire, plus facile à retenir que les nombres actuels. Pour optimiser un peu, on ne lit les valeurs min et max de la colonne no_client que quand ce dernier est nul.
Comme vous pouvez le voir, on n’utilise pas BETWEEN et LIKE pour toutes les colonnes. Comme indiqué précédemment, les astuces ne fonctionnent pas avec la colonne Region qui peut être nulle, ni avec la colonne Pays qui n’est pas indexée.
Pour les filtres OrderDate et UnitPrice, il n’y a pas besoin d’utiliser l’astuce BETWEEN car nous filtrions déjà sur un intervalle. Les valeurs utilisées comme second paramètre de coalesce sont les dates les plus petites et les plus grandes supportées par le type datetime. Pour le type monétaire, on suppose des valeurs minimum et maximum raisonnables.
Est-ce que ça fonctionne bien ?
Les résultats sont très mitigés. Le jeu de test suivant est testé sur la base Northgale :
EXEC Commande_S_Recherche_5 @no_commande = 11000 WITH RECOMPILE
EXEC Commande_S_Recherche_5 @no_client = 'ALFKI' WITH RECOMPILE
EXEC Commande_S_Recherche_5 @no_produit = 76 WITH RECOMPILE
EXEC Commande_S_Recherche_5 @no_produit = 76, @no_client = 'RATTC' WITH RECOMPILE
EXEC Commande_S_Recherche_5 @datedebut = '19980205', @datefin = '19980209' WITH RECOMPILE
EXEC Commande_S_Recherche_5 @ville = 'Bräcke', @no_produit = 76 WITH RECOMPILE
La recherche sur le numéro client seul ou accompagnant le numéro produit donne une réponse instantanée, beaucoup mieux que Commande_S_Recherche_3. Il en est de même pour une recherche sur l’identifiant de produit.
Ce qui est perturbant, c’est que malgré l’utilisation de WITH RECOMPILE, le plan d’exécution est toujours le même. Ce qu’on cherche, c’est d’avoir un plan d’exécution qui dépende des paramètres d’entrées. Doit-on en conclure que les astuces d’Umachandar ne sont pas bonnes ? Ce serait une conclusion un peu hâtive, elles peuvent être utile dans un autre contexte. Peut-être a-t-on abusé de ces astuces pour Commande_S_Recherche_5 en les utilisant pour toutes les conditions. Nous verrons quand nous aborderons Commande_S_Recherche_7 un cas ou l’opérateur BETWEEN nous aidera à obtenir un meilleur plan.
En tout cas, il n’est pas inutile de répéter qu’il est primordial de tester et d’évaluer les différentes méthodes que l’on a à sa disposition avec des données représentatives. Et n’oublions pas que souvent la bonne réponse passe par une combinaison de techniques.
Utilisation des tables temporaires
Quelquefois, il est possible de simplifier le code d’une procédure en utilisant une table temporaire (ou plus d’une). Nous allons voir ici 2 possibilités ; la première est pitoyable en termes de performance tandis que la seconde nous donnera une procédure Commande_S_Recherche qui se défend bien.
Le cas à oublier
Cette façon de faire est très simpliste et peut être utile pour des petits jeux de données mais elle ne conviendra jamais pour notre étude de cas. Eventuellement, pour des conditions très complexes à calculer (beaucoup de jointures), elle peut se défendre.
L’idée générale est de faire une première sélection dans une table temporaire et ensuite de supprimer les enregistrements qui ne conviennent pas aux autres conditions. Voici un extrait de Commande_S_Recherche_6 qui utilise cette technique :
SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
c.CustomerID, c.CompanyName, c.Address, c.City, c.Region,
c.PostalCode, c.Country, c.Phone, p.ProductID,
p.ProductName, p.UnitsInStock, p.UnitsOnOrder
INTO #temp
FROM Orders o
JOIN [Order Details] od ON o.OrderID = od.OrderID
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN Products p ON p.ProductID = od.ProductID
IF @no_commande IS NOT NULL
DELETE #temp WHERE OrderID <> @no_commande
IF @datedebut IS NOT NULL
DELETE #temp WHERE OrderDate < @datedebut
...
IF @region IS NOT NULL
DELETE #temp WHERE Region <> @region OR Region IS NULL
...
SELECT * FROM #temp ORDER BY OrderID
Le code est très simple à écrire et à comprendre. La seule difficulté est encore une fois les colonnes nulles comme Region.
Si vous décidez de partir sur cette méthode, il est essentiel de faire en sorte que la première sélection soit d’une taille raisonnable. Cette procédure est viable dans la base Northwind mais pas dans la base Northgale où le premier SELECT peut générer plus de 2 millions de lignes dans la table temporaire.
Il y a un second problème de performance à considérer, ce sont les statistiques qui peuvent entraîner des recompilations. Un moyen de ne pas avoir à s’en soucier est d’utiliser une variable de type Table qui n’a pas de statistiques. Mais attention, dans certaines conditions, comme un DELETE qui s’appuie sur une autre table, les statistiques sur la table temporaire peuvent être un avantage.
Dans cet exemple, nous avons mis toutes les colonnes dans la table temporaire. Il aurait été suffisant de prendre OrderID, OrderDate and UnitPrice, CustomerID and ProductID, qui sont les colonnes de recherche des plus grosses table Orders and Order Details. Ensuite, pour les suppressions sur les autres critères, nous aurions pu faire des jointures. L’avantage est sans conteste le gain sur la taille de la table temporaire même s’il ne permet toujours pas une utilisation avec les volumes de la base Northgale.
Filtres inversés
Cet exemple utilise un mix de stratégies. Sans dire que cette solution est du bricolage, elle s’appuie surtout sur votre imagination et votre bonne connaissance des données plutôt que sur une technique claire et formelle.
Voici comment commence Commande_S_Recherche_7 :
IF @no_commande IS NULL
BEGIN
INSERT #custids (custid)
SELECT CustomerID
FROM Customers c
WHERE (c.CustomerID = @no_client OR @no_client IS NULL)
AND (c.CompanyName LIKE @nom_client + '%' OR @nom_client IS NULL)
AND (c.City = @ville OR @ville IS NULL)
AND (c.Region = @region OR @region IS NULL)
AND (c.Country = @pays OR @pays IS NULL)
END
INSERT #products(ProductID, ProductName, UnitsInStock, UnitsOnOrder)
SELECT ProductID, ProductName, UnitsInStock, UnitsOnOrder
FROM Products
WHERE (ProductID = @no_produit OR @no_produit IS NULL)
AND ProductName LIKE coalesce(@nom_produit + '%', '%')
On remplit 2 tables temporaires, une pour les clients, une pour les produits. Celle contenant les clients ne contient que leur identifiant alors que la table des produits contient tous les champs nécessaires à la recherche. Pour couper court, nous n’avons pas essayé d’utiliser les index existants. Tout dépendra de vos données mais ici, les tables contenant les clients et les produits sont plus légères que celles contenant les commandes.
Comme vous pouvez le voir, on ne cherche dans les clients que si l’utilisateur n’a pas fourni de numéro de commande. Ce même test n’est pas applicable aux produits.
La suite de la procédure est la suivante :
EXEC Commande_S_Recherche_7_sub @no_commande, @datedebut, @datefin, @prixmini, @prixmaxi
Cette sous-procédure utilise l’option WITH RECOMPILE et ressemble à ceci :
IF @no_commande IS NULL
BEGIN
SELECT ...
FROM Orders o
JOIN [Order Details] od ON o.OrderID = od.OrderID
JOIN #custids t ON o.CustomerID = t.custid
JOIN Customers c ON o.CustomerID = c.CustomerID
AND t.custid = c.CustomerID
JOIN #products p ON p.ProductID = od.ProductID
WHERE o.OrderDate >= coalesce(@datedebut, '17530101')
AND o.OrderDate <= coalesce(@datefin, '99991231')
AND od.UnitPrice >= coalesce(@prixmini, 0)
AND od.UnitPrice <= coalesce(@prixmaxi, 1E18)
ORDER BY o.OrderID
END
ELSE
BEGIN
SELECT ...
FROM Orders o
JOIN [Order Details] od ON o.OrderID = od.OrderID
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN #products p ON p.ProductID = od.ProductID
WHERE o.OrderID = @no_commande
AND od.OrderID = @no_commande
AND od.UnitPrice >= coalesce(@prixmini, 0)
AND od.UnitPrice <= coalesce(@prixmaxi, 1E18)
END
Comme dans Commande_S_Recherche_4, on utilise des conditions IF pour différencier les cas. Le premier fait une jointure sur la table temporaire des clients et pas le second. Dans tous les cas on fait une jointure sur la table temporaire des produits mais jamais sur la table originale Products.
Mais pourquoi utiliser une sous-procédure ? C’est pour garantir qu’il y ait une recompilation pour la requête finale puisque le meilleur plan va dépendre du contenu des tables temporaires. Comme pour Commande_S_Recherche_4a, nous aurions pu avoir une sous-procédure pour chaque cas, afin de diviser le temps de recompilation ou bien utiliser OPTION (RECOMPILE) sur chaque SELECT avec SQL Server 2005.
Testons l’exécution de cette technique sur le jeu d’essai Northgale
EXEC Commande_S_Recherche_7 @no_commande = 11000
EXEC Commande_S_Recherche_7 @no_client = 'ALFKI'
EXEC Commande_S_Recherche_7 @no_produit = 76
EXEC Commande_S_Recherche_7 @no_produit = 76, @no_client = 'RATTC'
EXEC Commande_S_Recherche_7 @datedebut = '19980205', @datefin = '19980209'
EXEC Commande_S_Recherche_7 @ville = 'Bräcke', @no_produit = 76
Comparé à Commande_S_Recherche_3, le résultat est meilleur dans tous les cas sous SQL 2000 (et même significativement meilleur dans certains cas). Sur SQL 2005, les recherches sur un numéro de commande ou des dates est 50 à 70% plus lent alors qu’une recherche sur la ville ou le produit est 3 fois plus rapide.Quelque soit la version, chaque appel génère bien un plan d’exécution différent.
Une observation intéressante concerne la condition sur le prix unitaire. Comme vous pouvez le voir, l’astuce min/max d’Umachandar a été utilisée de cette façon :
AND (od.UnitPrice >= @prixmini OR @prixmini IS NULL)
AND (od.UnitPrice <= @prixmaxi OR @prixmaxi IS NULL)
Le plan d’exécution de la 5ème recherche (@datedebut = '19980205', @datefin = '19980209') est différent sur SQL 2000 et beaucoup plus lent et il n’y a pas de raison apparente. Cela souligne bien qu’il faut toujours tester différentes méthodes pour obtenir le meilleur de l’optimiseur de requête en SQL statique
x = @x AND @x IS NOT NULL
Recherche sur clés étrangères
Toutes les fonctionnalités de recherche ne sont pas aussi complexes que Commande_S_Recherche. Souvent, la recherche multicritère se restreint aux clés étrangères d’une table, et l’utilisateur n’en fournit qu’une à la fois. Dans ce cas, un simple branchement conditionnel est le plus efficace. Mais vous avez peut-être dans la liste des colonnes à retourner des expressions complexes que vous n’avez pas envie de répéter dans chaque branche pour des raisons de maintenance.
Nous savons que cette façon d’écrire une condition n’est pas bonne car elle conduit à un scan de la table :
WHERE (key1 = @key1 OR @key1 IS NULL)
AND (key2 = @key2 OR @key2 IS NULL)
AND (key3 = @key3 OR @key3 IS NULL)
Mais que dire de celle-ci :
WHERE (key1 = @key1 AND @key1 IS NOT NULL)
OR (key2 = @key2 AND @key2 IS NOT NULL)
OR (key3 = @key3 AND @key3 IS NOT NULL)
Nous avons juste inversé la logique; on recherche tous les enregistrements qui correspondent à l’une des conditions. D’un point de vue performance, cela ressemble étrangement mais cela peut en fait mieux fonctionner. SQL Server va en fait rechercher efficacement sur les 3 clés (indexées) et ensuite faire une union des résultats. Et la bonne nouvelle est que, grâce à la condition @x IS NOT NULL, SQL Server fait le test avant d’accéder à l’index.
Application
Mais cette méthode ne fonctionne que si l’utilisateur veut les enregistrements qui correspondent à n’importe laquelle des conditions. Or, la majorité des fonctionnalités de recherche comme notre Commande_S_Recherche cherchent une intersection des critères (AND). Mais pour améliorer les performances de notre recherche, nous pouvons nous baser sur la technique ci-dessus sous certaines conditions :
- L’utilisateur doit au moins fournir une condition
- Toutes les colonnes de recherche sont dans la même table
- Toutes les colonnes de recherche sont indexées
Cela nous conduit à la procédure Commande_S_Recherche_10 qui, tenant compte de ces restrictions à une interface différentes de ces consœurs.
CREATE PROCEDURE Commande_S_Recherche_10 @no_commande int = NULL,
@no_client nchar(5) = NULL,
@datedebut datetime = NULL,
@datefin datetime = NULL,
@empid smallint = NULL,
@shipdate datetime = NULL
AS
IF @datedebut IS NOT NULL AND @datefin IS NULL
SELECT @datefin = MAX(OrderDate) FROM Orders
ELSE IF @datedebut IS NULL AND @datefin IS NULL
SELECT @datedebut = MIN(OrderDate) FROM Orders
SELECT o.OrderID, o.OrderDate, o.EmployeeID, o.ShippedDate,
od.UnitPrice, od.Quantity,
c.CustomerID, c.CompanyName, c.Address, c.City, c.Region,
c.PostalCode, c.Country, c.Phone, p.ProductID,
p.ProductName, p.UnitsInStock, p.UnitsOnOrder
FROM (SELECT TOP 99.9999999 PERCENT
o.OrderID, o.OrderDate, o.EmployeeID, o.ShippedDate,
o.CustomerID
FROM Orders o
WHERE (o.OrderID = @no_commande AND @no_commande IS NOT NULL)
OR (o.CustomerID = @no_client AND @no_client IS NOT NULL)
OR (o.EmployeeID = @empid AND @empid IS NOT NULL)
OR (o.ShippedDate = @shipdate AND @shipdate IS NOT NULL)
OR (o.OrderDate BETWEEN @datedebut AND @datefin AND
@datedebut IS NOT NULL AND @datefin IS NOT NULL)
) AS o
JOIN [Order Details] od ON o.OrderID = od.OrderID
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN Products p ON p.ProductID = od.ProductID
WHERE (o.OrderID = @no_commande OR @no_commande IS NULL)
AND (o.CustomerID = @no_client OR @no_client IS NULL)
AND (o.EmployeeID = @empid OR @empid IS NULL)
AND (o.OrderDate >= @datedebut OR @datedebut IS NULL)
AND (o.OrderDate <= @datefin OR @datefin IS NULL)
ORDER BY o.OrderID, od.ProductID
Commande_S_Recherche_10 fonctionne en utilisant une table dérivée dans laquelle on construit l’union des conditions de recherche. Ensuite, on filtre de façon classique cette table dérivée. Cet arrangement complexe permet à SQL Server de s’appuyer sur les index pour calculer la table dérivée.
Revenons un peu sur la notion de table dérivée. C’est fonctionnellement comme une table virtuelle qui s’emploie (et se déclare) dans la clause FROM d’une requête. A l’exécution, le moteur de SQL Server va remettre à plat (ou pas) cette formalisation dans son plan d’exécution. On peut comparer cela à une table temporaire même si cette « table » n’est jamais matérialisée.
Ici on veut que cette table dérivée soit bien traitée en premier lieu à l’exécution c’est pourquoi il y a l’utilisation du TOP 99.9999999 PERCENT pour forcer un pré-calcul. Sinon, il y a de fortes chances pour que l’optimiseur de requête normalise le tout pour aboutir à un plan d’exécution classique et donc non performant.
Si on exécute par exemple ceci :
exec Commande_S_Recherche_10 @no_client = 'ALFKI'
En regardant le plan d’exécution, on voit que tous les index apparaissent mais en regardant sur l’opérateur Filter, on voit l’expression qui désactive l’appel à l’index si le paramètre n’est pas présent.
Côté performance, on obtient des temps comparables à ceux du SQL dynamique pour les 3 cas de tests que Commande_S_Recherche_10 supporte.
Et puisque le même plan d’exécution est correct pour tous les cas de recherche, on ne devrait plus avoir besoin de l’option WITH RECOMPILE. Mais en exécutant ceci en forçant la recompilation :
exec Commande_S_Recherche_10 @no_client = 'ALFKI', @orderdate = '19970825', @empid = 501
Nous obtenons un nouveau plan d’exécution qui utilise du parallélisme. Evidemment, plus il y a de conditions, plus la table dérivée contiendra de lignes et dans cet exemple, l’optimiseur juge qu’un simple scan sera plus performant. Ceci peut être très problématique si c’est ce plan qui est retenu à la première compilation.
Ce fait nous conduit à considérer l’utilisation de WITH RECOMPILE. Mais encore une fois, à vous de juger en fonction de vos données et de vos utilisateurs. Le risque est peut-être à prendre.
Pourquoi des restrictions?
Mais pourquoi avoir mis des restrictions dans cette méthode ? Premièrement, il faut au minimum une condition car sinon, la table dérivée ne retournera pas de ligne. Les autres restrictions sont basées sur la performance. Si toutes les colonnes ne sont pas indexées, SQL Server ne conditionnera pas les critères et conduira à un scan et un seul scan obligera l’optimiseur à reconsidérer les autres index en scans.
Enfin, si les conditions pointent sur des tables différentes, l’optimiseur ne cherchera pas à faire l’union des recherches dans les différents index à cause du lookup supplémentaire dans la table principale.
Recherche sur les intervalles
Vous aurez noté dans le code ci-dessus qu’il y a un traitement spécial pour les paramètres @datedebut et @datefin. Si un seul des deux est fourni, l’autre est fixé à une borne existante pour faire un intervalle fermé. Nous sommes obligés car une condition comme celle présentée ci-après remonterait trop d’enregistrement dans la table dérivée ce qui conduirait à un scan :
@orderdate >= @datedebut AND @datedebut IS NOT NULL
Gérer des conditions complexes
Dans l’exemple utilisé dans cet article, nous accédons à toutes les tables dans tous les cas. Dans la réalité, vous n’avez besoin d’accéder à certaines tables que si le bon paramètre d’entrée est fourni voire en fonction choisir des tables différentes en fonction du paramètre.
Avec du SQL dynamique, ces conditions se gère très facilement ; quand on construit la requête, on peut inclure les jointures nécessaires.
Certaines techniques que nous avons vu peuvent être étendue pour traiter ces cas. En particulier la combinaison des IF et des tables temporaires mais nous allons voir une autre astuce qui vient de Philipp Sumi.
Sélection basée sur l’existence dans une autre table
Ajoutons un nouveau paramètre d’entrée à notre interface : @suppl_pays. Si ce paramètre est fourni, la procédure doit filtrer les produits venant des fournisseurs du pays donné. Par un chemin classique on ajouterai une nouvelle jointure sur la table Suppliers mais Philipp Sumi nous suggère une autre solution, basé sur l’instruction EXISTS :
@suppl_country IS NULL OR EXISTS (SELECT *
FROM Suppliers s
WHERE s.SupplierID = p.SupplierID
AND s.Country = @suppl_country)
L’astuce est que si @suppl_country est nul, SQL Server ne cherchera pas à accéder à la table Suppliers. Pour illustrer ceci, écrivons Commande_S_Recherche_9 qui est juste une extension de Commande_S_Recherche_3. Et pour voir le comportement de la base :
SET STATISTICS IO ON
go
EXEC Commande_S_Recherche_9 @pays = 'Sweden'
EXEC Commande_S_Recherche_9 @suppl_country = 'Sweden'
Les plans d’exécution semblent identiques mais si on regarde le retour des statistiques on voit les résultats suivants :
Table 'Suppliers'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'Products'. ...
...
Table 'Suppliers'. Scan count 2075, logical reads 4150, physical reads 0, read-ahead reads 0.
Table 'Products'. ...
On voit bien que la table Suppliers n’est pas accédée dans le premier cas car la jointure, de type Nested Loops, n’est pas « activée » grâce à la condition. Mais encore une fois, n’hésitez pas à tester et être critique sur les techniques que vous utilisez car l’optimiseur peut, en fonction de votre volumétrie, de vos index, une autre façon de traiter l’opérateur EXISTS.
Solutions hybrides
Dans ce chapitre, nous allons voir des solutions qui utilisent à la fois du SQL statique et dynamique. L’idée générale est de mettre toutes les références aux tables dans une vue ou une fonction et ensuite d’appeler celle-ci via du SQL dynamique. Ainsi, les utilisateurs n’ont pas besoin des droits en SELECT sur les tables.
Des 2 possibilités, la fonction est sans doute le meilleur choix mais abordons d’abord les vues.
Utilisation des vues
Sur une suggestion du MVP Eric Garza, l’idée est de définir une vue qui expose les données auxquelles l’utilisateur est autorisée et de donner ensuite accès à la vue.
On crée donc la vue Commande_Recherche_Vue:
CREATE VIEW Commande_Recherche_Vue
AS
SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
c.CustomerID, c.CompanyName, c.Address, c.City, c.Region,
c.PostalCode, c.Country, c.Phone, p.ProductID,
p.ProductName, p.UnitsInStock, p.UnitsOnOrder
FROM Orders o
JOIN [Order Details] od ON o.OrderID = od.OrderID
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN Products p ON p.ProductID = od.ProductID
Puis on crée la procédure Commande_S_Recherche_8, très similaire à la première version Commande_S_Recherche_1 mais les lignes 18-28 deviennent :
SELECT @sql = 'SELECT * FROM dbo.search_order_view WHERE 1 = 1 '
Les performances ne changent pas car SQL Server ne garde pas de plan d’exécution pour la vue. SQL Server aplatit la vue avec le reste de la requête à l’exécution et donc on obtient quelque chose approchant Commande_S_Recherche_1.
Plutôt que d’utiliser une vue, nous aurions pu utiliser une fonction de table. Nous aurions eu le même comportement d’aplatissement à l’exécution mais on peut passer des paramètres. On peut dire que les fonctions de table sont des vues paramétrées.
Dans notre argumentaire sur les vues, nous avons dit que l’on peut retirer la permission de SELECT sur les tables sources. Mais les utilisateurs ont toujours accès à toutes les données via la vue, ce qui limite l’intérêt initial. Cependant, imaginons que l’on doive restreindre l’accès de l’utilisateur à certains comptes clients et que ces accès soient stockés dans une table AccesUtilisateur :
CREATE TABLE AccesUtilisateur (
UserName sysname NOT NULL,
CustomerID nchar(10) NOT NULL,
CONSTRAINT pk_uca PRIMARY KEY (UserName, CustomerID))
Le champ UserName est simplement le même utilisateur que dans la table système sys.users. On peut donc écrire la requête de la vue ainsi
CREATE VIEW search_order_view
AS
SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
c.CustomerID, c.CompanyName, c.Address, c.City, c.Region,
c.PostalCode, c.Country, c.Phone, p.ProductID,
p.ProductName, p.UnitsInStock, p.UnitsOnOrder
FROM Orders o
JOIN [Order Details] od ON o.OrderID = od.OrderID
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN UsersAccessCustomers u ON u.CustomerID = c.CustomerID
AND u.CustomerID = o.CustomerID
JOIN Products p ON p.ProductID = od.ProductID
WHERE u.UserName = USER
Le mot-clé USER sur la dernière ligne est une instruction T-SQL qui retourne l’utilisateur en cours.
Utilisation des fonctions de table
Cette méthode a été creusée par le MVP Marcello Poletti. Il a vu qu’en mettant une requête comme celle de Commande_S_Recherche_3 dans une fonction de table (inline) et en testant quelques exemples, il avait toujours le plan optimum, comme s’il avait utilisé du code dynamique.
CREATE FUNCTION Commande_S_Recherche_11func (
@no_commande int = NULL,
@datedebut datetime = NULL,
@datefin datetime = NULL,
@prixmini money = NULL,
@prixmaxi money = NULL,
@no_client nchar(5) = NULL,
@nom_client nvarchar(40) = NULL,
@ville nvarchar(15) = NULL,
@region nvarchar(15) = NULL,
@pays nvarchar(15) = NULL,
@no_produit int = NULL,
@nom_produit nvarchar(40) = NULL)
RETURNS TABLE AS
RETURN
(
SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
c.CustomerID, c.CompanyName, c.Address, c.City, c.Region,
c.PostalCode, c.Country, c.Phone, p.ProductID,
p.ProductName, p.UnitsInStock, p.UnitsOnOrder
FROM Orders o
JOIN [Order Details] od ON o.OrderID = od.OrderID
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN Products p ON p.ProductID = od.ProductID
WHERE (o.OrderID = @no_commande OR @no_commande IS NULL)
AND (o.OrderDate >= @datedebut OR @datedebut IS NULL)
AND (o.OrderDate <= @datefin OR @datefin IS NULL)
AND (od.UnitPrice >= @prixmini OR @prixmini IS NULL)
AND (od.UnitPrice <= @prixmaxi OR @prixmaxi IS NULL)
AND (o.CustomerID = @no_client OR @no_client IS NULL)
AND (c.CompanyName LIKE @nom_client + '%' OR @nom_client IS NULL)
AND (c.City = @ville OR @ville IS NULL)
AND (c.Region = @region OR @region IS NULL)
AND (c.Country = @pays OR @pays IS NULL)
AND (od.ProductID = @no_produit OR @no_produit IS NULL)
AND (p.ProductName LIKE @nom_produit + '%' OR @nom_produit IS NULL)
)
Si on l’invoque comme ceci :
SELECT *
FROM Commande_S_Recherche_11func(11000, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
ORDER BY OrderID
comparé au plan de :
EXEC Commande_S_Recherche_1 @no_commande = 11000
On voit qu’on a exactement les mêmes. Il en est de même pour ces exemples :
SELECT * FROM Commande_S_Recherche_11func(NULL, NULL, NULL, NULL,
NULL, N'ALFKI', NULL, NULL, NULL, NULL, NULL, NULL)
ORDER BY OrderID
EXEC Commande_S_Recherche_1 @no_client = N'ALFKI'
Pourquoi ces résultats ? Rappelons que les fonctions de table inline sont comme des macros, “aplaties” à l’exécution. Donc c’est comme si on avait écrit la requête avec les valeurs dans le SQL. Ainsi, quand on cherche un n° de commande, l’optimiseur lit ceci :
WHERE (o.OrderID = 11000 OR 11000 IS NULL)
AND (o.OrderDate >= NULL OR NULL IS NULL)
AND (o.OrderDate <= NULL OR NULL IS NULL)
...
et est capable d’optimiser en conséquence.
Mais quel rapport avec le SQL dynamique ? Nous avons ici une solution qui semble idéale. Cependant, projetons là dans un vrai contexte d’appel par une application :
cmd = @"SELECT OrderID, ...
FROM Commande_S_Recherche_11fun(@no_commande, @datedebut, @datefin, ...
Ensuite, on définit tous les paramètres, même quand l’utilisateur ne recherche pas sur certains critères et c’est là la faille. On obtient le résultat de Commande_S_Recherche_3 sans l’option WITH RECOMPILE, parce qu’on a paramétré la requête qui va être mise en cache et elle devra marcher pour tous les jeux de paramètres.
Si on ajoute l’option OPTION (RECOMPILE), on obtient le même résultat que Commande_S_Recherche_3 avec l’option de recompilation. En fait, on a ré-implémenté Commande_S_Recherche_3 d’une autre façon.
Quand on invoque la fonction manuellement, on passe le littéral NULL (ou DEFAULT) comme paramètres quand l’utilisateur ne passe pas de valeur. Ainsi, en fonction du jeu de paramètres, la chaîne de requête est différente, ce qui donne un comportement similaire au SQL dynamique.
Et donc, pour pousser dans cette direction, on peut écrire une requête hybride. Elle utiliserait une fonction pour interdire les droits de SELECT directement sur les tables, mais les paramètres seraient écrit via du SQL dynamique pour les rendre inline.
SELECT @sql =
'SELECT OrderID, OrderDate, UnitPrice, Quantity,
CustomerID, CompanyName, Address, City, Region,
PostalCode, Country, Phone, ProductID,
ProductName, UnitsInStock, UnitsOnOrder
FROM dbo.Commande_S_Recherche_11fun (' +
CASE
WHEN @no_commande IS NOT NULL THEN '@no_commande'
ELSE 'DEFAULT'
END
+ ', ' + --...
Conclusion
Vous avez maintenant différentes méthodes à disposition pour implémenter ce type de recherche, utilisant aussi bien du SQL statique que dynamique. Vous avez pu voir que le SQL dynamique apporte les meilleures performances, tout en gardant un code maintenable. Avec le code statique, nous avons un code encore plus maintenable mais des résultats discutables.
Vous avez aussi pu remarquer que, pour le SQL statique, il faut être créatif, jouer avec les astuces, déjouer les pièges et décrypter le comportement de l’optimiseur. Aussi nous avons vu que la combinaison des 2 mondes peut être une bonne pratique.
Mais encore une fois, retenez bien le plus important, que vous partiez sur du SQL statique ou dynamique, il est important de tester, avec tous les jeux de paramètres possibles et imaginables. Vous vérifierez en premier lieu que le résultat est correct et ensuite qu’il correspond à vos exigences de performances. Ensuite, il faudra piocher dans la boite à outils pour tuner la requête.
Ne négligez pas non plus dans vos tests la volumétrie, qui doit correspondre au schéma final. On travaille, et il en est de même pour l’optimiseur, différemment entre 50 milles et 50 millions d’enregistrements.
La conclusion de Jean-Pierre RIEHL
Je ne vais pas conclure en revenant sur les différentes techniques présentées dans cet article. Je vais par contre revenir sur le choix entre statique et dynamique et aussi sur une approche plus méthodologique de la problématique.
Avant cela, d’un point de vue technique, je voudrais aussi parler de la recherche Full-Text (plein texte). Elle n'est pas abordée ici et pour cause, on recherche ici sur des critères précis, correspondants à des champs du modèle. La recherche Full-Text n'a pas cet objectif et a été créée pour répondre à un autre type de recherche, la recherche d'une expression dans des lignes de texte. C'est une problématique à part entière même si elle est connexe.
Sur la différence entre statique et dynamique, j'insisterai sur le pragmatisme. Faîtes le choix qui convient le mieux à votre contexte, à vos contraintes. En effet, il ne faut pas être dogmatique et dire que l'un est bien et l'autre mal. Chaque voie a ses avantages et ses inconvénients et il faut juste faire correspondre ceux-ci à vos contraintes. Par exemple, si vous avez une contrainte forte d'accès direct aux tables, la voie dynamique n'est pas la plus adaptée mais il faut balayer l'ensemble des solutions de contournement avant de fermer la porte (EXECUTE AS, vues, etc.).
Personnellement, j'ai toujours été réticent au SQL dynamique car la maintenance, le risque d'erreur, la maîtrise étaient toujours plus compliqués. Néanmoins, il faut reconnaitre que c'est le meilleur compromis pour les performances. Mais comme cet article le montre, on peut très bien arriver au même résultat avec du SQL statique. Cependant, cela requiert une connaissance parfaite du moteur SQL (compilation, gestion du cache, plan d'exécution, optimiseur).
Ce dernier point me permet de le redire : SQL Server n'est pas simple, c'est un moteur de base de données d'entreprise, conçu pour traiter des volumes énormes et le maîtriser nécessite des compétences et de l’expérience.
Sur l'aspect méthodologique, j'ai trop souvent vu des équipes de développement qui cherchent à tout faire rentrer dans la même boite au nom de la sacro-sainte généricité et qui après sont confrontées à des graves problèmes de performance. La recherche est une fonctionnalité à part entière dans une application. Il faut l'aborder avec une bonne conception, qui ne soit surtout pas générique. Si votre champ de saisie sert à 80% du temps à saisir un n° de commande composé de 2 lettres et 5 chiffres, identifiez le dans votre code métier et faite une recherche simple sur une commande par sa clé primaire (ou pas). Une pré-analyse de la saisie peut souvent aiguiller vers une fonctionnalité plus simple de votre application (cf. la technique des IF dans l’article d’Erland). Chez tous les clients chez qui je suis intervenu, on pouvait sortir un cas simple utilisé à 80% ou plus en étudiant les logs (loi de pareto). Donc avant de partir vers de la technique complexe, faites une passe avec votre chef de projet et votre client pour comprendre ce qu'il veut rechercher et comment.
Enfin, je terminerai cette conclusion sur l'importance des données elles-mêmes. En effet, il ne suffit pas de bien connaître le langage et le moteur, il faut projeter ces postulats avec vos données. Vous ne faîtes pas la même chose avec une base de 1Go qu'avec une base de 1To. Et quand bien même, il faut considérer la distribution des valeurs, le modèle de données, etc.
Tout ceci me permet de conclure sur le fait qu'il ne faut pas hésiter à vous faire aider pour la réalisation d'une base de données performante ou de vous faire assister sur son optimisation. L'intervention d'un expert est souvent plus rentable que de tatonner pendant des jours, au risque de faire une erreur.