L'OPTION (RECOMPILE) est toujours plus rapide ; pourquoi ?

Question

J'ai rencontré une situation étrange où l'ajout de OPTION (RECOMPILE) à ma requête la fait s'exécuter en une demi-seconde, alors que si je l'omets, la requête prend bien plus de cinq minutes.

C'est le cas lorsque la requête est exécutée depuis Query Analyzer ou depuis mon programme C# via SqlCommand.ExecuteReader() . Appeler (ou ne pas appeler) DBCC FREEPROCCACHE o DBCC dropcleanbuffers ne fait aucune différence ; les résultats des requêtes sont toujours renvoyés instantanément avec OPTION (RECOMPILE) et plus de cinq minutes sans. La requête est toujours appelée avec les mêmes paramètres [pour les besoins de ce test].

J'utilise SQL Server 2008.

Je suis assez à l'aise avec l'écriture du SQL mais je n'ai jamais utilisé un OPTION dans une requête et je n'étais pas familier avec le concept de cache de plan jusqu'à ce que je consulte les messages sur ce forum. D'après ce que j'ai lu dans ces messages, j'ai compris que OPTION (RECOMPILE) est une opération coûteuse. Elle crée apparemment une nouvelle stratégie de recherche pour la requête. Alors pourquoi les requêtes ultérieures qui omettent l'élément OPTION (RECOMPILE) sont si lents ? Les requêtes suivantes ne devraient-elles pas utiliser la stratégie de recherche calculée lors de l'appel précédent, qui comprenait l'indice de recompilation ?

Est-il très rare d'avoir une requête qui nécessite un indice de recompilation à chaque appel ?

Désolé pour cette question de base, mais je n'arrive pas à comprendre ce qui se passe.

UPDATE : On m'a demandé de poster la requête...

select acctNo,min(date) earliestDate 
from( 
    select acctNo,tradeDate as date 
    from datafeed_trans 
    where feedid=@feedID and feedDate=@feedDate 

    union 

    select acctNo,feedDate as date 
    from datafeed_money 
    where feedid=@feedID and feedDate=@feedDate 

    union 

    select acctNo,feedDate as date 
    from datafeed_jnl 
    where feedid=@feedID and feedDate=@feedDate 
)t1 
group by t1.acctNo
OPTION(RECOMPILE)

Lorsque j'exécute le test à partir de Query Analyzer, je fais précéder les lignes suivantes :

declare @feedID int
select @feedID=20

declare @feedDate datetime
select @feedDate='1/2/2009'

Lorsque je l'appelle à partir de mon programme C#, les paramètres sont transmis par l'intermédiaire de la fonction SqlCommand.Parameters propriété.

Pour les besoins de cette discussion, vous pouvez supposer que les paramètres ne changent jamais, ce qui nous permet d'exclure la cause d'un reniflement sous-optimal des paramètres.

Answer 1

OPTION (RECOMPILE) est utilisé dans des scénarios de génération de mots réels. Je l'ai utilisé pour éliminer l'odeur des paramètres et optimiser les grosses requêtes. C'est peut-être la réponse à votre problème, mais tout porte à croire que l'optimisation de l'inconnu (comme les variables locales) peut également résoudre le problème.

Je n'évite certainement pas l'option simplement parce qu'il y avait un bug temporel qui a été corrigé il y a plusieurs années. Le principal risque de OPTION (RECOMPILE) est lorsqu'il est utilisé de manière inappropriée, comme les demandes à haute fréquence.