SQL Parameter Sniffing when you use ADO Object in ERP

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