Hi All,
I have a query regarding optimization and SQL cache miss..
There is a stored procedure which has a parameter @idmember
Create proc proc_procname
@idmember int = null
Question: What is the difference if i use this parameter (@idmember ) directly in my sp
Or what if i declare a local variable say declare @mlocalidmember and then assign this variable the value of the parameter like set @mlocalidmember = @idmember and then use this local variable
Whats the difference b/w these two cases.
Can anyone comment on this please?
T-SQL
If you directly use the parameter value there is a chance of parameter sniffing that can slow down the performance. In that case you can go ahead and use second method
Refer this for more informations
http://furrukhbaig.wordpress.com/2007/08/22/parameter-sniffing/
Thanks Madhivana. Seems okay to me.
[quote user="Varun"]
[/quote]
Thanks and you are welcome
I prefer to use parameters instead of local variables and these are my reasons:
1- Parameters values carries valuable informations for optimizer about actual usage, and in case of parameters sniffing there is the OPTIMIZE FOR hint regarding procedure code or the WITH RECOMPILE option regarding procedure execution.
2- local variables will influence the execution plan and not necessarily in good manner, because the optimizer will try to figure out what the optimal execution plan for all situations blindly.
3- Extra work to be done (declaration and assignment of local variables and choosing different name for example).
So to sum up I prefer to use parameters first and let the optimization until I actually need it.
Managed Windows Shared Hosting by OrcsWeb