LINQ TO SQL Performance



LINQ TO SQL

Performance

Contents

Introduction 3

Modelling Databases Using LINQ to SQL 3

Create New Data Model 3

Creating Entity Classes from a Database 3

Working with LINQ to SQL 3

LINQ to SQL Performance Issue 3

How to Improve the LINQ to SQL Performance 3

Performance Tips 3

Conclusion 3

1. Introduction

LINQ to SQL allows .NET developers to create SQL queries in our preferred .NET language syntax and work with strongly typed collection of objects as a return result. In simple terms, it allows us to write “Queries” in .NET language of choice to retrieve and manipulate data from the SQL Server database. DLinq (also known as LINQ to SQL) is designed to be non-intrusive to your application. Since DLinq is simply another component in the family, it is possible to migrating current solutions to DLinq. This article explains you about the optimization of LINQ to SQL Performance.

2. Modelling Databases Using LINQ to SQL

LINQ to SQL is an O/RM (object relational mapping) implementation that ships in the .NET Framework “Orcas” release, and which allows you to model a relational database using .NET classes. You can then query the database using LINQ, as well as update/insert/delete data from it. LINQ to SQL fully supports transactions, views, and stored procedures.  It also provides an easy way to integrate data validation and business logic rules into your data model.

2.1 Create New Data Model

LINQ to SQL Data model can be added to the solution by using the “Add New Item” option within Visual studio and selecting the “LINQ to SQL” item within it. See the below figure:

[pic]

Launched LINQ to SQL designer allow us to design model classes that represent a relational database. It creates strongly typed “DataContext” class that will have properties (represent each table) and methods for each stored procedure.

2.2 Creating Entity Classes from a Database

Entity Classes can easily be created only if we have database schema defined. On server Explorer within Visual Studio:

1. Establish a connection with database server to access the data.

2. Expand the Tables node from Server Explorer to retrieve the view the table list.

3. Select the required table says for example: Customer in our case.

4. Drag and drop the selected table into the left pane of ORM designer surface.

5. Similary drag and drop the stored procedure say: GetCustomer (City) that belongs to selected table (Customer), from the Server Explorer.

Below is a screen-shot of an LINQ to SQL ORM designer surface:

[pic]

DataContext: Right Pane shows the method that represents the stored procedures and left pane shows the properties that represent the table

3. Working with LINQ to SQL

In the case of LINQ to SQL, it is important to realize that .NET 3.5 creates a sql statement from LINQ query syntax calls a SQLDataReader to iterate over the results after execution. Now, consider the below example that uses LINQ syntax to retrieve the results (Customer detail) from database.

Above is the key part of main program in which connection string is assigned to the DataContext that created by drag and drop the table into the ORM (Object Relation Mapping). Using special LINQ syntax, the details of customer is queried and the results are stored in the Var type. Each record is retrieved by iterating the resultset.

4. LINQ to SQL Performance Issue

Consider the Example 1, we have already discussed that LINQ to SQL uses SqlDataReader internally to iterate the results. So, let us compare the behavior of both LINQ to SQL and SqlDataReader by getting the results from database.

Sample snippet of LINQ to SQL:

Please refer the Example 1.

Sample snippet using SqlDataReader:

It is a lot more code but that is how you do it using just SqlDataReader. Let us measure the time taken by both LINQ to SQL & SqlDataReader. I don’t think LINQ to SQL can be faster than SQLDataReader because ofcourse it uses SqlDataReader itself to do the job.

Below are the captured results when I executed above snippets to retreive the customer details from db.

|Execution Cycle |No. Of Query Executed |Rows Returned |Time taken by the LINQ Code to|Time taken by the SQLDataReader |

| | | |execute (Msec) |to execute (Msec) |

|1st |1 |5 |122 |3 |

|2nd |1 |150 |9 |6 |

|3rd |1 |150 |9 |6 |

|4th |1 |112 |8 |7 |

|5th |2 |4279 |3621 |3422 |

Note: Initial execution of LINQ query always consume more time than the SQLDataReader.

Based on the observation of above captured data, the performance of LINQ is slower than SQL DataReader.

Let us calculate no. of queries can execute per Sec by both LINQ & SqlDataReader:

Consider 2nd Cycle,

LINQ:

Query Executed (Q) = 1 ; Time taken by LINQ (TLQ) = 9 Msec.

Approx. queries can execute by LINQ = 111 Queries /Sec

SQLDataReader:

Query Executed (Q) = 1; Time taken by SQLDataReader (TSR) = 6

Approx. queries can execute by SQLDataReader = 166 Queries/Sec

Linq is running at low speed than the SQLDataReader object. Basic LINQ construction builds expression tree and builds the required SQL for each expression that results in poor performance. Let us see the overcome of this issue below.

5. How to Improve the LINQ to SQL Performance

The performance issue comes from building and parsing the LINQ queries. We can improve the performance by somehow prepare the statement for execution ahead of time, sort of like and compiling them. So, by using COMPILE feature of LINQ to SQL performance get close to 93% of native code.

Look at the sample snippet to compile the query:

Importantly, upon compilation, the query can be reduced to some kind of prepared statement. At this time any helper methods that need to be code-generated are also created. Upon binding we do the minimal query formatting for the constants and no jitting. The compiled query has lifetime specified by the user, so it lives exactly as long as it needs to.

