Linq to entities is very slow using .Take() method

Question

I have a table of 200,000 record where I am getting only the top 10 using .Take() but it is taking about 10 seconds to get the data.

My question is: does the .Take() method get all the data from the database and filter the top 10 on the client side?

Here is my code:

mylist = (from mytable in db.spdata().OrderByDescending(f => f.Weight)
                                    group feed by mytable.id into g
                                    select g.FirstOrDefault()).Take(10).ToList();

spdata() is a function Import from stored procedure.

Thanks

If you're curious what it runs against the database you should hook a profiler up to your DB & check the logs, that's the best way to find out. — Chris, Dec 18 '12 at 13:39
If spdata() returns the 200,000 then yes, it will do it on the client side. — Pablo Romeo, Dec 18 '12 at 13:41
In addition to Chris' method, you could also try Linqpad and switch to the SQL view after writing your query to see the generated SQL, or put a breakpoint (with this method http://stackoverflow.com/questions/1412863/how-do-i-view-the-sql-generated-by-the-entity-framework) to see what is generated as the program executes. — Aaron Newton, Dec 19 '12 at 11:53

score 9 · Accepted Answer · answered Dec 18 '12 at 13:42

The stored procedure probably returns a lot of data to the client which is very slow. You cannot remote a query to an sproc. That would be possible using a view or a table-valued function.

There's no way to use an sproc in a query. You can only execute it by itself.

Your intention probably was to execute the Take(10) on the server. For that to work you need to switch to an inline query, a view or a TVF.

score 7 · Answer 2 · answered Dec 18 '12 at 13:41

7

The extension method Take does not fetch all the results from the database. That is not how Takeworks.

However your db.spdata() call probably does fetch all rows.

answered Dec 18 '12 at 13:41

Mark Byers

811,555
193
1,581
1,452

CoffeeCode · Answer 3 · 2012-12-18T13:59:17.540

I'm not 100% sure but as I remember you get an IEnumerable result when you call an SP using EF DataContext...

There are a couple of was to optimize the performance:

Pass the search criteria s as SP params and do the filtering in the stored procedure.

Or if you have a quite simple query in the SP where you are not declaring any variables and where you are just joining some tables then:

Create an indexed view where specify the query that you need and call the Take method on it.
What this will give you? You can map to the created view and EF will now be returning an IQueryable result and not an IEnumerable. This will optimize the sql command and rather the receiving all of the data and then taking the 10 elements that you need, a sql command that just retrieves the 10 elements will be formed.

I also advice you to see what is the deference between IEnumerable vs IQueryable.

Guffa · Answer 4 · 2012-12-18T14:31:10.593

0

It does, because you are sorting the data before grouping, which is not possible to do in SQL.

You should use an aggregate to get the highest weight from each group, then sort the weights to get the ten largest:

mylist = (
  from mytable in db.spdata()
  group feed by mytable.id into g
  select g.Max(f => f.Weight)
).OrderByDescending(w => w).Take(10).ToList();

edited Dec 18 '12 at 14:31

answered Dec 18 '12 at 14:05

Guffa

687,336
108
737
1,005

it is giving an error Error "A local variable named 'g' cannot be declared in this scope because it would give a different meaning to 'g', which is already used in a 'parent or current' scope to denote something else" – Alex Dec 18 '12 at 14:23
@AliIssa: Right, it has to be a different variable there. I changed it. – Guffa Dec 18 '12 at 14:31

Linq to entities is very slow using .Take() method

4 Answers4