SQL server EntityFramework: how to handle a large set of data?

Question

Our application requests to handle a large set of data in SQL database table (i.e the system log table with 10,000 rows of data)
Our application will use WPF ListBox/ListView to display the system log. Normally, it will display 20 rows of log data at once.
We know that WPF ListBox will use UI virtualization to handle the performance issue in UI.

So far we plan to use Entity Framework version 1( vs2008) to model the system log table.
In general, Entity Query will get a collection of system log from the whole log table.
In our case, it will load 10, 000 log records into memory. And ListBox will data binding to this 10.000 collection of system logs.
 
Since each time, it will display only 20 rows of system log data, loading 10,000 record data into memory seems not make sense, it will hit performance.

Our question: how does Entity Framework handle this case? 
Can the Entity Framework loads only partial of 10,000 records into memory and when use scrolldown the listview, load another partial of 10.000 records?
What the technique is avaiable for us to use in Entity framework version 1?
thx!

Answer 1

Some comments:

1. 10,000 rows really isn't that much, especially with UI virtualization turned on - you should give it a try and benchmark before writing complicated code to deal with a performance issue that may not exist.

2. The EF will issue the queries that you give it, nothing more nor less. So, you could write code that will load the first, say 200 rows in the log table, and then, when the user scrolls down the list, lazily load additional rows by making additional calls to the database. There is a lot of information available for such data virtualization tasks online, once particularly useful project for you may be this:
http://www.codeproject.com/KB/WPF/WpfDataVirtualization.aspx

Which you should be able to hook up to the EF using queries involving Skip and Take: http://msdn.microsoft.com/en-us/library/bb738702.aspx.

HTH,
 Noam

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 2

Hi Noam,

Thanks for your info.

We have tried with 10,000 records in SQL Database table.
Using Entity Framework to load the entity collection always take 2 ms no matter we load whole collection
or load partial collection (with range condition). It is not bad at all! 
Thanks for pointing this out. We do not have many experience with DB.

Also with WPF UI Virtualization enabled, listBox binding with 10,000 entity objects seems pretty quick to display the view.
If we turn off the UI virtualization, we see the performace hit (display the view with a big delay).

One thing we notice, after we call Objectcontext to get the entity collection first time (no matter partial or whole collection),
next time call ObjectContext to get the partial collection, it alwasy take 0 ms. 
Does this mean that after first loading, whole entity collection already in memory?

This also raise another question, is it possible we do not load whole collection in memory?
can we only loading partial entity collection into memory?
Does the Skip will achieve this?

We have use SQL Compact edtion database, it does not support Skip.
We have use Embedded Window XP, so the memory useage is also our concern.
thanks,
Jane

Answer 3

Hello Jane,

When you refer to "entity collection" I am not sure you refer to an actual EntityCollection<T> object or simply to any number of entities from the same entity set.

In general, when you use the default MergeOption for an ObjectQuery/ObjectSet, which is AppendOnly, executing the query will still hit the database every time, but as the results are merged into the context we will never materialize twice an objects that has already been loaded. I haven't seen you code, so I am just guessing, but I imagine the fact that the objects are not being materialized the second time might be accounting for the performance difference you are seeing. In any case, if you want to know exactly what entities are loaded at a certain point it time, you can use the ObjectStateManager.GetObjectStateEntries method.

Regarding partial loading or paging, while it is usually possible to do server-side paging in queries with SKIP and TAKE like Noam indicated, it is true that SKIP is not supported with EF in SQL CE. I am not aware of workarounds for this.

One possible alternative that comes to mind is the following project that one of our team member created:

http://code.msdn.microsoft.com/EFLazyLoading

It uses some interesting lazy loading techniques that for instance will only load minimal data about an entity until the value of any property is requested.

Hope this helps,
Diego

---

This posting is provided "AS IS" with no warranties, and confers no rights.