CODEWITHSUNDEEP
c#sql-serverentity-framework
aw04
aw04

Reputation: 11177

Enumeration of EF stored procedure results

I'm calling a simple stored procedure that returns around 650 rows. There are several joins and the procedure takes about 5-6 seconds. No problem.

Enumerating the results, however, is taking about a minute to complete.

using (var context = new DBContext())
{
    var results = context.GetResults(param); //5-6 seconds
    var resultList = results.ToList(); //1 minute+
}

I don't use Entity Framework much, but this seems abnormal. Am I doing something wrong? Is there something I can look at to speed this up? The table is huge, but the way I read it, this code should only be enumerating the 650 results... which should take no time at all.

Note: Not sure if this is related, but the time it takes to select all rows from said table is about the same (around a minute)

Upvotes: 1

Views: 230

Answers (2)

aw04
aw04

Reputation: 11177

The solution to my problem was to disable parameter sniffing by creating a copy of the input parameter.

alter procedure dbo.procedure
    @param int
as
begin
    set nocount on;

    declare @paramCopy int
    set @paramCopy = @param

    ...

Upvotes: 1

Corey Adler
Corey Adler

Reputation: 16137

Based on your recent edit, I have an idea of what's happening. I think that the .GetResults() call is simply getting the query ready to be run, utilizing deferred execution. Only when you are calling .ToList() in the next line is it actually going out and trying to build the entities themselves (hence the time difference).

So why is it taking so long to load? That could be for a number of reasons, including:

  1. You might have lazy loading disabled. This will cause all of the records to be fully loaded, with all of their respective navigational properties as well, and have all of that be tracked by the DbContext. That makes for a lot of memory consumption. You might want to consider turning it on (but not everyone likes having lazy loading enabled).

  2. You are allowing the tracker to track all of the records, which takes up memory. Instead of this, if the data you're grabbing is going to be read-only anyway, you might want to consider the use of AsNoTracking, like in this blog post. That should reduce the load time.

  3. You could be grabbing a lot of columns. I don't know what your procedure returns, but if it's a lot of rows, with lots of different columns, all of that data being shoved into memory will take a loooong time to process. Instead, you might want to consider only selecting as few columns as needed (by using a .Select() before the call to .ToList()) to only grab what you need.

Upvotes: 0

Related Questions