Below are the captured results when I executed both Compiled and SQLDataReader queries:

|Execution Cycle |No. Of Query Executed |Rows Returned |Time taken by the Compiled |Time taken by the SQLDataReader |

| | | |LINQ Code to execute (Msec) |to execute (Msec) |

|1st |1 |5 |132 |3 |

|2nd |1 |180 |6 |7 |

|3rd |1 |180 |6 |7 |

|4th |1 |243 |7 |8 |

|5th |2 |4279 |3418 |3422 |

|6th |2 |4729 |3652 |3739 |

Note: Initial execution of query (Not compiled or compiled) always consume more time than the SQLDataReader.

Now see the benchmark from captured data:

Consider 2nd cycle,

LINQ:

Query Executed (Q) = 1; Time taken by Compiled LINQ (TLQ) = 6.

Approx. queries can execute by LINQ = 166 Queries /Sec

SQLDataReader:

Query Executed (Q) = 1; Time taken by SQLDataReader (TSR) = 7

Approx. queries can execute by SQLDataReader = 142 Queries/Sec

That’s pretty good, compiled queries got improvement in performance when compared to underlying providers. To better understanding better, create your own sample console application which should contain the implementation of compiled / not compiled LINQ & SQLDataReader. Measure the query-cost as well as row-cost.

6. Performance Tips

• Avoid many database roundtrips by implementing new decent method called DataLoadOptions, which gives you much more control over the lazy loading and prefetch functionality of DataContext class.

• Turn off LINQ to SQL Deferred loading only if we have a long list of resultsets. By default Deferred loading is turned on which cause a performance issue when we have huge recordsets.

• Use CompiledQuery class to increase the performance by compiling the query once and executing it several times in the application with different parameters.

• Set ObjectTracking property on the DataContext to false to instruct the DataContext not to track the changes to the entities only if not necessary to update the entities retrieved from the database i.e. displaying the record.

• Use QuerySyntax rather than Method Syntax. An object in QuerySyntax is declared once and used in where, orderby and select clause but the same object has to be re-declared each time in lambda expression. Example:

In QuerySyntax,

from person in persons

where person.Age > 12

orderby person.LastName

select person.LastName;

Here, person is declared once and used in the where, orderby, and select clauses.

In Method Syntax,

persons

  .Where (person => person.Age > 12)

  .OrderBy (person => person.LastName)

  .Select (person => person.LastName);

Here, we have to "redeclare" person each time in the lambda expressions.

Conclusion:

The purpose of this article was to provide a very brief introduction of LINQ to SQL performance when building a database-driven application. My hope is that the sample snippetin this article has convinced you that using LINQ to SQL can dramatically reduce the amount of code you need to write when building database-driven applications. In this article, I could provide only the briefest of introductions. For a much more detail about Performance of LINQ to SQL, visit Rico Mariani’s blog .

[pic]

-----------------------

EXAMPLE 1:

//Create an instance of CustomerDataContext class.

CustomerDataContext dc = new CustomerDataContext("Server=ServerName;uid=sa;pwd=sa;database=Sales");

//LINQ query to get the customer details those who are in Tirunelveli.

var customers = from cust in dc.customers

where cust.customercity == "Tirunelveli"

select cust;

//Iterate and print the customer details

Console.WriteLine ("CustomerId\tCustomerName\tAddress\tArea\tcity\tphone");

foreach (customer cust in customers)

{

Console.WriteLine (cust.customerid + "\t" + ……….+);

}

Example 2

SqlConnection con = new SqlConnection("Server=ServerName;uid=sa;pwd=sa;database=sales");

SqlCommand sqlCommand = null;

SqlDataReader sReader = null;

//Check the state of connection

if (con.State == ConnectionState.Closed)

con.Open();

//assign the Query to be execute against db.

sqlCommand = new SqlCommand("select * from customer where customercity='Tirunelveli'", con);

//Execute and return the reader object.

sReader = sqlCommand.ExecuteReader();

//Iterate the reader object to get the result.

Console.WriteLine("CustomerId\tCustomerName\tAddress\tArea\tcity\tphone");

while (sReader.Read())

{

Console.WriteLine(sReader["customerid"].ToString() + "\t" +

sReader["customername"] + "\t" + sReader["customeraddr"] + "\t" +

sReader["customerarea"] + "\t" + sReader["customercity"] + "\t" +

sReader["customerphone"]);

}

sReader.Close();

sqlCommand.Dispose();

Example 3:

CustomerDataContext dc = new CustomerDataContext("Server=SERVERNAME;uid=sa;pwd=sa;database=sales");

//Compile the query

public static Func

CustomerDetail = pile(

(CustomerDataContext dc, string city) =>

from cust in dc.customers

where cust.customercity == city

select cust);

//execute the compiled query

var customers = CustomerDetail(dc, "Tirunelveli");

//Iterate the retrieved record set

Console.WriteLine ("CustomerId\tCustomerName\tAddress\tArea\tcity\tphone");

foreach (customer cust in customers)

{

Console.WriteLine (cust.customerid + "\t" +

cust.customername + "\t" + cust.customeraddr + "\t"

+ cust.customerarea + "\t" + cust.customercity + "\t"

+ cust.customerphone );

}

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download