Filtering a datagrid - efficient way

While filt开发者_C百科ering existing data of a datagrid, what is more efficient:

- filtering the existing data collection OR
- querying database with filter clauses

Okay, so you've pulled down a subset of data from the database and you have presented it to the user. All is great in the world. Now the pesky user wants to apply further filtering to the data. Assuming that we're not accessing massive data sets through cursors and the such like, then the data is in memory already. I'd be very tempted to filter them there.

Provided the data isn't changing rapidly and that the chance of a certain degree of staleness is acceptable then there's no good reason to go back to the database to extract a subset of what you already have. You've already pulled the data over the wire once, why do it again.

If the data does need to be bang up to date then forget everything I've just said and go ask the DB for it.

The idea is probably somewhere in between that would be marshalled by your data access layer. Here you'll monitor data age and ensure that even if the user just keeps altering the filtering of the same data set for hours, it's periodically refreshed from the database. The request for the filtered data should also be presented to this layer, that way your app is blind to whether it's actually fetched or just served from memory. That separation of concern will stand you in good stead moving forward should you ever decide that one or the other approach (memory or database) is actually always better as you'll be able to change the underlying code and leave your app unchanged.

In general, filtering on the database is more efficient, because the database not only has less data to return, but normally it has also less data to transact (eg. it can use indexes to reduce amount of records to be processed). Furthermore, in most infrastructures is the database not on the same server as the client and therefore there are also other resources concerned from returning unfiltered data. Generally, the more data has to be processed, the more efficent is filtering on the database.

But there could be clearly also circumstances that makes filtering in code/client more efficient, mostly if the application has information already at hand that the database first must query.

But all this is very vague, in the end it is dependent on your application. If you say you have a datagrid and the user makes many changes to filters concerning the same static data, filtering on the the database will lead to a lot unnecessary traffic and workload on the db server wheras unfilterd cached data on the client would increase efficiency.

Definitely filtering the existing data collection.

There are several solutions to filtering existing DataGridCollection, but in my opinion, the best solutions are the ones which uses only styles with the standard WPF DataGrid control without inventing a new inherited DataGird type or depending on another third-party control. The followings are the best solutions I found:

• Option 1: which I personally use: Automatic WPF Toolkit DataGrid Filtering
• Option 2: Auto-filter for Microsoft WPF DataGrid