Optimizing LINQ Queries using DataLoadOptions

Introduction

Thanks to everyone who has knowingly / unknowingly helped me to become a Microsoft MVP, hope I can live up to it.

In this section, we will understand the round trip issues of LINQ and how we can overcome the same using ‘DataLoadOptions’. One of the biggest issues with LINQ to SQL is that it fires SQL query for every object which has a huge impact on performance. In this article, we will see how we can get all data in one SQL query.

Catch my videos for WCF, WPF, WWF, LINQ, SilverLight, Design patterns, UML and lot on http://www.questpond.com.

LINQ Basics

This article assumes that you have basic knowledge of how entity objects can be flourished using LINQ. In case you are not aware of basics of LINQ to SQL mapping, you can read my article to understand the basic LINQ concepts.

Customer, Addresses and Phones LINQ Entities

First let’s try to understand how LINQ queries actually work and then we will see how round trips happen. Let’s consider the below database design where we have 3 tables -- customer, addresses and phone. There is one-many relationship between customer and addresses, while there is one-one relationship between address table and phones.

We have created three entities as per the table design:

  • ClsCustomerWithAddresses
  • ClsAddresses
  • ClsPhone

We have defined the relationships between them using ‘EntitySet’ and ‘EntityRef’.

Click to enlarge image

To fill the entity objects with data from table is a 5 step process. As a first step, the datacontext connection is created using the connection string, LINQ query is created and then we start browsing through customer, address and phones.

Click to enlarge image

Analyzing the LINQ SQL Round Trips

Ok, now that we have analyzed that it takes 5 steps to execute a LINQ query, let’s try to figure out on which step the LINQ query actually fires SQL to the database. So what we will do is run the above LINQ code and analyze the same using SQL profiler.

Just so that we do not catch a lot of SQL Server noise, we have only enabled RPC and SQL batch events.

Now when you run the query, you will find the below things:

  • The execution of actual SQL takes place when the foreach statement is iterated on the LINQ objects.
  • The second very stunning thing you will notice is that for every entity, a separate query is fired to SQL Server. For instance, for customer one query is fired and then separate queries for address and phones are fired to flourish the entity object. In other words, there are a lot of round trips.

Click to enlarge image

Avoiding Round Trips using DataLoadOptions

We can instruct LINQ engine to load all the objects using ‘DataLoadOptions’. Below are the steps involved to enable ‘DataLoadOptions’.

The first step is to create the data context class:


See full detail: http://www.codeproject.com/KB/linq/OptimizingLINQ.aspx

Comments

Post a Comment

Popular posts from this blog

Very fast test data generation using exponential INSERT

Image
Introduction This article (my first) will describe an algorithm that enables a large amount of data to be generated very quickly using a SQL query. The test data can be static or incremental, such as “Item Name” and “Item ID”, respectively, as shown below: Background One of the tasks I did in a project involves generating a testing table with 103,680,000 records. The conventional method of data generation would take a month; hence, a fast method of data insertion was required. The new method took only 5 hours. Using the code Conventional method – Sequential INSERT The conventional way of generating a list of numbers from 0…100000 would be using a loop and an INSERT statement as follows: CREATE TABLE #tempTable([Item ID] [ bigint ], [Item Name] nvarchar ( 30 )) DECLARE @counter int SET @counter = 1 WHILE (@counter 100000 ) BEGIN INSERT INTO #tempTable VALUES (@counter, ' Hammer' ) SET @counter = @counter + 1 END SELECT * FROM #tem...
Read more

Basic concept and fundamentals of ASP.NET MVC (Model View Controller) Architecture

Image
This is basic concepts and fundamentals of ASP.NET Model View Controller (MVC) architecture workflow. MVC is stands for MODEL VIEW CONTROLLER. ASP.NET MVC is an architecture to develop ASP.NET web applications in a different manner than the traditional ASP.NET web development. Web applications developed with ASP.NET MVC are even more SEO (Search Engine) friendly. Microsoft .Net Framework 3.5 is minimum requirement to develop ASP.net MVC application.   MVC (Model View Controller) Interaction with Browser Like a normal web server interaction, MVC application also accepts requests and responds to the web browser in the same way.   Inside MVC Architecture The entire ASP.NET MVC architecture is based on Microsoft .NET Framework 3.5 and in addition uses LINQ to SQL Server. What is a Model? MVC   model   is basically a C# or VB.NET class A   model   is accessible by both   controller   and   view A   model   can be used to pass data ...
Read more

MVC Architecture Model In ASP.NET

Image
When developing ASP.NET applications the need of reducing code duplication increases along with their complexity. This is because testing and performing changes become very difficult tasks when having many different sources of code with the same functionality. Model View Controller architecture (or pattern) allows us to separate different parts of our applications into tiers to fulfill this need. MVC Overview Model View Controller architecture aims to separate an application into three parts: Model:  It is the business logic of an application. From an object oriented perspective it would consist of a set of classes that implement the critical functionality of an application from a business point of view. View:  It can consist of every type of interface given to the user. In ASP.NET the view is the set of web pages presented by a web application. Controller:  This part of the architecture is the most difficult to explain, hence the most difficult to implement in ...
Read more