Blog

Kitchen Sink à proscrire !


Kezako “Kitchen Sink” en T-SQL ? C’est une technique qui consiste à essayer de remplir le plus de conditions possibles dans une requête. Cela se traduit par la répétition de clauses du type WHERE @variable IS NULL OR colonne = @variable. Et là c’est le drame, patatras, on attend encore le résultat… C’est une approche assez courante mais surtout coûteuse en terme de performance sur de gros volume de données. Cette problématique est d’autant plus intéressante qu’elle nous permettra d’aborder d’autres thèmes sous-jacents pour le tuning d’instance et de requête.

Kitchen Sink : version 1

C’est un exemple courant de kitchen sink.

Expert SQL Server - Kitchen Sink à proscrire ! - SQL Server  - kitchen_sink_planquery1

Kitchen Sink : version 2

L’exemple ci-dessous est une version un peu plus élégante de kitchen sink syntaxiquement mais qui se traduit quasiment par le même résultat en terme de performance, suivant l’indexation, on le verra plus tard.

Expert SQL Server - Kitchen Sink à proscrire ! - SQL Server  - kitchen_sink_planquery2

Requête dynamique : EXEC

On pourrait se contenter de réécrire la requête comme ceci, en passant directement les valeurs littérales mais est-ce bien raisonnable ?

Expert SQL Server - Kitchen Sink à proscrire ! - SQL Server  - kitchen_sink_plan_query3

Il y a une petite différence par rapport aux deux appels précédents en mode kitchen sink, dans une requête dynamique, le moteur est capable de détecter un index manquant qui compte tenu du prédicat est tout à fait justifié.

Expert SQL Server - Kitchen Sink à proscrire ! - SQL Server  - missing_index

Requête dynamique : sp_executesql

Voici une manière plus académique de faire du T-SQL dynamique.

Expert SQL Server - Kitchen Sink à proscrire ! - SQL Server  - kitchen_sink_plan_query4

Là encore, l’index manquant est détecté.

Expert SQL Server - Kitchen Sink à proscrire ! - SQL Server  - missing_index

Coût respectifs des 4 requêtes

Voici le résultat final en terme de coût, les requêtes dynamiques sont “légèrement” plus performante, 2% de gain mais c’est loin d’être suffisant.

Expert SQL Server - Kitchen Sink à proscrire ! - SQL Server  - kitchen_sink_result

Ajout de l’index manquant

Comme indiqué, pour les requêtes dynamiques, la détection d’index manquant se fait bien. Ajoutons notre index et rejouons nos 4 requêtes. Ha là c’est beaucoup mieux, on obtient un gain significatif sur les 3 derniers appels à savoir :

– Kitchen Sink : version 2
– Requête dynamique : EXEC
– Requête dynamique : sp_executesql

Expert SQL Server - Kitchen Sink à proscrire ! - SQL Server  - kitchen_sink_result2

Et si on en profitait pour prendre connaissance du paramètre d’instance Optimize For Ad Hoc Workload et exécutons la requête dynamique EXEC avec une valeur de paramètre différente, @StartOrderDate DATETIME = ‘2006-05-01’. A l’arrivée, on se retrouve avec autant de plans que de valeurs de paramètre distinctes, sans parler de la taille des plans et des compilations à répétition.

Expert SQL Server - Kitchen Sink à proscrire ! - SQL Server  - kitchen_sink_2_plans

On pourrait se contenter d’activer le paramètre d’instance Optimize For Ad Hoc Workload mais est-ce bien suffisant ? Non car contrairement à la requête dynamique sp_executesql, on ne peut pas bénéficier du mécanisme de cache des requêtes préparées, ni profiter de paramètre en sortie et en plus on est sujet à l’injection SQL. Bref, cela fait beaucoup de concessions au final.

Conclusion

Bien que la syntaxe du T-SQL dynamique soit laborieuse à appréhender et le debug pénible en pratique, c’est une méthode redoutablement efficace au niveau performance. Même si la détection d’index manquant n’est pas à prendre au pied de la lettre, elle est fonctionnelle pour les requêtes dynamiques. Dans le cas présent, cette proposition d’index était pertinente. Et enfin la méthode utilisée pour recourir à une requête dynamique a aussi son importance. Pour finir voici une session co-animé avec David Barbarin sur l’optimisation de requêtes sur la base des plans d’exécution dans laquelle la kitchen sink est évoquée.

Auteur

Expert SQL Server - Kitchen Sink à proscrire ! - SQL Server  - avatar_ninja_tete-150x150
Sarah Béquet
Archietcte Data Microsoft, les maîtres mots sont : performance, industrialisation, méthodologie & bonne humeur.
error: