Shulou(Shulou.com)06/02 Report--

This article introduces the relevant knowledge of "LINQ query effect example analysis". In the actual case operation process, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

Some Experience of LINQ Query

When we will skillfully use the above query methods to make various queries on the contents of the database, we should understand the mechanism of these database operations, adjust various data operation statements in time, and run with higher efficiency. So let's take a look at LINQ's database operations and see what she does.

Compared with NHibernate, LINQ is indeed superior to NHibernate in O/R Mapping performance and controllability. First of all, Linq default data mapping is implemented by Attribute, which is a. NET specific syntax, which has already determined various attributes of data objects at compile time. Most O/R Mapping tools such as NHibernate still use XML mapping files to describe attributes of data objects, and read attributes of data objects from external files. Obviously, runtime efficiency will be lost. Secondly, there are differences in the way to obtain data. LINQ's powerful SQL analysis mechanism can analyze SQL statements for various data operations and optimize them. The efficiency improvement is also obvious.

Of course, as an O/R Mapping tool, its efficiency must not reach the efficiency of directly using SQL statements to access the database, that is, we usually call SqlDataReader/SqlDataAdapter to access the database, but Linq's performance has given us a very big surprise, I did a test, using SqlDataReader and LINQ to do the same large number of data queries, falling behind even less than 10%, while NHibernate query efficiency, but a lot lower, almost twice slower. For such a powerful data mapping function, such efficiency is acceptable. Unfortunately, LINQ currently only supports SQL Server (although XML, Entity, etc. can be supported).

In using LINQ queries for data queries, we can optimize queries by analyzing SQL statements generated by LINQ, which is very convenient, but for the efficiency of data updates, we have to talk about LINQ's data update mechanism. In general, we will do this for data updates:

var query = from emp in dbdata.Employees where emp.DepId=="1001" select emp; Employee employee = query.First(); employee. Employee Name = ""; dbdata.SubmitChanges();

For the above code, we can see that its function is to extract all employees with department code 1001 from the Employee table, and then we extract *** data (here for simplicity, we only extract ***, in fact, we can use Where to extract records that meet the conditions), and then modify the name to "Li Si" and update it to the database. What does LINQ do with this code? Several records are retrieved from the database by query, and are placed in memory, and marked as new (unchanged) state. When the employee name is modified, the modified object is marked as Dirty(changed). When SubmitChanges, SQL statements are automatically generated and executed for the records with Dirty object state in memory. That is to say, we have to complete a data update, at least once query and update.

A bit of analysis of LINQ queries:

Because of the delay loading (Layze Load) technology, in the above statement, only one record is actually taken from the database, and only this record is updated when updating, so the efficiency is still very high. I found in the process of testing that one record is randomly selected from 250000 pieces of data for updating. The actual efficiency is almost the same as that of randomly selecting one record from 10 pieces of data for updating, because the comparison update state is carried out in memory. Therefore, efficiency is relatively high. Let's take a look at what SQL statements the actual update generates:

UPDATE [dbo]. [Employee] SET [EmployeeName] = @p4 WHERE ([EmployeeId] = @p0) AND ([DepId] = @p1) AND ([EmployeeName] = @p2) AND ([EmployeeSalary] = @p3)

Originally, we only modified the Employee Name field, and the generated SQL statement only updated the Employee field. Then, let's look at the latter conditions. Why do we include other conditions besides the primary key? It turns out that this is also the rigor of LINQ's automatic generation of SQL statements. This is to prevent errors when multiple transactions update the same record in concurrent cases. If transaction A updates the record, transaction B updates will fail. We have to ask, what if you want to update the primary key field? Could it be wrong to update to multiple records? The answer is yes, it will definitely be updated to other records by mistake. Therefore, LINQ stipulates that the primary key field is not allowed to be updated. If you really want to update, delete the record and insert a new record. Such a rigorous SQL statement will bring us some trouble. Let's look at the following application scenario:

If we have a field in the table for counters, using SQL statements is something like this:

Update CountTable set CountColumn= CountColumn+1 where CountId=@countId

But the Sql statement generated using LINQ is:

UPDATE [dbo]. [CountTable] SET [CountColumn] = @p2 WHERE ([CountId] = @p0) AND ([CountColumn] = @p1)

@p2 parameter is passed in after calculation,@p1 parameter is the original value of CountColumn. That is, CountColumn+1 is not calculated by the database, so we tend to fail when the concurrency count is high. I did a test, using multi-thread simulation of multi-user counting statistics, statistics in the database value than the use of static variables to save the value is smaller, which means that the database update is the existence of failure. In addition, such each update, the operation to be completed has two steps of search and update, so it also has a relatively large impact on efficiency.

Here, we are not saying that LINQ has defects, because this situation may not be well solved under any O/R Mapping framework, but just to tell us that only by understanding the internal operation of the system can we design a more efficient and reliable system.

"LINQ query effect example analysis" content is introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.