How to optimize linq query for speed?

Question

I have a linq query which fetches all records from the customer table to an observable collection as below:

customerList = new ObservableCollection<customer>(dbContext.customers);
dgRecords1.ItemsSource = customerList;

the list is bound to a datagrid. The customer table contains many fields nearly hundred. But I display only a few fields on the datagrid. My question is

whether bringing of only selected fields from the database using linq query increase the speed the customer screen?

I need to filter and sometimes delete records from this list.

Which is the best way to select few fields into observable collection, Can someone give some sample linq queries?

Solution 1

Tips to optimize speed:

• reducing columns reduces bandwidth required
• reducing rows, but introducing paging, reduced bandwidth by much more (usually)
• turning off change-tracking and identity-management (for example ObjectTrackingEnabled in LINQ-to-SQL) will reduce overheads post-processing the data
• using a pre-compiled query can sometimes help reduce pre-processing overheads

... but frankly, when we had this problem, we solved it "once and for all" by writing "dapper", and going old-school:

var list = connection.Query<CustomerViewModel>(
    "select {some specific cols} from Customers").ToList();

where CustomerViewModel is a simple POCO type not related to LINQ etc that just has the required values, for example:

class CustomerViewModel {
   public int Id {get;set;}
   public string Name {get;set;}
   // ...
}

This cuts out all unnecessary overheads, and is ideal when you just want to display data; additionally, the parameterization and materialization layers are very optimised (with strategy-caching, for optimum performance).

Solution 2

If you are only selecting required columns instead of all columns, you will have the improvement in the performance. You may use StopWatch to actually calculate the difference. It is always better to only select the required columns from the database, not all of them.

One more thing that you may consider is implementing pagination using Skip and Take. You may see: LINQ TO SQL GridView (Enhanced Gridview)

Comments

[Jodrell]

Well, obviously, limiting the number of columns in the result set will reduce the volume of data that needs to be transmitted.

[dutzu]

another thing you could try is Pagination. Or prefetching...