Cont()與Where().Count()有時性能差別如此之大！
發(fā)布日期：2022-01-30 11:03 | 文章來源：腳本之家
首先，先看看我之前是如何寫這個提示狀態(tài)字段的，實體中加入ContentStatus，然后直接在Linq語句中Select 實體對象中加入ContentStatus=Product_Maintain.Count(C => C.CompanyID == company.ID && C.IsDeleted == 0 && (C.AuditStatus == 0 || C.AuditStatus == 4))>0？"產(chǎn)品有更新":""。這時我想應該是加入三元運算，linq在轉(zhuǎn)Sql時，產(chǎn)生過多的，Case，when ，then語句，三元運算增加了判斷會影響查詢性能，于是我去掉后，再運行查看頁面，仍然很慢，感覺不出快了多少。
這時，我想起了LinqPad，看看到底轉(zhuǎn)換生成了怎樣的Sql語句。運用Count（條件）生成Sql代碼如下：
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
這時我發(fā)現(xiàn)一個很簡單的Count的Sql 語句，linq轉(zhuǎn)換后變得如此復雜，我直接在sql server中運行此代碼，發(fā)現(xiàn)查詢還是很慢，于是我直接把ContentStatus=Product_Maintain.Where(C => C.CompanyID == company.ID && C.IsDeleted == 0 && (C.AuditStatus == 0 || C.AuditStatus == 4)).Count()生成Sql語句為：
代碼
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))
發(fā)現(xiàn)運行速度那是快了一個數(shù)量級??！
后臺列表查詢結果速度大大提升有圖為證(聲明：以下圖都為項目中截圖，不是簡單的單表查詢，還連了用戶表，詳細表等數(shù)量也都挺大的)：
復制代碼 代碼如下:
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
復制代碼 代碼如下:
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))
發(fā)現(xiàn)運行速度那是快了一個數(shù)量級??！
后臺列表查詢結果速度大大提升有圖為證(聲明：以下圖都為項目中截圖，不是簡單的單表查詢，還連了用戶表，詳細表等數(shù)量也都挺大的)：
圖1為Count結果，用了35秒，哇塞！
圖2為Where(條件).Count()結果，同樣的數(shù)據(jù)只用了4秒鐘，差了10倍！
然后為了取值方面我還是加入三元運算，ContentStatus=Product_Maintain.Where(C => C.CompanyID == company.ID && C.IsDeleted == 0 && (C.AuditStatus == 0 || C.AuditStatus == 4)).Count()>0？"產(chǎn)品有更新":""。結果如下：
真的是Count()與Where()區(qū)別，不可能這么大差距吧？于是我單寫
Product_Maintain.Where(C => C.IsDeleted == 0 && (C.AuditStatus == 0 || C.AuditStatus == 4)).Count()與
Product_Maintain.Count(C => C.IsDeleted == 0 && (C.AuditStatus == 0 || C.AuditStatus == 4))
發(fā)現(xiàn)速度差不多，生成的代碼是一樣的。
SELECT COUNT(*) AS [value]
FROM [GasSNS_Company_Equipment_Maintain] AS [t0]
WHERE ([t0].[IsDeleted] = @p0) AND (([t0].[AuditStatus] = @p1) OR ([t0].[AuditStatus] = @p2))
原來是我如果在Select中取某表的數(shù)量并且條件中使用了之前from后的某個變量時，這時用Count(條件)和Where(條件).Count()產(chǎn)生代碼才會不同，查詢速度才會出現(xiàn)數(shù)量級的差別。
代碼
//效率低版本：
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))
}
與
//效率高版本：
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()
}
否則，Count()與Where().Count()生成的SQL語句是相同的，效率也一樣。
復制代碼 代碼如下:
SELECT COUNT(*) AS [value]
FROM [GasSNS_Company_Equipment_Maintain] AS [t0]
WHERE ([t0].[IsDeleted] = @p0) AND (([t0].[AuditStatus] = @p1) OR ([t0].[AuditStatus] = @p2))
原來是我如果在Select中取某表的數(shù)量并且條件中使用了之前from后的某個變量時，這時用Count(條件)和Where(條件).Count()產(chǎn)生代碼才會不同，查詢速度才會出現(xiàn)數(shù)量級的差別。
代碼
復制代碼 代碼如下:
//效率低版本：
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))
}
與
復制代碼 代碼如下:
//效率高版本：
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()
}
否則，Count()與Where().Count()生成的SQL語句是相同的，效率也一樣。
總結到此，望各位看官以后要注意！本人入園兩年來，第一發(fā)在首頁，請各位看官不吝賜教！
謝謝各位看官的指點，聲明下以上查詢圖都LinqPad查詢結果截圖。至于為啥4秒左右為LinqPad查詢時間，Linq生成Sql語句在Sql Server中執(zhí)行不到1秒，以下截圖作解釋：
版權聲明：本站文章來源標注為YINGSOO的內(nèi)容版權均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務器上建立鏡像，否則將依法追究法律責任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學習參考，不代表本站立場，如有內(nèi)容涉嫌侵權，請聯(lián)系alex-e#qq.com處理。
相關文章
關注官方微信