Chez presque tous mes clients, tout le monde utilise le Query Designer pour écrire un SELECT en T-SQL, que ce soit pour faire une vue, une simple recherche ou encore pour créer un Dataset dans Reporting Services. Nombre de mes collègues utilise cet outil également.
A chaque fois je fais la même remarque qui me fait passer pour un vieux con has been :
Evitez d’utiliser le designer de requête ou bien refaite une passe sur le T-SQL généré.
Mes arguments étant les suivants : ordre des jointures, lisibilité du code généré et une expérience avec la bête ayant développée ma mauvaise impression à son égard.
Mais depuis SQL Server 2005, j’ai dû souvent avaler mon chapeau car la qualité de l’optimiseur de requêtes faisait passer mon argumentaire pour un discours de bonimenteur.
Mais j’ai enfin trouvé un cas reproductible où le designer de requête ne ramène pas le résultat escompté et où l’optimiseur de requêtes ne peut pas corriger le tir. En plus, pour appuyer mon discours, je tiens à préciser que cette imprécision du designer a conduit à des erreurs dans des rapports de synthèse.
Explication de texte :
Nous allons travailler avec AdventureWorks. Mon besoin est le suivant : je veux récupérer les produits bleus et, si j’ai l’info, afficher leur prix en juillet 2002. Il y a 26 produits bleus dans la table Product.
Voici la requête fournie par l’éditeur de requête :
SELECT Production.Product.ProductNumber, Production.Product.Name, Production.ProductCostHistory.StandardCost AS Cost200112
FROM Production.Product LEFT OUTER JOIN
Production.ProductCostHistory ON Production.Product.ProductID = Production.ProductCostHistory.ProductI
WHERE (Production.ProductCostHistory.StartDate = CONVERT(DATETIME, '2002-07-01 00:00:00', 102)) AND
(Production.Product.Color = N'Blue')
Et en résultat, je n’ai qu’un seul produit bleu au lieu de 26. Le problème est que le designer ajoute la condition de la date à l’ensemble du jeu de résultat, or s’il n’y a pas de prix disponible à la date demandée, la valeur sera NULL à cause de la jointure externe.
Pour corriger le problème, il faut remonter la condition sur la jointure pour filter la table ProductCostHistory avant de faire la jointure.
ON Production.Product.ProductID = Production.ProductCostHistory.ProductID AND
(Production.ProductCostHistory.StartDate = CONVERT(DATETIME, '2002-07-01 00:00:00', 102))
Ainsi, le LEFT JOIN est correctement interprété à l’exécution et on obtient bien les 26 produits bleus. On utilisait cette technique pour optimiser sous SQL Server 2000 car il arrivait que l’optimiseur ordonne mal les opérations dans le plan d’exécution.
Si on réouvre la requête avec l’éditeur, il représente bien la condition mais de façon assez… bizarre. D’ailleurs, je ne saurais même pas le refaire à la souris.
Aussi, le designer écrit les tables de la clause FROM dans l’ordre où vous posez les tables sur la surface de travail, ce qui lui fait mettre un RIGHT JOIN au lieu d’une lecture plus naturelle avec un LEFT JOIN. Ceci entraîne une maintenance plus complexe, déjà que se plonger dans une grosse requête n’est pas forcément trivial…
Autre problème que j’ai rencontré quelques fois, quand il y a trop de jointures, LEFT, RIGHT et INNER, en essayant de les mettre bout à bout il arrive qu’il se rate sur le résultat global (bien souvent avec des données non remontées) :-(.
En conclusion, même si le designer vous fait gagner du temps de rédaction, pensez à vérifier les jointures (ordre, condition) et en particulier les jointures externes.
Business Geek 