How to Improve the Performance of Linq queries (I)

How to Improve the Performance of Linq queries) since the introduction of Linq, many developers have been excited and it seems easier to write code than before, particularly with the release of Linq2Sql and so on, developers feel that they have powerful weapons in their hands. At the same time, problems brought about by Linq2Sql are constantly emerging, especially in terms of performance. This is why many applications with high performance requirements must abandon the Linq2Sql series of technologies, many of them return to the previous ADO. NET technology, the pursuit of full control.Series of articles: How to Improve the Performance of the Linq query (I) How to improve the performance of the Linq query (I) Here I will share with you some knowledge. In this article, we will not talk about the advantages of the Linq series of technologies, but whether to use them. Instead, we will tell friends who will or who have already used the Linq technology How to Improve the Performance of Linq2Sql queries. In this article, we will first introduce how the Linq2Sql query is executed, and then introduce how to compile the Linq query to improve its performance. In this article, we hope you will have some experience in using Linq2Sql, especially Linq2Sql.The topics in this article are as follows:In-depth view of the working principle of Linq2Sql query)How to compile the compiled Linq query)Performance Comparison)Result Analysis) View the working principle of Linq2Sql query in DepthFirst, let's take a look at the following simple Linq query. This query is very simple, that is, to obtain data from a Customer table. This Linq query is converted to an SQL statement by the Linq engine, 650) this. width = 650; "border =" 0 "alt =" 2012020.110155.png" src =" http://www.agilesharp.com/Services/BlogAttachment.ashx?AttachmentID=257 "Width =" 636 "height =" 367 "/> for a Linq query, you must first check its syntax, lexical form, and finally generate an SQL statement. This process can be said to be very time-consuming, and should be done for every Linq query. If we can cache the query plan for each Linq query, it will be faster. In fact, this principle is the same as we use SQL Server: submit SQL Server to the database, first execute lexical syntax analysis, then generate the query tree, and then hand it to the query optimizer to generate the execution plan, then it is handed over to the execution engine for execution. Finally, the storage engine obtains data and the generated execution plan is cached for reuse. This greatly improves the performance. For more information, see here. In the Linq technique, a technique called compiling a Linq query is provided. In compiled Linq queries, the execution plan generated by Linq is cached in a static class. We know that the static class is a global public resource, so the corresponding Execution Plan can be found in this static class for the Linq query, rather than being parsed every time, as shown below: 650) this. width = 650; "border =" 0 "alt =" 2012020.110237.png" src =" http://www.agilesharp.com/Services/BlogAttachment.ashx?AttachmentID=258 "Width =" 550 "/> it can be seen that, from the query of Linq to the final execution parsing to SQL Execution, four steps are required. If the execution plan of the Linq query is not cached, there are actually five steps: 650) this. width = 650; "border =" 0 "alt =" 2012020.110310.png" src =" http://www.agilesharp.com/Services/BlogAttachment.ashx?AttachmentID=259 "Width =" 400 "height =" 615 "/>How to compile compiled Linq queriesNow that we know that compiling a query can improve the performance, let's take a look at the specific operations. First, import the System. Data. Linq namespace. Then, in order to avoid losing a lot of code to make friends uncomfortable, let's do it a little bit:650) this. width = 650; "border =" 0 "alt =" 20120425110414.png" src =" http://www.agilesharp.com/Services/BlogAttachment.ashx?AttachmentID=260 "Width =" 634 "height =" 60 "/> for this method:. the parameter of this method must first accept a DataContext object B. this method can then accept one or more input parameters. Now in our example, we accept a string type input parameter, because our above customer query only passed in a txtCustomer. text value. C. We need to define the output parameters of the IQueryable type. 3. after the variables of the method pointer are defined, we can call the CompiledQuery class method Compiled and use the DataContext and a string as the input parameters, as follows: 650) this. width = 650; "border =" 0 "alt =" 2012020.110555.png" src =" http://www.agilesharp.com/Services/BlogAttachment.ashx?AttachmentID=261 "Width =" 612 "height =" 55 "/> 4. the complete code for defining the entire method pointer is as follows: 650) this. width = 650; "border =" 0 "alt =" 2012020.110637.png" src =" http://www.agilesharp.com/Services/BlogAttachment.ashx?AttachmentID=262 "Width =" 609 "height =" 65 "/> 5. the entire static class is defined as follows: 650) this. width = 650; "border =" 0 "alt =" 20120425110714.png" src =" http://www.agilesharp.com/Services/BlogAttachment.ashx?AttachmentID=263 "Width =" 618 "height =" 154 "/> after the above preparations are completed, we will start to use them. You can make the following calls as needed: 650) this. width = 650; "border =" 0 "alt =" 2012020.110751.png" src =" http://www.agilesharp.com/Services/BlogAttachment.ashx?AttachmentID=264 "Width =" 606 "height =" 145 "/>