ef 生成的分頁查詢方案效率差，有解決方案嗎
0
悬赏园豆：50
[已解决问题]
解决于 2022-06-07 17:31
最近使用ef過程中，做一部分功能的效率優化，發現ef生成的分頁語句有點問題。
如dudu的博客中提到一樣
http://www.cnblogs.com/dudu/archive/2011/01/28/entity_framework_bad_sql.html
比如;
db.v_ABC.skip(15).take(15);
生成的sql语句
select top(15)
a as a,
b as b
from(select a,b,c from (select a,b,c from (select a,b,c from v_ABC).....where xxx.row_number>0
先对整个表建立row_number作为子查询，然后再对子表筛选row_number来定位。问题是子查询等于要把整表检索出来，如果这样的话，随着数据量的提升，不是会越来越慢。。
各位有遇到這個問題，和知道解決方案嗎？
比較好的方案應該是像dudu博文中說的linq to sql 生成的那種語句
各位如还遇到其他ef问题，也可以多分享下。
问题补充：
linq
var q5 = ((from c in this.context.CollectionSet join rc in this.context.ReceivableCollectionSet on c.CollectionID equals rc.Collection.CollectionID join r in this.context.ReceivableSet on rc.Receivable.ReceivableID equals r.ReceivableID join fc in this.context.FinancialContractSet on r.FinancialContract.FinancialContractID equals fc.FinancialContractID join channel in this.context.ChannelTypeSet on fc.ChannelType.ChannelTypeID equals channel.ChannelTypeID join histoty in this.context.CollectionHistorySet on new { id = c.CollectionID, newStatus = (byte)CollectionHistoryStatusType.Create } equals new { id = histoty.Collection.CollectionID, newStatus = histoty.NewStatusProp } where (!collectionID.HasValue || c.CollectionID == collectionID.Value) && (!lowAmount.HasValue || c.CollectionAmount >= lowAmount.Value) && (!highAmount.HasValue || c.CollectionAmount <= highAmount.Value) && (!startDate.HasValue || c.CollectionTime >= startDate.Value) && (!endDate.HasValue || c.CollectionTime <= endDate.Value) && ((byte)status == (byte)CollectionStatusType.All || (c.CollectionStatusProp == (byte)status)) //收款狀態 && (!isBankIn.HasValue || c.IsBankIn == isBankIn.Value) && ((int)methodOfPayment == (int)MethodOfPaymentType.All || c.MethodOfPaymentProp == (int)methodOfPayment) && ((null == organizationCode || organizationCode.Length == 0) || c.OrganizationCode == organizationCode) && ((null == apprCode || apprCode.Length == 0) || c.ApprCode == apprCode) && ((null == merchantNo || merchantNo.Length == 0) || c.MerchantNo == merchantNo) && ((null == cardTypeCode || cardTypeCode.Length == 0) || c.CardTypeCode == cardTypeCode) && ((null == promotCode || promotCode.Length == 0) || c.PromotionCode.Contains(promotCode)) && ((null == checkNo || checkNo.Length == 0) || c.CheckNo.Contains(checkNo)) && ((null == poNo || poNo.Length == 0) || fc.OrderNo == poNo) select new CollectionListItemExtend { OrderNo = fc.OrderNo, OrderURI = channel.OrderURI, CollectionID = c.CollectionID, CollectionAmount = c.CollectionAmount, MethodOfPaymentProp = c.MethodOfPaymentProp, CardTypeCode = c.CardTypeCode, CurrencyCode = c.CurrencyCode, CollectionTime = c.CollectionTime, IsBankIn = c.IsBankIn, StatusProp = c.CollectionStatusProp, OrganizationCode = c.OrganizationCode, OrganizationID = c.OrganizationID, Remark = c.Remark, RelationID = histoty.RelationID, IsRefund = c.RefundDetail != null, PromotCode = c.PromotionCode, ChequeNo = c.CheckNo, PaymentDeadline = c.PaymentDeadline }) .Concat(from c in this.context.CollectionSet join rc in this.context.ReceivedInAdvanceCollectionSet on c.CollectionID equals rc.Collection.CollectionID join r in this.context.ReceivedInAdvanceSet on rc.ReceivedInAdvance.ReceivedInAdvanceID equals r.ReceivedInAdvanceID join fc in this.context.FinancialContractSet on r.FinancialContract.FinancialContractID equals fc.FinancialContractID join channel in this.context.ChannelTypeSet on fc.ChannelType.ChannelTypeID equals channel.ChannelTypeID join histoty in this.context.CollectionHistorySet on new { id = c.CollectionID, newStatus = (byte)CollectionHistoryStatusType.Create } equals new { id = histoty.Collection.CollectionID, newStatus = histoty.NewStatusProp } where (!collectionID.HasValue || c.CollectionID == collectionID.Value) && (!lowAmount.HasValue || c.CollectionAmount >= lowAmount.Value) && (!highAmount.HasValue || c.CollectionAmount <= highAmount.Value) && (!startDate.HasValue || c.CollectionTime >= startDate.Value) && (!endDate.HasValue || c.CollectionTime <= endDate.Value) && ((byte)status == (byte)CollectionStatusType.All || (c.CollectionStatusProp == (byte)status)) //收款狀態 && (!isBankIn.HasValue || c.IsBankIn == isBankIn.Value) && ((int)methodOfPayment == (int)MethodOfPaymentType.All || c.MethodOfPaymentProp == (int)methodOfPayment) && ((null == organizationCode || organizationCode.Length == 0) || c.OrganizationCode == organizationCode) && ((null == apprCode || apprCode.Length == 0) || c.ApprCode == apprCode) && ((null == merchantNo || merchantNo.Length == 0) || c.MerchantNo == merchantNo) && ((null == cardTypeCode || cardTypeCode.Length == 0) || c.CardTypeCode == cardTypeCode) && ((null == promotCode || promotCode.Length == 0) || c.PromotionCode.Contains(promotCode)) && ((null == checkNo || checkNo.Length == 0) || c.CheckNo.Contains(checkNo)) && ((null == poNo || poNo.Length == 0) || fc.OrderNo == poNo) select new CollectionListItemExtend { OrderNo = fc.OrderNo, OrderURI = channel.OrderURI, CollectionID = c.CollectionID, CollectionAmount = c.CollectionAmount, MethodOfPaymentProp = c.MethodOfPaymentProp, CardTypeCode = c.CardTypeCode, CurrencyCode = c.CurrencyCode, CollectionTime = c.CollectionTime, IsBankIn = c.IsBankIn, StatusProp = c.CollectionStatusProp, OrganizationCode = c.OrganizationCode, OrganizationID = c.OrganizationID, Remark = c.Remark, RelationID = histoty.RelationID, IsRefund = c.RefundDetail != null, PromotCode = c.PromotionCode, ChequeNo = c.CheckNo, PaymentDeadline = c.PaymentDeadline })) .OrderByDescending(p => p.CollectionID) .Paging(pageIndex, pageSize);
生成的sql
SELECT TOP (20) [Project3].[C1] AS [C1], [Project3].[C2] AS [C2], [Project3].[C3] AS [C3], [Project3].[C4] AS [C4], [Project3].[C5] AS [C5], [Project3].[C6] AS [C6], [Project3].[C7] AS [C7], [Project3].[C8] AS [C8], [Project3].[C9] AS [C9], [Project3].[C10] AS [C10], [Project3].[C11] AS [C11], [Project3].[C12] AS [C12], [Project3].[C13] AS [C13], [Project3].[C14] AS [C14], [Project3].[C15] AS [C15], [Project3].[C16] AS [C16], [Project3].[C17] AS [C17], [Project3].[C18] AS [C18], [Project3].[C19] AS [C19] FROM ( SELECT [Project3].[C1] AS [C1], [Project3].[C2] AS [C2], [Project3].[C3] AS [C3], [Project3].[C4] AS [C4], [Project3].[C5] AS [C5], [Project3].[C6] AS [C6], [Project3].[C7] AS [C7], [Project3].[C8] AS [C8], [Project3].[C9] AS [C9], [Project3].[C10] AS [C10], [Project3].[C11] AS [C11], [Project3].[C12] AS [C12], [Project3].[C13] AS [C13], [Project3].[C14] AS [C14], [Project3].[C15] AS [C15], [Project3].[C16] AS [C16], [Project3].[C17] AS [C17], [Project3].[C18] AS [C18], [Project3].[C19] AS [C19], row_number() OVER (ORDER BY [Project3].[C4] DESC) AS [row_number] FROM ( SELECT [UnionAll1].[MethodOfPaymentProp] AS [C1], [UnionAll1].[OrderNo] AS [C2], [UnionAll1].[OrderURI] AS [C3], [UnionAll1].[CollectionID] AS [C4], [UnionAll1].[CollectionAmount] AS [C5], [UnionAll1].[MethodOfPaymentProp1] AS [C6], [UnionAll1].[CardTypeCode] AS [C7], [UnionAll1].[CurrencyCode] AS [C8], [UnionAll1].[CollectionTime] AS [C9], [UnionAll1].[IsBankIn] AS [C10], [UnionAll1].[CollectionStatusProp] AS [C11], [UnionAll1].[OrganizationCode] AS [C12], [UnionAll1].[OrganizationID] AS [C13], [UnionAll1].[Remark] AS [C14], [UnionAll1].[RelationID] AS [C15], [UnionAll1].[C1] AS [C16], [UnionAll1].[PromotionCode] AS [C17], [UnionAll1].[CheckNo] AS [C18], [UnionAll1].[PaymentDeadline] AS [C19] FROM (SELECT [Extent1].[MethodOfPaymentProp] AS [MethodOfPaymentProp], [Extent4].[OrderNo] AS [OrderNo], [Extent5].[OrderURI] AS [OrderURI], [Extent1].[CollectionID] AS [CollectionID], [Extent1].[CollectionAmount] AS [CollectionAmount], [Extent1].[MethodOfPaymentProp] AS [MethodOfPaymentProp1], [Extent1].[CardTypeCode] AS [CardTypeCode], [Extent1].[CurrencyCode] AS [CurrencyCode], [Extent1].[CollectionTime] AS [CollectionTime], [Extent1].[IsBankIn] AS [IsBankIn], [Extent1].[CollectionStatusProp] AS [CollectionStatusProp], [Extent1].[OrganizationCode] AS [OrganizationCode], [Extent1].[OrganizationID] AS [OrganizationID], [Extent1].[Remark] AS [Remark], [Extent6].[RelationID] AS [RelationID], CASE WHEN ([Extent7].[RefundDetailID] IS NOT NULL) THEN cast(1 as bit) ELSE cast(0 as bit) END AS [C1], [Extent1].[PromotionCode] AS [PromotionCode], [Extent1].[CheckNo] AS [CheckNo], [Extent1].[PaymentDeadline] AS [PaymentDeadline] FROM [dbo].[AC_Collection] AS [Extent1] INNER JOIN [dbo].[AC_ReceivableCollection] AS [Extent2] ON [Extent1].[CollectionID] = [Extent2].[Collection_CollectionID] INNER JOIN [dbo].[AC_Receivable] AS [Extent3] ON [Extent2].[Receivable_ReceivableID] = [Extent3].[ReceivableID] INNER JOIN [dbo].[AC_FinancialContract] AS [Extent4] ON [Extent3].[FinancialContract_FinancialContractID] = [Extent4].[FinancialContractID] INNER JOIN [dbo].[AC_ChannelType] AS [Extent5] ON [Extent4].[ChannelTypeID] = [Extent5].[ChannelTypeID] INNER JOIN [dbo].[AC_CollectionHistory] AS [Extent6] ON ([Extent1].[CollectionID] = [Extent6].[Collection_CollectionID]) AND (4 = [Extent6].[NewStatusProp]) LEFT OUTER JOIN [dbo].[AC_RefundDetail] AS [Extent7] ON ([Extent7].[RefundDetailID] = [Extent1].[RefundDetail_RefundDetailID]) AND ([Extent1].[RefundDetail_RefundDetailID] IS NOT NULL) WHERE (@p__linq__0 IS NULL OR [Extent1].[CollectionID] = @p__linq__1) AND ((@p__linq__2 IS NULL) OR ([Extent1].[CollectionAmount] >= @p__linq__3)) AND ((@p__linq__4 IS NULL) OR ([Extent1].[CollectionAmount] <= @p__linq__5)) AND ((@p__linq__6 IS NULL) OR ([Extent1].[CollectionTime] >= @p__linq__7)) AND ((@p__linq__8 IS NULL) OR ([Extent1].[CollectionTime] <= @p__linq__9)) AND (@p__linq__10 = 255 OR [Extent1].[CollectionStatusProp] = @p__linq__11) AND (@p__linq__12 IS NULL OR [Extent1].[IsBankIn] = @p__linq__13) AND (@p__linq__14 = 0 OR [Extent1].[MethodOfPaymentProp] = @p__linq__15) AND ((@p__linq__16 IS NULL) OR (0 = ( CAST(LEN(@p__linq__17) AS int))) OR ([Extent1].[OrganizationCode] = @p__linq__18)) AND ((@p__linq__19 IS NULL) OR (0 = ( CAST(LEN(@p__linq__20) AS int))) OR ([Extent1].[ApprCode] = @p__linq__21) OR (([Extent1].[ApprCode] IS NULL) AND (@p__linq__21 IS NULL))) AND ((@p__linq__22 IS NULL) OR (0 = ( CAST(LEN(@p__linq__23) AS int))) OR ([Extent1].[MerchantNo] = @p__linq__24) OR (([Extent1].[MerchantNo] IS NULL) AND (@p__linq__24 IS NULL))) AND ((@p__linq__25 IS NULL) OR (0 = ( CAST(LEN(@p__linq__26) AS int))) OR ([Extent1].[CardTypeCode] = @p__linq__27) OR (([Extent1].[CardTypeCode] IS NULL) AND (@p__linq__27 IS NULL))) AND ((@p__linq__28 IS NULL) OR (0 = ( CAST(LEN(@p__linq__29) AS int))) OR ([Extent1].[PromotionCode] LIKE @p__linq__30 ESCAPE N'~')) AND ((@p__linq__31 IS NULL) OR (0 = ( CAST(LEN(@p__linq__32) AS int))) OR ([Extent1].[CheckNo] LIKE @p__linq__33 ESCAPE N'~')) AND ((@p__linq__34 IS NULL) OR (0 = ( CAST(LEN(@p__linq__35) AS int))) OR ([Extent4].[OrderNo] = @p__linq__36)) UNION ALL SELECT [Extent8].[MethodOfPaymentProp] AS [MethodOfPaymentProp], [Extent11].[OrderNo] AS [OrderNo], [Extent12].[OrderURI] AS [OrderURI], [Extent8].[CollectionID] AS [CollectionID], [Extent8].[CollectionAmount] AS [CollectionAmount], [Extent8].[MethodOfPaymentProp] AS [MethodOfPaymentProp1], [Extent8].[CardTypeCode] AS [CardTypeCode], [Extent8].[CurrencyCode] AS [CurrencyCode], [Extent8].[CollectionTime] AS [CollectionTime], [Extent8].[IsBankIn] AS [IsBankIn], [Extent8].[CollectionStatusProp] AS [CollectionStatusProp], [Extent8].[OrganizationCode] AS [OrganizationCode], [Extent8].[OrganizationID] AS [OrganizationID], [Extent8].[Remark] AS [Remark], [Extent13].[RelationID] AS [RelationID], CASE WHEN ([Extent14].[RefundDetailID] IS NOT NULL) THEN cast(1 as bit) ELSE cast(0 as bit) END AS [C1], [Extent8].[PromotionCode] AS [PromotionCode], [Extent8].[CheckNo] AS [CheckNo], [Extent8].[PaymentDeadline] AS [PaymentDeadline] FROM [dbo].[AC_Collection] AS [Extent8] INNER JOIN [dbo].[AC_ReceivedInAdvanceCollection] AS [Extent9] ON [Extent8].[CollectionID] = [Extent9].[Collection_CollectionID] INNER JOIN [dbo].[AC_ReceivedInAdvance] AS [Extent10] ON [Extent9].[ReceivedInAdvance_ReceivedInAdvanceID] = [Extent10].[ReceivedInAdvanceID] INNER JOIN [dbo].[AC_FinancialContract] AS [Extent11] ON [Extent10].[FinancialContract_FinancialContractID] = [Extent11].[FinancialContractID] INNER JOIN [dbo].[AC_ChannelType] AS [Extent12] ON [Extent11].[ChannelTypeID] = [Extent12].[ChannelTypeID] INNER JOIN [dbo].[AC_CollectionHistory] AS [Extent13] ON ([Extent8].[CollectionID] = [Extent13].[Collection_CollectionID]) AND (4 = [Extent13].[NewStatusProp]) LEFT OUTER JOIN [dbo].[AC_RefundDetail] AS [Extent14] ON ([Extent14].[RefundDetailID] = [Extent8].[RefundDetail_RefundDetailID]) AND ([Extent8].[RefundDetail_RefundDetailID] IS NOT NULL) WHERE (@p__linq__37 IS NULL OR [Extent8].[CollectionID] = @p__linq__38) AND ((@p__linq__39 IS NULL) OR ([Extent8].[CollectionAmount] >= @p__linq__40)) AND ((@p__linq__41 IS NULL) OR ([Extent8].[CollectionAmount] <= @p__linq__42)) AND ((@p__linq__43 IS NULL) OR ([Extent8].[CollectionTime] >= @p__linq__44)) AND ((@p__linq__45 IS NULL) OR ([Extent8].[CollectionTime] <= @p__linq__46)) AND (@p__linq__47 = 255 OR [Extent8].[CollectionStatusProp] = @p__linq__48) AND (@p__linq__49 IS NULL OR [Extent8].[IsBankIn] = @p__linq__50) AND (@p__linq__51 = 0 OR [Extent8].[MethodOfPaymentProp] = @p__linq__52) AND ((@p__linq__53 IS NULL) OR (0 = ( CAST(LEN(@p__linq__54) AS int))) OR ([Extent8].[OrganizationCode] = @p__linq__55)) AND ((@p__linq__56 IS NULL) OR (0 = ( CAST(LEN(@p__linq__57) AS int))) OR ([Extent8].[ApprCode] = @p__linq__58) OR (([Extent8].[ApprCode] IS NULL) AND (@p__linq__58 IS NULL))) AND ((@p__linq__59 IS NULL) OR (0 = ( CAST(LEN(@p__linq__60) AS int))) OR ([Extent8].[MerchantNo] = @p__linq__61) OR (([Extent8].[MerchantNo] IS NULL) AND (@p__linq__61 IS NULL))) AND ((@p__linq__62 IS NULL) OR (0 = ( CAST(LEN(@p__linq__63) AS int))) OR ([Extent8].[CardTypeCode] = @p__linq__64) OR (([Extent8].[CardTypeCode] IS NULL) AND (@p__linq__64 IS NULL))) AND ((@p__linq__65 IS NULL) OR (0 = ( CAST(LEN(@p__linq__66) AS int))) OR ([Extent8].[PromotionCode] LIKE @p__linq__67 ESCAPE N'~')) AND ((@p__linq__68 IS NULL) OR (0 = ( CAST(LEN(@p__linq__69) AS int))) OR ([Extent8].[CheckNo] LIKE @p__linq__70 ESCAPE N'~')) AND ((@p__linq__71 IS NULL) OR (0 = ( CAST(LEN(@p__linq__72) AS int))) OR ([Extent11].[OrderNo] = @p__linq__73))) AS [UnionAll1] ) AS [Project3] ) AS [Project3] WHERE [Project3].[row_number] > 0 ORDER BY [Project3].[C4] DESC
另一个问题，如果生成类似的语句 多一个top40的话，性能貌似又能提升
linq
var qq=(from c in this.context.CollectionSet orderby c.CollectionID select c.OrganizationID).Paging(2, pageSize);
sql
SELECT TOP (20) [Extent1].[CollectionID] AS [CollectionID] FROM ( SELECT [Extent1].[CollectionID] AS [CollectionID] , row_number() OVER (ORDER BY [Extent1].[CollectionID] ASC) AS [row_number] FROM [dbo].[AC_Collection] AS [Extent1] ) AS [Extent1] WHERE [Extent1].[row_number] > 20 ORDER BY [Extent1].[CollectionID] ASC
多一个top 40
SELECT TOP (20) [Extent1].[CollectionID] AS [CollectionID] FROM ( SELECT TOP 40 [Extent1].[CollectionID] AS [CollectionID] , row_number() OVER (ORDER BY [Extent1].[CollectionID] ASC) AS [row_number] FROM [dbo].[AC_Collection] AS [Extent1] ) AS [Extent1] WHERE [Extent1].[row_number] > 20 ORDER BY [Extent1].[CollectionID] ASC
最佳答案
0
最好能将完整的LINQ写出来了
收获园豆：30
用你帖子里面现成的吧，我没做demo，直接在生产环境弄的，语句有点多
@滴答的雨: 问题就在LINQ上，没有LINQ，无法判断。你可以精简一下，主要是看.Where的写法。
@滴答的雨: “问题是子查询等于要把整表检索出来”，这样的问题通常都是LINQ写法上的问题。
@dudu: 看来是写法问题。。。。
@dudu: 如果一个 A集合.union(B集合).分页 这样的话，就必须A集合和B集合都要查出来了？（全部查出来后，才能进行排序）
@滴答的雨: 没有实际的代码，很难进行讨论
@dudu: 代码回复在补充问题上了，你看看
@滴答的雨: 第一次见到如此复杂的LINQ
@dudu: 這種寫法不好，因為union了，後面再改一改。只是今天從這個語句發現了一些問題
@滴答的雨: 你先得把Clustered Index Scan优化掉，这是性能杀手。
其他回答(2)
0
如果我没记错的话 ef是真分页
收获园豆：5
真分页？
0
rownumber并不会在数据量大时有影响
收获园豆：15
SELECT TOP (20) [Extent1].[CollectionID] AS [CollectionID] FROM ( SELECT [Extent1].[CollectionID] AS [CollectionID] , row_number() OVER (ORDER BY [Extent1].[CollectionID] ASC) AS [row_number] FROM [dbo].[AC_Collection] AS [Extent1] ) AS [Extent1] WHERE [Extent1].[row_number] > 20 ORDER BY [Extent1].[CollectionID] ASC
類似這樣的子查詢，會不會先查子查詢的所有數據？並且標上rownum，然後在外查詢再進行過濾的？
@滴答的雨: 然而并不会
