Cont () and Where (). Count () sometimes have such a big performance difference!

I previously added a field to this list to help me prompt the administrator if the product list has a Status field such as modification, so I can conclude that this field is added.

First, let's take a look at how I previously wrote this prompt Status field, add ContentStatus to the object, and then directly add ContentStatus = Product_Maintain.Count (C => C. companyID = company. ID & C. isDeleted = 0 & (C. auditStatus = 0 | C. auditStatus = 4)> 0? "Product updates ":"". At this time, I think it should be added to the three-element operation. During the SQL conversion of linq, there are too many cases, when, then statements, and the addition of the three-element operation will affect the query performance, so after I removed it, I ran to view the page. It was still very slow and I couldn't feel much faster.

Now, I think of LinqPad to see what SQL statements are generated by the conversion. The SQL code generated using Count (condition) is as follows:
Copy codeThe Code is as follows:
Select count (*) AS [value]
FROM (
SELECT
(CASE
WHEN ([t1]. [CompanyID] = ([t0]. [ID]) AND ([t1]. [IsDeleted] = @ p0) AND ([t1]. [AuditStatus] = @ p1) OR ([t1]. [AuditStatus] = @ p2) THEN 1
When not ([t1]. [CompanyID] = ([t0]. [ID]) AND ([t1]. [IsDeleted] = @ p0) AND ([t1]. [AuditStatus] = @ p1) OR ([t1]. [AuditStatus] = @ p2) THEN 0
ELSE NULL
END) AS [value]
FROM [Company_Product_Maintain] AS [t1]
) AS [t2]
WHERE [t2]. [value] = 1


At this time, I found a very simple Count SQL statement, which became so complex after the linq conversion. I ran this Code directly in SQL server and found that the query was still very slow, so I directly put ContentStatus = Product_Maintain.Where (C => C. companyID = company. ID & C. isDeleted = 0 & (C. auditStatus = 0 | C. auditStatus = 4 )). the SQL statement generated by Count () is:

Code
Copy codeThe Code is as follows:
Select count (*) AS [value]
FROM [GasSNS_Company_Equipment_Maintain] AS [t1]
WHERE ([t1]. [CompanyID] = ([t0]. [ID]) AND ([t1]. [IsDeleted] = @ p0) AND ([t1]. [AuditStatus] = @ p1) OR ([t1]. [AuditStatus] = @ p2 ))

It's an order of magnitude faster!
The query result speed of the background list is greatly improved (Statement:It is not a simple single-Table query, but also a large number of user tables and detailed tables.):

Figure 1 shows the Count result. It took 35 seconds. Wow!

 

Figure 2 shows the Where (condition). Count () result. The same data takes only 4 seconds, 10 times worse!

 

Then, for the value, I add the Three-element operation ContentStatus = Product_Maintain.Where (C => C. companyID = company. ID & C. isDeleted = 0 & (C. auditStatus = 0 | C. auditStatus = 4 )). count ()> 0? "Product updates ":"". The result is as follows:

 

It's really the difference between Count () and Where (). Isn't that big difference possible? So I write Product_Maintain.Where (C => C. isDeleted = 0 & (C. auditStatus = 0 | C. auditStatus = 4 )). count () and Product_Maintain.Count (C => C. isDeleted = 0 & (C. auditStatus = 0 | C. auditStatus = 4) The generated code is the same as the speed.
Copy codeThe Code is as follows:
Select count (*) AS [value]
FROM [GasSNS_Company_Equipment_Maintain] AS [t0]
WHERE ([t0]. [IsDeleted] = @ p0) AND ([t0]. [AuditStatus] = @ p1) OR ([t0]. [AuditStatus] = @ p2 ))

It turns out that if I retrieve the number of tables in the Select statement and use a variable after the from statement in the condition, Count (condition) and Where (condition) are used ). only when Count () is generated can the query speed be different by an order of magnitude.
Code
Copy codeThe Code is as follows:
// Low efficiency version:
From company in Company
Select new
{
Contacter = v. ContacterID,
Count = Product_Maintain.Count (C => C. companyID = company. ID & C. isDeleted = 0 & (C. auditStatus = 0 | C. auditStatus = 4 ))
}

And
Copy codeThe Code is as follows:
// High Efficiency version:
From company in Company
Select new
{
Contacter = v. ContacterID,
Count = Product_Maintain.Where (C => C. companyID = company. ID & C. isDeleted = 0 & (C. auditStatus = 0 | C. auditStatus = 4 )). count ()
}

Otherwise, the SQL statements generated by Count () and Where (). Count () are the same and the efficiency is the same. To sum up, we hope you will pay attention to it later! I entered the park for two years, the first time on the home page, please read the official advice! Thank you for your guidance,DeclarationAll the above query charts are the result of the LinqPad query. As for the LinqPad query time in about 4 seconds, the SQL statement generated by Linq cannot be executed within 1 second in SQL Server. The following is an explanation: