I have an API written in C# using Entity Framework in combination with SQL Server. On the SQL Server side, I have several procedures that I invoke through Entity Framework from a controller.
Here is an example of one procedure call:
var result = context.MyProcedure.FromSqlRaw(
"EXECUTE [MyDB].[dbo].[MyProcedure] @Param1, @Param2, @Param3, @Param4",
new SqlParameter("@Param1", param1),
new SqlParameter("@Param2", param2),
new SqlParameter("@Param3", param3),
new SqlParameter("@Param4", param4 == "all" ? DBNull.Value : param4)
).ToList();
return Ok(new { result });
Previously, calls to stored procedures were executed in ~2 seconds, but now they take ~20 seconds. At the same time, operations such as data filtering, retrieval by ID, and authorization are performed quickly.
After conducting a test without using Entity Framework in a new console application, I found that the procedure executes within the expected 2-3 seconds. However, replacing the call through Entity Framework with similar code in ASP.NET MVC leads to the same delay of ~20 seconds. It seems that the delay is associated with the process of fetching data after the procedure call.
How can the performance of such calls be improved? Are there ways to optimize or alternative methods of invoking stored procedures through Entity Framework to reduce execution time?
As described above, I tried running the procedure from the same database through an empty console application without Entity Framework, and it worked very quickly.