Why does the stored procedure run slow from code, but fast from SSMS?

Question

I am running a stored procedure from my web app, using Dapper. I ran the same stored procedure from SSMS first, to make sure all is well. It ran in 1-5 seconds from SSMS.

Then I copied/pasted my script into my app to be run through Dapper. When I ran my app and step debugged my code, the stored procedure ran for over 2 minutes and timed out. It's the same exact code. What could be causing the discrepancy?

Here is my code I am running from SSMS:

DECLARE @RC int
DECLARE @ownerId varchar(50)
DECLARE @type varchar(50)
DECLARE @dateFrom datetime
DECLARE @dateTo datetime
DECLARE @offset int
DECLARE @perPage int

SET @ownerId = '990042064' 
SET @type = 'voice' 
SET @dateFrom = '2018-05-16 00:00:00.000'  --'YYYY-MM-DD hh:mm:ss[.nnn]' 
SET @dateTo = '2018-08-14 23:59:59.000'  --'YYYY-MM-DD hh:mm:ss[.nnn]' 
SET @offset = 0 
SET @perPage = 50

EXECUTE @RC = dbo.IndexSearch @ownerId
                             ,@type
                             ,@dateFrom
                             ,@dateTo
                             ,@offset
                             ,@perPage
GO

And here is the code running from my app:

using (IDbConnection db = new SqlConnection(ConnectionStringHelper.ConnectionString))
{
    dbRecs = db.Query<IndexRec>(@"
    DECLARE @RC int
    DECLARE @ownerId varchar(50)
    DECLARE @type varchar(50)
    DECLARE @dateFrom datetime
    DECLARE @dateTo datetime
    DECLARE @offset int
    DECLARE @perPage int

    SET @ownerId = '990042064'
    SET @type = 'voice'
    SET @dateFrom = '2018-05-16 00:00:00.000'--'YYYY-MM-DD hh:mm:ss[.nnn]'
    SET @dateTo = '2018-08-14 23:59:59.000'--'YYYY-MM-DD hh:mm:ss[.nnn]'
    SET @offset = 0
    SET @perPage = 50

    EXECUTE @RC = dbo.IndexSearch @ownerId
                                 , @type
                                 , @dateFrom
                                 , @dateTo
                                 , @offset
                                 , @perPage
    ", commandTimeout: 120);
}

I've even tried running SSMS on a couple different machines, and I always get it in 1-5 seconds. And I have run the same query several times from the app, and it always times out.

Could there be anything in the script itself that causes the execution plan to be different? I am also using the same login from SSMS and my app.

Most likely parameter sniffing. [Slow in the Application, Fast in SSMS?](http://www.sommarskog.se/query-plan-mysteries.html) — Lukasz Szozda, Aug 14 '18 at 20:08
But if that was the case, wouldn't it start running slow from SSMS? — Matt Spinks, Aug 14 '18 at 20:15
Matt, the briefest of perusals of the excellent resource that Lukasz linked indicates that the answer to your question is "no." Suppose you have a look yourself. — BobRodes, Aug 14 '18 at 20:25
Two minutes then a timeout. Sounds like a network problem to me. Are you sure it is even able to connect to SQL server? — John Wu, Aug 14 '18 at 20:56

score 1 · Answer 1 · answered Aug 14 '18 at 20:27

As mentioned by Lukasz it may be Parameter Sniffing or it could be something else.

There are already lot of questions blogs to understand why !!

http://www.sommarskog.se/query-plan-mysteries.html

or you could try https://stackoverflow.com/a/12483089/1481690

Take a look at sys.dm_exec_sessions for your ASP.Net application and for your SSMS session. I will hazard a guess that at least one of your SET settings is different. This can contribute to different plans (ultimately this gets attributed to parameter sniffing) and the app side usually ends up worse off.

score 1 · Answer 2 · answered Aug 14 '18 at 20:33

Try the suggestions from this page: I think the parameter sniffing might be a red herring, but ARITHABORT solution might work. Try optiins(recompile), in any case. SQL Query slow in .NET application but instantaneous in SQL Server Management Studio

score 0 · Answer 3 · answered Aug 14 '18 at 20:27

The solution should be based on assigning the stored procedure parameters to local variables (please take a look for parameter sniffing). Below I've provided a link to an article that you might find useful: https://www.tangrainc.com/blog/2007/08/parameter-sniffing/

Why does the stored procedure run slow from code, but fast from SSMS?

3 Answers3