Temps d'exécution différent entre requête et procédure

Bonjour,

Je n'arrive pas à comprendre pourquoi j'ai un temps d'exécution différent entre une requête et une procédure.

J'ai une requête qui commence par

Code:

---

1 2 3 4

declare @_idlot as int;set @_idlot = 7123;     SET NOCOUNT ON;

---

et ma procédure stockée commence par

Code:

---

1 2 3

ALTER PROCEDURE [dbo].[PS_Get_](@_idlot INT)AS BEGIN SET NOCOUNT ON;

---

Tout le reste est exactement le même.
Sous SSMS, quand j'exécute la requête j'ai le résultat en 7 secondes, mais quand j'exécute la procédure cela dure plus d'une minute.
Je n'arrive pas à comprendre pourquoi..

Je sais que mes connaissances sont limitées, et je vois déjà le type de réponse SQLPro : "une bonne formation s'impose"
mais si vous avez des explications, je suis preneur.

Merci d'avance

Par contre si j'ajoute ça dans ma procédure

Code:

---

1 2 3 4 5 6 7 8 9

ALTER PROCEDURE [dbo].[PS_Get_](@_idlot INT) AS BEGIN declare @id_lot as int; set @id_lot = @_idlot; SET NOCOUNT ON;

---

ma procédure ne prend plus autant de temps..

Problème classique de "parameter sniffing"....

La requête travaille sur les stats détaillées, très pertinentes = plan d'exec affuté
La procédure enregistre le plan avec des stats globales moins pertinente : plan d'exec moyen

En remettant une couche de détail dans la procédure tu retrouve le comportement de la requête adhoc

Tu aurais la même différence en retirant la variable locale et en ajoutant l'indicateur de requête RECOMPILE

Code:

---

1 2 3 4 5 6 7 8

ALTER PROCEDURE [dbo].[PS_Get_](@_idlot INT)AS BEGIN SET NOCOUNT ON;   SELECT... OPTION (RECOMPILE)   GO

---

A +

Bonjour,

Merci pour les explications et pour le terme.. je vais faire un peu de lecture

Mais avant de lire, je vais quand même demander : pourquoi ne pas utiliser

Code:

---

1 2	SELECT... OPTION(RECOMPILE)

---

à chaque fois?

C'est ce que font certains SGBDR bas d gamme comme MySQmerde....

Le problème est que le calcul d'un plan de requête est susceptible de prendre plus de temps à être calculé que certaines exécution d'un plan hautement basique.
Entre d'autre terme, si le calcul de mon plan, explorant un million d'opérations (ce que fait SQL Server dans le pure des cas) met 100 ms alors que le premier plan basique de requête faisant des scans de table aurait mis 50 ms de temps d'exécution, alors le recalculer à chaque fois est une idiotie.
En revanche si le plan est mis en cache (ce que fais SQL Server et que ne fais pas MySQL) cela permet d'économiser les 100 ms à chaque exécution pour avoir un plan optimal qui mettre moins de temps encore que le plan basique...

Cependant, si tu a l'assurance que le temps de calcul du plan sera toujours très inférieur au temps d'exécution (requêtes brassant beaucoup de données, ou renvoyant une très importante quantité de lignes) alors ça vaut le coup de forcer le recalcul systématiquement....

Tout est une question d'équilibre....

Il faut savoir néanmoins que SQL Server met en cause tout plan de requête pour lequel :
1) une table a été modifié
2) un index à été créé, modifié supprime pour une des tables de la requête
3) les statistiques sont obsolètes ou ont été recalculées depuis la dernière exécution
4) les paramètres de la session ne sont pas les mêmes (par exemple changement de culture...)
5) ...

Au fait as tu mis en place dans le sp_configure "optimize for adhoc workload" à 1 ?

A +

Merci pour ces compléments.
Pour l'option je ne sais pas.. je vais regarder.

Bonjour,

Je viens de regarder.
optimize for ad hoc workloads est à 0.

Mais je ne suis pas DbAdmin.
Et je ne vais pas faire ce changement, et surtout pas en pleine prod..

Mais je me garde ça sous le coude afin de voir à faire évoluer cette option.

Par contre sur le principe, il vaut mieux pour moi mettre une variable local, ou RECOMPILE dans mon cas?
Je n'ai pas encore compris la subtilité entre les deux..

Citation:

---

Envoyé par mail.spam

...
optimize for ad hoc workloads est à 0.

Mais je ne suis pas DbAdmin.
Et je ne vais pas faire ce changement, et surtout pas en pleine prod..

---

Pas d'incidence en pleine prod. Plutôt une notable amélioration !
Vérifiez quel est le volume occupé par l'ensemble des plans d'exécution des requêtes ad hoc, par la requête suivante :

Code:

---

1 2 3

SELECT SUM(size_in_bytes) / 1048576.0 AS PLAN_CACHE_SIZE_ADHOC_Mo FROM sys.dm_exec_cached_plans WHERE objtype = 'Adhoc'

---

Si c'est quelques Mo, pas grave. Si ce sont des Go, il faut agir URGEMENT !!! Cela obère le cache des données.

Citation:

---

Par contre sur le principe, il vaut mieux pour moi mettre une variable local, ou RECOMPILE dans mon cas?
Je n'ai pas encore compris la subtilité entre les deux..

---

Pas de grande différence.

A +

Voici le résultat

PLAN_CACHE_SIZE_ADHOC_Mo

1606.609375000

Et c'est à peu près équivalent pour chacune de mes 8 bases.
Et elles sont en réplication.

donc ça ne doit pas aider sur les performances de mon serveur..
Je vais prévenir mon DBAdmin

Ben oui, c'est pas terrible !

A +