Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Performance switched with parameter
Message
De
21/09/2014 02:14:44
Walter Meester
HoogkarspelPays-Bas
 
 
À
20/09/2014 22:00:38
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Indexation
Versions des environnements
SQL Server:
SQL Server 2012
Application:
Web
Divers
Thread ID:
01607903
Message ID:
01607914
Vues:
52
>>ok, let me ask the question. Why are you setting the values in the script? Can't you set the values through parameters (instead of using variables)? SQL server is able to do parameter sniffing and use that to optimize the plan.
>
>What you saw was the auto generated result of my code. My code does pass those parameters as real SQL Client parameters.

Well, your code says this:
DECLARE @NoClient Int
DECLARE @NoStatus Int
DECLARE @NoStatus2 Int
DECLARE @NoStatus3 Int
DECLARE @NoStatus4 Int

SET @NoClient=3831141
SET @NoStatus=5
SET @NoStatus2=6
SET @NoStatus3=7
SET @NoStatus4=10
That is not the same as executing a parameterized query through sp_executesql(). That is because in the first, the optimizer cannot optimize for the parameter values while with sp_executesql, it can.

You also might execute it with the RECOMILE option. The optimizer will be forced to re-determine the execution plan for each call.

Can you rewrite the SQL to be executed through sp_executesql and see whether it resolves the issue?
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform