0

I'm using Entity Framework 5, and I'm executing a query that returns a relatively large resultset, around 30,000 rows. The query itself executes very fast, taking only around 700ms.

After building the linq query I call query.Load();, and then return DataContext.Set<MyType>.Local as an ObservableCollection to the caller. The results are displayed in an editable grid.

The problem is with the query.Load(); call - it's taking around 60 seconds, which is way too long. I'm guessing that this is where EF transforms the results into .NET objects? I've tried profiling, but it won't go any deeper than IQueryable.Load :/

Any ideas how can I improve performance?

BTW, I tried upgrading to EF6 to see if that helped any, but it actually got worse, wih query.Load(); typically taking 100 seconds!

Cocowalla
  • 13,822
  • 6
  • 66
  • 112
  • 2
    Why do you load 30k rows into a DataGrid? What is the indend for the user to view so many rows? Is it not possible to paginate the grid? – Michael Mairegger Nov 11 '13 at 20:35
  • 1
    I know, I know... we have pagination in the application already, and it there are no performance issues. But the users don't like it - they want to view all rows at once. There are no memory issues, and thanks to UI virtualisation there are no issues with the grid either. Just with EF :/ – Cocowalla Nov 11 '13 at 22:08
  • 1
    If the user do not like pagination you can load 200 Entities and at that time when the user scrolls to the end of the grid then you can load the next 200 consecutive entities. So you have that the user sees no real pagination (like next/previous page) and you can load less data. – Michael Mairegger Nov 12 '13 at 07:22
  • 2
    @xxMUROxx the problem is that EF currently has an issue with materialization (see: http://entityframework.codeplex.com/workitem/1781). It is more visible when you fetch a lot of rows, but it is equally there when you fetch small sets. – Frans Bouma Nov 12 '13 at 14:03

1 Answers1

0

The Linq to Entities query is not executed until the results are needed. That's when the computational cost is "paid". Until then, the query is just a query. As Stefan suggested, you should try add some filters, or at least a paging mechanism using .Skip and .Take methods.

You could also check this MSDN article about linq paging.

Hope I helped!

Pantelis Natsiavas
  • 5,293
  • 5
  • 21
  • 36