Executing stored procedure takes incredible amount of time in EF 6
I am running EF 6 (v6.2.0) in ASP.NET MVC 5.
When executing a certain stored procedure via EF 6's SqlQuery<T>() function, I have to wait for about 2 minutes (!) to get the result in memory.
The stored procedure takes about 9-12 seconds in the database due to some complex calculations and is called with 11 parameters:
exec sp_Calculation @q, @y, @gn, @gesa, @rg, @cl, @yc, @vlv, @vlb, @ugv, @ugb
The result is about 2.1 MB of data (~9000 rows, 49 columns).
Total execution time: 00:00:11.711
In code I call it like this:
dbContext.Database.Log = s => Trace.Write(s);
return await dbContext.Database.SqlQuery<CalculationResult>("exec sp_Calculation @q, @y, @gn, @gesa, @rg, @cl, @yc, @vlv, @vlb, @ugv, @ugb", parameters).ToListAsync(token);
Trace:
exec sp_Calculation @q, @y, @gn, @gesa, @rg, @cl, @yc, @vlv, @vlb, @ugv, @ugb
-- @q: 'null' (Type = Int32, IsNullable = false)
-- @y: '1101' (Type = Int16, IsNullable = false)
-- @gn: 'null' (Type = Int32, IsNullable = false)
-- @gesa: '1' (Type = Byte, IsNullable = false)
-- @rg: 'null' (Type = Int32, IsNullable = false)
-- @cl: '4' (Type = Byte, IsNullable = false)
-- @yc: '17' (Type = Int16, IsNullable = false)
-- @vlv: 'null' (Type = Int16, IsNullable = false)
-- @vlb: 'null' (Type = Int16, IsNullable = false)
-- @ugv: 'null' (Type = Int16, IsNullable = false)
-- @ugb: 'null' (Type = Int16, IsNullable = false)
-- Executing asynchronously at 19.07.2018 18:27:23 +02:00
-- Completed in 114479 ms with result: SqlDataReader
My first guess was the network as a bottleneck, but calling the stored procedure on the web server via SSMS is also very fast. So network should not be the problem.
Here is the call stack from dotTrace with the big bottleneck:
What is weird is the extremely long execution times of the native assemblies.
Can someone clarify what exactly is going on there and how to resolve the issue?
EDIT:
I just found a question with a similar problem and will try to find out some more about it. Maybe it is the network.
EDIT 2:
I do need all the data in memory due to some preprocessing before creating a csv file from it. The bottleneck seems to be in the SNINativeMethodWrapper. I do not need help to perform my task with other libraries. I just want to get the data faster in memory.
The issue was heavy load between the database and linked servers. The native API had a hard time to push the whole record set through the SQL Network Interface. So there is no problem with the code itself.
Everything works as fast as expected when load between linked servers was low.