{"id":506,"date":"2012-07-03T00:00:54","date_gmt":"2012-07-02T22:00:54","guid":{"rendered":"http:\/\/www.sqlserver.fr\/blog\/?p=506"},"modified":"2026-05-02T14:31:09","modified_gmt":"2026-05-02T12:31:09","slug":"recherche-multi-criteres","status":"publish","type":"post","link":"https:\/\/www.sqlserver.fr\/blog\/recherche-multi-criteres\/","title":{"rendered":"Recherche multi-crit\u00e8res"},"content":{"rendered":"

Une probl\u00e9matique r\u00e9currente au niveau SQL Server consiste \u00e0 r\u00e9aliser des requ\u00eates de recherche multicrit\u00e8res\u00a0avec des crit\u00e8res facultatifs. Le but est bien \u00e9videmment que chacune des combinaisons fournisse le r\u00e9sultat demand\u00e9 avec des performances optimales.<\/p>\n

Pour d\u00e9tailler le raisonnement, nous allons nous appuyer sur le base AdventureWorksDW2008R2, que l’on peut trouver gratuitement ici<\/a>.<\/p>\n

Prenons par exemple la table suivante :<\/p>\n

\"\"<\/a><\/p>\n

Consid\u00e9rons par exemple que nous avons un \u00e9cran de recherche qui permet de filtrer les enregistrement de cette table suivant les crit\u00e8res suivants : CurrencyKey, DueDateKey, EmployeeKey, OrderDateKey, ProductKey, PromotionKey, ResellerKey et ShipDateKey.<\/p>\n

Cherchons \u00e0 construire une proc\u00e9dure stock\u00e9e qui prendrait en param\u00e8tres d’entr\u00e9e 8 valeurs correspondant \u00e0 ces crit\u00e8res, et qui retournerait l’ensemble des enregistrements correspondant. Ajoutons comme difficult\u00e9 suppl\u00e9mentaire le fait que chaque crit\u00e8re de s\u00e9lection est facultatif, c’est-\u00e0-dire que le filtre associ\u00e9 ne doit pas \u00eatre pris en consid\u00e9ration si la valeur fournie \u00e0 la proc\u00e9dure stock\u00e9e vaut Null.<\/p>\n

On obtient une proc\u00e9dure ayant l’allure suivante :<\/p>\n

create procedure Recherche1 \r\n\t@CurrencyKey int=Null,\r\n\t@DueDateKey int=Null,\r\n\t@EmployeeKey int=Null,\r\n\t@OrderDateKey int=Null,\r\n\t@ProductKey int=Null,\r\n\t@PromotionKey int=Null,\r\n\t@ResellerKey int=Null,\r\n\t@ShipDateKey int=Null\r\nAS\r\nBEGIN\r\n\tSELECT * FROM dbo.FactResellerSales\r\n\tWHERE (@CurrencyKey is null or CurrencyKey=@CurrencyKey)\r\n\t\tAND (@DueDateKey is null or DueDateKey=@DueDateKey)\r\n\t\tAND (@EmployeeKey is null or EmployeeKey=@EmployeeKey)\r\n\t\tAND (@OrderDateKey is null or OrderDateKey=@OrderDateKey)\r\n\t\tAND (@ProductKey is null or ProductKey=@ProductKey)\r\n\t\tAND (@PromotionKey is null or PromotionKey=@PromotionKey)\r\n\t\tAND (@ResellerKey is null or ResellerKey=@ResellerKey)\r\n\t\tAND (@ShipDateKey is null or ShipDateKey=@ShipDateKey)\r\nEND<\/pre>\n
Cherchons maintenant \u00e0 tester un peu notre proc\u00e9dure stock\u00e9e, et surtout \u00e0 observer ses performances.<\/p>\n
Premier test, aucun crit\u00e8re fourni :<\/p>\n
exec Recherche1 -- Liste tous les enregistrements<\/pre>\n
\"\"<\/a><\/p>\n
Jusqu’ici, tout va bien, il est normal que l’on utilise un scan de l’index clsutered, puisque l’on cherche \u00e0 r\u00e9cup\u00e9rer toutes les colonnes, pour tous les enregistrements de la table.<\/p>\n
Maintenant, choisissons un filtre sur la date de commande.<\/p>\n
exec Recherche1 @OrderDateKey=20080331 -- Filtre sur 4 enregistrements<\/pre>\n
On s’aper\u00e7oit que le plan d’ex\u00e9cution n’a pas chang\u00e9, et que le moteur passe toujours par un scan complet de l’index clustered, c’est-\u00e0-dire par un parcours complet de la table.<\/p>\n
En fait, aucune directive particuli\u00e8re n’ayant \u00e9t\u00e9 donn\u00e9e, le moteur conserve le plan d’ex\u00e9cution qu’il avait d\u00e9termin\u00e9 lors de la premi\u00e8re requ\u00eate, et l’applique de nouveau. Pour s’en convaincre, choisissons de repasser ces deux requ\u00eates, mais dans un sens puis dans l’autre, et en ayant pris soin de vider le cache des plans d’ex\u00e9cution entre temps…<\/p>\n
dbcc freeproccache\r\nexec Recherche1 -- Liste tous les enregistrements\r\nexec Recherche1 @OrderDateKey=20080331 -- Filtre sur 4 enregistrements<\/pre>\n
\"\"<\/a><\/p>\n
dbcc freeproccache\r\nexec Recherche1 @OrderDateKey=20080331 -- Filtre sur 4 enregistrements\r\nexec Recherche1 -- Liste tous les enregistrements<\/pre>\n
\"\"<\/a><\/p>\n
Au del\u00e0 du fait que le plan d’ex\u00e9cution est conserv\u00e9, on remarque ici surtout que le syst\u00e8me se fourvoie compl\u00e8tement quand au co\u00fbt relatif des requ\u00eates. Ainsi, pour les ex\u00e9cutions passant par l’index non clustered (il s’agit de l’index\u00a0IX_FactResellerSales_OrderDateKey), les co\u00fbts pr\u00e9vus sont identiques pour les deux requ\u00eates, alors qu’en r\u00e9alit\u00e9 les dur\u00e9es ne sont pas du tout les m\u00eames…<\/p>\n
set statistics io on\r\nset statistics time on\r\ndbcc freeproccache\r\nexec Recherche1 @OrderDateKey=20080331 -- Filtre sur 4 enregistrements\r\nexec Recherche1 -- Liste tous les enregistrements<\/pre>\n
\"\"<\/a><\/p>\n
Plusieurs solutions sont possibles pour corriger ce probl\u00e8me de performances.<\/p>\n
Tout d’abord, il est possible d’ajouter un param\u00e8tre lors de la d\u00e9finition de la requ\u00eates pour indiquer au moteur de re-d\u00e9terminer syst\u00e9matiquement le plan d’ex\u00e9cution correspondant le mieux aux param\u00e8tres d’entr\u00e9e fournis.<\/p>\n
create procedure Recherche2\r\n\t@CurrencyKey int=Null,\r\n\t@DueDateKey int=Null,\r\n\t@EmployeeKey int=Null,\r\n\t@OrderDateKey int=Null,\r\n\t@ProductKey int=Null,\r\n\t@PromotionKey int=Null,\r\n\t@ResellerKey int=Null,\r\n\t@ShipDateKey int=Null\r\nWITH RECOMPILE -- D\u00e9terminer un nouveau plan d'ex\u00e9cution \u00e0 chaque fois.\r\nAS\r\nBEGIN\r\n\tSELECT * FROM dbo.FactResellerSales\r\n\tWHERE (@CurrencyKey is null or CurrencyKey=@CurrencyKey)\r\n\t\tAND (@DueDateKey is null or DueDateKey=@DueDateKey)\r\n\t\tAND (@EmployeeKey is null or EmployeeKey=@EmployeeKey)\r\n\t\tAND (@OrderDateKey is null or OrderDateKey=@OrderDateKey)\r\n\t\tAND (@ProductKey is null or ProductKey=@ProductKey)\r\n\t\tAND (@PromotionKey is null or PromotionKey=@PromotionKey)\r\n\t\tAND (@ResellerKey is null or ResellerKey=@ResellerKey)\r\n\t\tAND (@ShipDateKey is null or ShipDateKey=@ShipDateKey)\r\nEND<\/pre>\n
On constate bien ici que les plans d’ex\u00e9cution sont adapt\u00e9s.<\/p>\n
exec Recherche2 -- Liste tous les enregistrements\r\nexec Recherche2 @OrderDateKey=20080331 -- Filtre sur 4 enregistrements<\/pre>\n
\"\"<\/a><\/p>\n
Le probl\u00e8me de cette solution est le surco\u00fbt du calcul syst\u00e9matique du plan d’ex\u00e9cution, notamment lorsque les m\u00eames combinaisons de param\u00e8tres d’entr\u00e9e reviennent souvent.<\/p>\n
Nous nous attacherons ici \u00e0 d\u00e9tailler une solution qui permet d’adapter la requ\u00eate ex\u00e9cut\u00e9e pour chaque combinaison de param\u00e8tres d’entr\u00e9e. Dans notre cas, elle joue presque \u00e0 jeu \u00e9gal avec la recompilation syst\u00e9matique. N\u00e9anmoins, dans la pratique, elle la devance largement lorsque la requ\u00eate contient des jointures qui ne sont \u00e0 r\u00e9aliser que pour certains param\u00e8tres ou certaines combinaisons de param\u00e8tres. Voici donc la notion de requ\u00eate dynamique.<\/p>\n
Nous allons donc d\u00e9finir une requ\u00eate en fonction des crit\u00e8res d’entr\u00e9e de la proc\u00e9dure, et nous n’aurons alors plus qu’\u00e0 ex\u00e9cuter cette requ\u00eate minimaliste. Cette requ\u00eate sera construite \u00e9tape par \u00e9tape, en y ajoutant les conditions seulement si n\u00e9cessaire.<\/p>\n
create procedure Recherche3\r\n\t@CurrencyKey int=Null,\r\n\t@DueDateKey int=Null,\r\n\t@EmployeeKey int=Null,\r\n\t@OrderDateKey int=Null,\r\n\t@ProductKey int=Null,\r\n\t@PromotionKey int=Null,\r\n\t@ResellerKey int=Null,\r\n\t@ShipDateKey int=Null\r\nWITH RECOMPILE -- D\u00e9terminer un nouveau plan d'ex\u00e9cution \u00e0 chaque fois.\r\nAS\r\nBEGIN\r\n\tdeclare @Requete nvarchar(max)\r\n\r\n\tselect @Requete=N'SELECT * FROM dbo.FactResellerSales\r\n\tWHERE 1=1'\r\n\r\n\tif (@CurrencyKey is not null)\r\n\t\tselect @Requete=@Requete+ N' AND CurrencyKey=@CurrencyKey'\r\n\tif (@DueDateKey is not null)\r\n\t\tselect @Requete=@Requete+ N' AND DueDateKey=@DueDateKey'\r\n\tif (@EmployeeKey is not null)\r\n\t\tselect @Requete=@Requete+ N' AND EmployeeKey=@EmployeeKey'\r\n\tif (@OrderDateKey is not null)\r\n\t\tselect @Requete=@Requete+ N' AND OrderDateKey=@OrderDateKey'\r\n\tif (@ProductKey is not null)\r\n\t\tselect @Requete=@Requete+ N' AND ProductKey=@ProductKey'\r\n\tif (@PromotionKey is not null)\r\n\t\tselect @Requete=@Requete+ N' AND PromotionKey=@PromotionKey'\r\n\tif (@ResellerKey is not null)\r\n\t\tselect @Requete=@Requete+ N' AND ResellerKey=@ResellerKey'\r\n\tif (@ShipDateKey is not null)\r\n\t\tselect @Requete=@Requete+ N' AND ShipDateKey=@ShipDateKey'\r\n\r\n\texec sp_executesql @Requete,\r\n\t\t\t\t\tN'@CurrencyKey int,\r\n\t\t\t\t\t\t@DueDateKey int,\r\n\t\t\t\t\t\t@EmployeeKey int,\r\n\t\t\t\t\t\t@OrderDateKey int,\r\n\t\t\t\t\t\t@ProductKey int,\r\n\t\t\t\t\t\t@PromotionKey int,\r\n\t\t\t\t\t\t@ResellerKey int,\r\n\t\t\t\t\t\t@ShipDateKey int',\r\n\t\t\t\t\t@CurrencyKey=@CurrencyKey,\r\n\t\t\t\t\t\t@DueDateKey=@DueDateKey,\r\n\t\t\t\t\t\t@EmployeeKey=@EmployeeKey,\r\n\t\t\t\t\t\t@OrderDateKey=@OrderDateKey,\r\n\t\t\t\t\t\t@ProductKey=@ProductKey,\r\n\t\t\t\t\t\t@PromotionKey=@PromotionKey,\r\n\t\t\t\t\t\t@ResellerKey=@ResellerKey,\r\n\t\t\t\t\t\t@ShipDateKey=@ShipDateKey\r\n\r\nEND<\/pre>\n
Et cette fois-ci, on consta bien sur les plan d’ex\u00e9cution que les requ\u00eates sont adapt\u00e9es aux crit\u00e8res de recherche. (au passage, le 1=1 permet \u00e0 moindre co\u00fbt de ne pas s’emb\u00eater avec des tests suppl\u00e9mentaires pour savoir quel param\u00e8tre sera le premier \u00e0 \u00eatre utilis\u00e9…)<\/p>\n
exec Recherche3 -- Liste tous les enregistrements\r\nexec Recherche3 @OrderDateKey=20080331 -- Filtre sur 4 enregistrements<\/pre>\n
\"\"<\/a><\/p>\n
Le fait de construire de la sorte la requ\u00eate est nomm\u00e9 \u00ab\u00a0SQL dynamique\u00a0\u00bb. Dans notre exemple simple, la requ\u00eate n’implique pas de jointures, mais si cela avait \u00e9t\u00e9 le cas, on aurait de la m\u00eame mani\u00e8re pu choisir de positionner ou pas ces jointures suivant leur n\u00e9cessit\u00e9 ou pas en fonction des param\u00e8tres fournis, et cette possibilit\u00e9 n’est pas offerte avec la solution de recompilation syst\u00e9matique \u00e9voqu\u00e9e plus haut qui, elle, conserve dans tous les cas la m\u00eame requ\u00eate…<\/p>\n
Nous verrons dans un prochain article comment optimiser sous SQL Server 2012 les performances de requ\u00eates de ce style lorsque plusieurs param\u00e8tres sont fournis \u00e0 la proc\u00e9dure stock\u00e9e…<\/p>\n","protected":false},"excerpt":{"rendered":"
Une probl\u00e9matique r\u00e9currente au niveau SQL Server consiste \u00e0 r\u00e9aliser des requ\u00eates de recherche multicrit\u00e8res\u00a0avec des crit\u00e8res facultatifs. Le but est bien \u00e9videmment que chacune des combinaisons fournisse le r\u00e9sultat demand\u00e9 avec des performances optimales.<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-506","post","type-post","status-publish","format-standard","hentry","category-article_sql"],"_links":{"self":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/506","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/comments?post=506"}],"version-history":[{"count":34,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/506\/revisions"}],"predecessor-version":[{"id":1950,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/506\/revisions\/1950"}],"wp:attachment":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/media?parent=506"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/categories?post=506"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/tags?post=506"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}