CODEWITHSUNDEEP
c#sql-serverperformanceentity-framework-6
cmxl
cmxl

Reputation: 775

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:

enter image description here

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.

Upvotes: 0

Views: 390

Answers (2)

cmxl
cmxl

Reputation: 775

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.

Upvotes: 1

IteratioN7T
IteratioN7T

Reputation: 356

I had the same issue, and offcourse SSMS will execute faster.

The problem is that all records are assigned to the respective POCO and its properties it iterates through every value until it produces a huge set of object.

What i did to address the issue :

I created a pagination in the sproc (sql level paging). No one can view 9000+ records at a time unless you are a CYBORG. So just get 10-100 records from the result set when the store procedure is executed.

Updated :

If you need to retrieve result set for creating an excel, i would suggest the possible ways to do so :

  • Create excel file directly from the sql, taking the result set from db to C# is time consuming.
  • If you still need it in server side then i would suggest you use EPPlus or any other well maintained third party libraries to generate excel for you at server side (EPPlus is what i use and it does not take longer than 5 secs for me excluding SP execution time)
  • Switching back to ado.net for report generation is viable, due to performance difference between EF and ADO
  • Optimize your query, refine it.
  • If your hands are still tied up by the client, then bear with the loading time you are currently experiencing ;)

Upvotes: 2

Related Questions