Parameter Sniffing in SQL
If you ever face the problem that your stored procedure works perfectly in SSMS, but not well on your application (either web, or ERP; Acumatica and Microsoft Dynamics 365 Business Central, NAV, Navision by using ADO objects), you will start to scratch your head why that is the causes.
There is something called “Parameter” Sniffing. Yes. So if you have Stored Procedure like this
create procedure dbo.SearchProducts
@Keyword varchar(100)
As
select * from Products where Keyword like @Keyword
It shoud be changed to like that
create procedure dbo.SearchProducts
@Keyword varchar(100)
As
Declare @Keyworddummy as varchar(100)
Set @Keyworddummy = @Keyword
select * from Products where Keyword like @Keyworddummy
Wow.. Why?
In simple english, it simply try to look (compile) for parameter in every single row in SQL is fetched;
Instead of doing that, SQL 2005 or later version, we allow that indiviaul plans to be recomplied only.
We can use other trick as well such as
(OPTIMIZE FOR RECOMPILE)
Disable auto-update statistics during the batch
Here is the reference from Microsoft if you want to get into deeper : https://docs.microsoft.com/en-us/archive/blogs/mdegre/what-is-parameter-sniffing
