La newsletter SQL Server gratuite de TechRepublic, livrée chaque mardi, contient des conseils pratiques qui vous aideront à devenir plus habile avec ce puissant système de gestion de base de données relationnelle. Abonnez-vous automatiquement aujourd’hui!

La sagesse généralement acceptée à propos des procédures stockées (ou sprocs) est que, parce que SQL peut les optimiser et les compiler, elles s’exécutent plus rapidement que les instructions SQL équivalentes exécutées à partir de QueryAnalyzer (ou peut-être transmises à partir d’une application frontale telle qu’une page Web ou un programme VB). Cela est vrai dans une certaine mesure.

Tout d’abord, vous devez savoir ce que fait SQL Server avec un nouveau sproc. Au moment de la création, il vérifie la syntaxe. S’il ne trouve aucune erreur, il ajoute le sproc aux tables système: sysobjects, sysdepends et syscomments (ce dernier stocke le corps du sproc). Par défaut, il ne compile pas le sproc au moment de la création.

Lors de la première exécution du sproc, SQL Server l’optimise et le compile. C’est à ce moment que SQL Server conçoit un plan de requête et le stocke dans son cache de procédures. Lors des appels ultérieurs, SQL regarde d’abord le cache, y trouve le sproc et ne le compile pas. Si le sproc n’est pas dans le cache, SQL Server le compile et le place dans le cache.

Mon expérience avec l’option WITH RECOMPILE

Il y a quelque temps, je supportais une page de recherche qui permettait à ses utilisateurs de rechercher par l’une des plusieurs colonnes. Ensuite, la page a appelé un sproc, en passant un paramètre pour indiquer quelle colonne rechercher. J’ai examiné le paramètre à l’aide d’un bloc de CASSE, puis j’ai exécuté l’une desplusieurs requêtes, en fonction de la colonne à rechercher.

Je savais que quelque chose n’allait pas quand j’ai commencé à tester ma procédure stockée allegedlyclever. En théorie, les performances de chaque recherche devraient au moins être à peu près les mêmes, mais ce n’est pas ce qui s’est passé. Lorsque j’effectuais plusieurs recherches, quel que soit l’ordre, la première serait rapide et les recherches ultérieures étaient beaucoup plus lentes.

Enfin, j’ai réalisé que la première fois que la procédure a été appelée, un plan de requête a été conçu et stocké dans le cache. Pendant que je cherchais sur cette colonne particulière, tout fonctionnerait comme prévu. Au moment où j’ai changé de colonne, cependant, les performances ont chuté. Pourquoi est-ce arrivé?

La première recherche que j’ai effectuée a créé un plan de requête et un storedit dans le cache. Par exemple, disons que je cherchais dans la colonne OrderDate. Si je passais la recherche dans la colonne Nom de l’entreprise, SQL utiliserait aveuglément le queryplan mis en cache, recherchant le nom de l’entreprise cible à l’aide de OrderDateindex. Pas étonnant que les performances chutent si dramatiquement.

Le correctif est assez simple. J’ai exécuté le sprocsupplying de l’option WITH RECOMPILE:

EXEC MySproc_Select '12/31/2004' WITH RECOMPILE

Cela indique à SQL Server de jeter le plan de requête existant et d’en construire un autre once mais seulement cette fois.

Vous pouvez également ajouter le WITH RECOMPILE directement à la procédure de stockage juste avant le mot-clé AS. Cela indique à SQL Server de supprimer le plan de requête à chaque exécution du sproc.

Il existe également une troisième option. J’aurais pu créer un sproc séparé pour chaque méthode de recherche, puis décider lequel exécuter dans le bloc CASE. De cette façon, la requête planassociée aux sous-sprocs reste dans le cache, où SQL peut en tirer parti. Puisque chacun des pignons a recherché exactement une colonne, il n’est pas nécessaire de recompiler.

La capacité de SQL Server à optimiser et à compiler une procédure stockée est excellente mais, si vous ne faites pas attention, elle peut vous mordre lorsque vous l’attendez le moins. Maintenant que vous savez comment résoudre le problème, il y a peut-être de nouvelles situations dans votre propre base de données que vous voudrez peut-être revoir.