Details
I have a WPF Datagrid which loads from Linq-sql query Datacontext data item.The result set contains around 200k rows and its it very slow loading them,sorting,filtering etc. What is the simple and easy way to improve the speed?
Couple of things I saw searching are Scrollview,Data virtualization etc people also talk about Paging,Profiling etc
Loading data: 200k rows is a lot of data that no one (user) wants to see in one place. It will definitely reduce your UI user experience. So your best bet is to filter your data just to reduce the amount of it (for example do not show closed orders, just show the open ones). If you can't do so, you should use Virtualization. I didn't see any applications that use pagination in order to show data (Of course except in web). Most of the time it isn't such a good approach. But if you are talking about a type of data that is like search engines results you must use it. But keep in mind that most users won't exceed page 10 in search engines results.
Filtering: I would suggest doing it on your server side for such a huge amount of data (SQL Server here), or as I said first filter the whole 200k to reduce the amount on server side and then filter it (for user) in order to find something, on the client side. You might also find the following link helpful:
Sorting: Again I would suggest server-client solution but you might also find following links helpful:
Many people don't use default SortMemberPath of WPF datagrid just because it uses reflection on every single record and this will highly reduce the performance of the sorting process.
Hosein
Here is a very good sample of Data Virtualization (Not UI Virtualization):
http://www.codeproject.com/KB/WPF/WpfDataVirtualization.aspx
Althogh it doesn't support the LINQ IQueryable objects directly but you can use this sample as it is. Of course I'm now wokring to improve it to work with IQueryable objects directly. I think it's not so hard.
Wow, 200K rows is a lot of data. Paging sounds like a good idea. Try to decide how many rows per page you want, say 50. Upon showing the screen the first time, show only the first 50. Then give the user the option to move between pages.
Sorting might be trickier this way though.
Virtualization can be another option, sadly, I have yet to work with virtualization.
Sometimes you may have only ~30 visible rows to load and if those rows + whatever columns are expensive to load due to their number and complexity of the each cell (it's template, or how many wpf elements it has), none of the above comments really make a difference. Each row will take it's sweet time to load!
What helps is to stagger or lazily load each row on the UI, so that the user sees that the ui is doing something rather than just freezing for ~10+ seconds.. For simplicity, assuming that the datagrid ItemSource="{Binding Rows}", and Rows is IEnumerable, where Row is some class you created : add a property IsVisible to Row (don't forget to raise property changed, of course)
you could do something like this:
private void OnFirstTimeLoad()
{
Task.Factory.StartNew(() =>
{
foreach (var row in ViewModel.Rows)
{
/*this is all you really need,
note: since you're on the background thread, make sure IsVisible executes on the UI thread, my utils method does just that*/
myUtils.ExecuteOnUiThread(() => row.IsVisible = true);
/*optional tweak:
this just forces Ui to refresh so each row repaint staggers nicely*/
Application.Current.Dispatcher
.Invoke(DispatcherPriority.Background, (SendOrPostCallback) delegate { }, null);
}
});
}
oh, and don't forget to trigger in XAML:
<DataGrid.ItemContainerStyle>
<Style TargetType="{x:Type DataGridRow}">
<Setter Property="Visibility" Value="{Binding Path=IsVisible, Converter={StaticResource BoolToVisibility}}"/>
........
Is your datagrid inside a Scrollviewer? Because it does the entire datagrid (all the rows) to be rendered. I had a similar problem and removing the scrollviewer solved the problem with the slow loading.
Question that you should be asking is:
