A fine site

Optimization Of LINQ

Leave a comment

Optimization Of LINQ

Tip #1: Slim Down Your Queries with Projections
Let’s say I have a Customer table with 20 fields, but I’m only interested in three: FirstName, LastName, and Email. What do you suppose LINQ to SQL will do if I execute this code?

var customer =
        (from cust in dataContext.Customers
         where cust.ID == customerID
         select cust).Single();
var customerLite = new {

The answer is it’ll happily issue a ‘SELECT’ statement asking for all 20 fields in the Customer table so it can populate every field of the Customer entity it gives me back (even though I only care about three fields). That seems a bit wasteful, don’t you agree?

Now suppose I re-wrote the code to look like this:

var customerLite =
        (from cust in dataContext.Customers
         where cust.ID == customerID
         select new {

This time LINQ to SQL will issue a ‘SELECT’ statement asking only for the three fields I care about because I gave it enough information before it executed the query to know specifically what I wanted.

The only downside I’ve found with using projections is losing the ability to use the LINQ-generated entity classes (like the Customer class for customer records). This means I lose some entity-dependent features like deferred loading of child entities and state tracking. Since I tend to use projections mostly when I’m pulling lots of records for display or analysis purposes (and not for updating data), I’m generally okay with that tradeoff.

Tip 2: Optimize Your Optimistic Concurrency Checking
It’s time to invoke the hiking analogy again. Concurrency checking is another case where LINQ to SQL will automatically prepare for a 14er hike unless you tell it you’re just doing a 15-minute nature walk.

Unless we tell it not to, LINQ to SQL will help us ensure that multiple users working with the same record don’t overwrite each other’s changes. It does this by enabling optimistic concurrency checking on all database updates by default and alerting us if it detects we’re trying to update a record that has changed since we last retrieved it.

To see how LINQ to SQL does this, take a look at the entities LINQ to SQL generates for you. You’ll see that each property on an entity has an UpdateCheck attribute, which has possible values ofAlwaysNever, and WhenChanged.

By default, UpdateCheck is set to Always, which means LINQ to SQL will check the value you’re saving for that property against what’s in the database. (The checking comes in the form of extra qualifiers added to the ‘WHERE’ clause of your update query.) If they’re different, LINQ to SQL assumes the record has changed since you last retrieved it and throws a concurrency exception.

Unless you need optimistic concurrency checking in your application, I recommend setting UpdateCheck to Never for the properties on your entities. This will greatly speed up your queries by eliminating all the extra checking that is done during updates. For many applications, this “last write wins” type of approach is perfectly acceptable.

If, however, you do need optimistic concurrency checking in your application, then I suggest adding a column of type timestamp to each of your tables in SQL Server that you want checked. I often do this by adding a column called “RowVersion” to my tables. Then when LINQ to SQL generates an entity class from that table, it will automatically set the UpdateCheck property to Never for all properties in the entity and use the timestamp column in the table for concurrency checking instead.

Avoid to put all the DB Objects into One Single Entity Model

Entity Model specifies a single unit of work, not all our database. If we have many database objects that are not connected to one another or these(log tables, objects used by batch processes,etc.) are not used at all. Hence these objects are consuming space in the memory and cause performance degrades. So try to make separate entity models of related database objects.

Disable change tracking for entity if not needed

Whenever you retrieve the data only for reading purpose, not for modification then there is no need of object tracking. So disable object tracking by using MergeOption as below:

NorthwindDataContext context = newNorthwindDataContext() context.tblCities.MergeOption = MergeOption.NoTracking; 

This option allow us to turn off the object cache and unnecessary identity management of the objects.

Choose appropriate Collection for data manipulation

In linq we have Var, IEnumerable, IQueryable, IList type collection for data manipulation. Each collection has its importance and performance impact on the query, so beware of using all these collection for data manipulation. For learning difference among all these collection refer my articles IEnumerable VS IQueryableIEnumerable VS IList and Var VS IEnumerable.

Avoid using Contains

In LINQ, we use contains method for checking existence. It is converted to “WHERE IN” in SQL which cause performance degrades.

Use Compiled Query wherever needed

Make a query to compiled query if it is frequently used to fetch records from the database. This query is slow in first time but after that it boost the performance significantly. We use Compile method of CompiledQuery class for making compiled query.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s