LINQ is one of the most exciting enhancements made in the Microsoft.NET languages in the recent years.
Essentially, it’s a new way to represent different data models in OOPs notation.
While working extensively with LINQ in last couple of years on mission critical .NET projects, I’ve observed the following five performance enhancement possibilities.
1. Lazy Loading & Eager Loading
Lazy Loading:
By default LINQ loads the related objects lazily. Lazy loading means Loading Object with that associate Objects.
For example, If I have two tables called User (UserId, Name) and User_Car (UserId, CarID, CarBrand), whenever we initiate a User object, it will automatically load User_Car object details also as shown in the code below.
var userObj = from user in DataContext.User
Select user;
The above query returns the User Object with the details of associate object of User_Car. E.g.
Int carId= userObj.User_Car.CarID;The above statement will fire two queries on database.
First one will be fired when we write query on User Object. E.g.
var userObj = from user in datacontext.User
Select user;
(Select ID, Name from User)And second query will fired when we try getting the value from User_Car object.
E.g
Int carId=userObj.User_Car.CarID;
(Select User_Car.CarID, User_Car.User_ID, User_Car.CarBrand
from User_Car where User_Car. User_ID =@userID)This leads to more round trips to the database. Instead, we can opt for Eager loading.
Eager Loading:
Defining associate Objects which are require in advance.So when LINQ execute query then it fetch only that objects.And LINQ fire a single on database.
For Example,
DataContext dc= new DataContext ();
DataLoadOptions options = new DataLoadOptions ();
option.Loadwidth<User> (U => U.User_Car)
dc.LoadOptions = options; (Here, User_Car object as pre-fetch object. So when we write query on User Object then automatically User_car records get fetch.)
e.g
var userObj = from user in DataContext.User
Select user;SQL Query is,
(Select User.ID, User.Name, User_Car.CarID, User_Car.User_ID,
User_Car.CarBrand from User join User_Car on
User.ID=User_Car.User_ID)And when we write
Int carId= userObj.User_Car.CarIDIt fetches the record from memory only. So Eager Loading reduce the Database round Trips and Improve the performance.
2. Use DataLoadOption.AssociateWith ()
In Eager Loading, When association of Master object and child object are based on non PrimaryKey then use DataLoadOption.AssociateWith (). It will optimize SQL query and give better Performance.
3.Set ObjectTrackingEnabled = False
LINQ objects are use only for data retrieval (No insert, update, delete operations are required) then make this flag off.It will avoid Identity Field(Auto-Increment Field) management process.
4. Set Delay Loaded = True
Field which is not in use or huge in size of contain for particular object then set this property as True.So we access the field then only data get load.
5. Use Compiled Query
Compiled Query skip the steps of generating Expression Tree and generating SQL Query of Process Life Cycle.When First time query get executed, the expression tree and SQL query is generated and next time system will use same for execution.
e.g
Public static Func<DataContext, int, IQueryable<User>>
UserByID=
CompiledQuery.Compile ((DataContext db, int UserID) =>
From U in db.User where U.ID == UserID select U);When we call Function first time the expression tree and SQL query will be generate and next time system will use the same to execute query on database.
After applying one or more of the above tips, a LINQ application will definitely perform better and thus will ensure faster response times to business needs.
