Several methods for improving the efficiency of the SQL statement using the LINQ to SQL statement

In this forum, I will talk about several ways to improve the efficiency when using LINQ to SQL.

(This forum will be updated constantly and everyone will learn and make progress together)

 

(A log attribute under the datacontext instance can be seen by setting this attribute. The SQL code generated by the LINQ statement will be used in the code below)

1. latency.

(1) delayed execution and immediate execution: delayed execution is not only exclusive to LINQ to SQL, but also excludes this feature in almost all LINQ, it can improve efficiency so that we can execute the appropriate query at the appropriate time. If you do not want to use this feature in some special cases, you can use tolist () or toarray () method To make the query run immediately.

Example 1: delayed execution

NorthwndDataContext dc = new NorthwndDataContext();            dc.Log = Console.Out;            var query = from c in dc.Customers                select c;            Console.WriteLine("this is deferred execute.");            foreach (var item in query)            {                Console.WriteLine(item.CustomerID);                Console.ReadKey();            }

Result:

This is deferred execute.
Select [t0]. [customerid], [t0]. [companyName], [t0]. [contactname], [t0]. [contactt
Itle], [t0]. [address], [t0]. [City], [t0]. [region], [t0]. [postalcode], [t0]. [coun
Try], [t0]. [PHONE], [t0]. [Fax]
From [DBO]. [MERs] as [t0]
-- Context: sqlprovider (sql2008) model: attributedmetamodel build: 4.0.30319.1

Alfki

Example 2: execution without delay

NorthwndDataContext dc = new NorthwndDataContext();            dc.Log = Console.Out;            var query = (from c in dc.Customers                select c).ToList();            Console.WriteLine("this is not deferred execute.");            foreach (var item in query)            {                Console.WriteLine(item.CustomerID);                Console.ReadKey();            }

Result:

Select [t0]. [customerid], [t0]. [companyName], [t0]. [contactname], [t0]. [contactt
Itle], [t0]. [address], [t0]. [City], [t0]. [region], [t0]. [postalcode], [t0]. [coun
Try], [t0]. [PHONE], [t0]. [Fax]
From [DBO]. [MERs] as [t0]
-- Context: sqlprovider (sql2008) model: attributedmetamodel build: 4.0.30319.1

This is not deferred execute.
Alfki

(Pay attention to the location marked in yellow to see the difference between latency and normal execution)

(2) Delayed loading and immediate loading: in C # code, object instance attributes are often used to establish relationships with other objects (customer. order), but sometimes we need a complete object chain information at the same time, often as long as the current object information, so we can achieve different requirements through delayed loading and immediate loading.

(Implemented through the dataloadoptions instance)

Instance 1: delayed Loading

var query = (from c in dc.Customers                select c).ToList();            foreach (var item in query)            {                Console.WriteLine(item.CustomerID);                Console.WriteLine("this is deferred load.");                foreach (var o in item.Orders)                {                    Console.WriteLine(o.OrderID);                }                Console.ReadKey();            }

Result:

Select [t0]. [customerid], [t0]. [companyName], [t0]. [contactname], [t0]. [contactt
Itle], [t0]. [address], [t0]. [City], [t0]. [region], [t0]. [postalcode], [t0]. [coun
Try], [t0]. [PHONE], [t0]. [Fax]
From [DBO]. [MERs] as [t0]
-- Context: sqlprovider (sql2008) model: attributedmetamodel build: 4.0.30319.1

Alfki
This is deferred load.
Select [t0]. [orderid], [t0]. [customerid], [t0]. [employeeid], [t0]. [orderdate], [
T0]. [requireddate], [t0]. [shippeddate], [t0]. [shipvia], [t0]. [freight], [t0]. [sh
Ipname], [t0]. [shipaddress], [t0]. [shipcity], [t0]. [shipregion], [t0]. [shipposta
Lcode], [t0]. [shipcountry]
From [DBO]. [orders] as [t0]
Where [t0]. [customerid] = @ P0
-- @ P0: Input nvarchar (size = 4000; prec = 0; scale = 0) [alfki]
-- Context: sqlprovider (sql2008) model: attributedmetamodel build: 4.0.30319.1

10643
...

Instance 2: Run now

NorthwndDataContext dc = new NorthwndDataContext();            dc.Log = Console.Out;            DataLoadOptions lo = new DataLoadOptions();            lo.LoadWith<Entity.Customer>(c => c.Orders);            dc.LoadOptions = lo;            var query = (from c in dc.Customers                select c).ToList();            foreach (var item in query)            {                Console.WriteLine(item.CustomerID);                Console.WriteLine("this is deferred load.");                foreach (var o in item.Orders)                {                    Console.WriteLine(o.OrderID);                }                Console.ReadKey();            }

Result:

Select [t0]. [customerid], [t0]. [companyName], [t0]. [contactname], [t0]. [contactt
Itle], [t0]. [address], [t0]. [City], [t0]. [region], [t0]. [postalcode], [t0]. [coun
Try], [t0]. [PHONE], [t0]. [Fax], [T1]. [orderid], [T1]. [customerid] as [customerid
2], [T1]. [employeeid], [T1]. [orderdate], [T1]. [requireddate], [T1]. [shippeddate]
, [T1]. [shipvia], [T1]. [freight], [T1]. [shipname], [T1]. [shipaddress], [T1]. [Shi
Pcity], [T1]. [shipregion], [T1]. [shippostalcode], [T1]. [shipcountry], (
Select count (*)
From [DBO]. [orders] as [T2]
Where [T2]. [customerid] = [t0]. [customerid]
) As [value]
From [DBO]. [MERs] as [t0]
Left Outer Join [DBO]. [orders] as [T1] on [T1]. [customerid] = [t0]. [customerid]
Order by [t0]. [customerid], [T1]. [orderid]
-- Context: sqlprovider (sql2008) model: attributedmetamodel build: 4.0.30319.1

Alfki
This is deferred load.
10643

...

(You can also see the difference between the two. Pay attention to the use of this delayed loading. In the corresponding entity class, use entityref and entityset packaging for the class to be mapped,

Link packaging is required for delayed loading of a field mapped to the object class)

 

2. Compile a query: a query expression is often used multiple times. If an Expression Tree is repeatedly generated every time, generating an SQL statement is costly and inefficient. Therefore, you can use the compile query function to compile the unchanged part and add the part to be changed.

Example:

// Compile the query core var query1 = compiledquery. compile (northwnddatacontext DC1, string initialchar) => from C in dc1.mers MERs where c. customerid. startswith (initialchar) Select C); foreach (VAR Item1 in new [] {"A", "B", "C", "D "}) {// call method var query = query1 (DC, Item1); foreach (VAR item in query) {console. writeline ("Customer ID: {0}", item. customerid );}}

The above method should be used in a suitable place to improve efficiency. Other methods and methods that can improve the efficiency of LINQ to SQL will be further supplemented in the future.