[EF CORE 9] exec stored procedure really more slow in EF CORE compare to SSMS

[julienGrd]

Question

Hello guys, i notice big difference in execution time when my call to some stored procedures through my DbContext rather than when i execute them with SSMS.

it took 1 second with SSMS and approx 17 second with the dbcontext.

i found several reference of this issue on internet, some was talking about the ARITHABORT parameter, and generally all was centering about the sniffing problem.

Anyway, nothing of the answers was actually very clear or acceptable, and i didnt find any solution (well, actually i find but was very not recommended to use them) to have my stored proecedure have the same performance than SSMS.

to be precise, there is one request with option(recompile) inside the stored procedure, in case it have impact

So the question, what is the good way to solve this problem ?

thanks for your answer !

Your code

a bit of code about the call and what is generated,
`
//The call
var _ = _context.SqlQuery<TBordUsage_Result>("EXEC @returnValue = [dbo].[GetTBordUsage] @pDateDeb, @pDateFin, @pServiceId, @pAntenneId", sqlParameters);

returnValue?.SetValue(parameterreturnValue.Value);

return _;

//generated sql, take 17second
declare @p7 int
set @p7=0
exec sp_executesql N'EXEC @returnValue = [dbo].[GetTBordUsage] @pDateDeb, @pDateFin, @pServiceId, @pAntenneId
',N'@pDateDeb smalldatetime,@pDateFin smalldatetime,@pServiceId tinyint,@pAntenneId tinyint,@returnValue int output',@pDateDeb='2025-01-01 00:00:00',@pDateFin='2025-12-31 00:00:00',@pServiceId=NULL,@pAntenneId=NULL,@returnValue=@p7 output
select @p7
`

Stack traces

Verbose output

EF Core version

9.0.1

Database provider

Microsoft.entityFramework.SqlServerCode

Target framework

.NET 9.0

Operating system

Windows 11

IDE

Visual Studio 2022 17.4

[AndriySvyryd]

How do you measure the time? Note that the context needs to do significant initialization on the first call after the app is launched, so at the very least start the stopwatch after calling context.Model. But preferably use BenchmarkDotNet to measure the average time between multiple executions.

[julienGrd]

@AndriySvyryd this time was mesured with sql server profiler to be at the lower level of the database and exclude all object mapping this kind of stuff.

i made a lot a test, each time the stored proc called with entity took 17 second and the same one call through SSMS took 1 second (with the same parameters of course), including the fact i call many time the same procedure in a row, each time i have exactly the same duration.

this topic speak exactly of this subject : https://learn.microsoft.com/en-us/answers/questions/90948/stored-procedure-slow-from-application-but-fast-in, but the answer of microsoft is not acceptable.

actually after few other research, i understand i can put ARITHABORT ON but not at the sotred procedure level, but more at entity level, what you guys think about that ?

thanks

[julienGrd]

I try different things but unable to have same performance than SSMS

• execute SET ARITHABORT ON before execute my stored procedure don't make the job as this other command is executed automatically just before the call of the sored proc (but after my SET ARITHABORT ON)

 _context.Database.ExecuteSql($"SET ARITHABORT ON");
 var _ = _context.SqlQuery<TBordUsage_Result>("EXEC @returnValue = [dbo].[GetTBordUsage] @pDateDeb, @pDateFin, @pServiceId, @pAntenneId", sqlParameters);

//result in Sql Server profile
SQL:BatchCompleted	SET ARITHABORT ON		

Audit Login	-- network protocol: LPC
set quoted_identifier on
set arithabort off //i think this put it back the setting
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language Français
set dateformat dmy
set datefirst 1
set transaction isolation level read committed

RPC:Completed	declare @p7 int
set @p7=0
exec sp_executesql N'EXEC @returnValue = [dbo].[GetTBordUsage] @pDateDeb, @pDateFin, @pServiceId, @pAntenneId
',N'@pDateDeb smalldatetime,@pDateFin smalldatetime,@pServiceId tinyint,@pAntenneId tinyint,@returnValue int output',@pDateDeb='2025-01-01 00:00:00',@pDateFin='2025-12-31 00:00:00',@pServiceId=NULL,@pAntenneId=NULL,@returnValue=@p7 output
select @p7	

i also try put this setting directly on the database level following this link https://blog.sqlauthority.com/2018/08/07/sql-server-setting-arithabort-on-for-all-connecting-net-applications/

But same, it change nothing, i think the setting is override because of the Audit Login -- network protocol: LPC which happen each time

[AndriySvyryd]

But same, it change nothing, i think the setting is override because of the Audit Login -- network protocol: LPC which happen each time

That might be happening because the connection is closed in-between the two calls. Try surrounding your code with these lines:

context.Database.OpenConnection();

context.Database.CloseConnection();

[julienGrd]

ok with these lines :

_context.Database.OpenConnection();

_context.Database.ExecuteSql($"SET ARITHABORT ON");

var _ = _context.SqlQuery<TBordUsage_Result>("EXEC @returnValue = [dbo].[GetTBordUsage] @pDateDeb, @pDateFin, @pServiceId, @pAntenneId", sqlParameters);

I see in sql server profiler the call to my stored procedure correctly preceded by ARITHABORT ON

But it actually change nothing to the performance... so either this parameter is not taken into account either its a totally different problem.

Do you have any other suggestions ?

[AndriySvyryd]

This doesn't seem to be related to EF, so I don't have enough expertise to be useful. Perhaps you can get help at dotnet/SqlClient#412 or dotnet/SqlClient#2919

[julienGrd]

Indeed it look more a problem in SqlClient than Ef Core, i will check that and see with them if its the case