首页 新闻 搜索 专区 学院

ef 生成的分頁查詢方案效率差,有解決方案嗎

0
悬赏园豆:50 [待解决问题]

最近使用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

 

滴答的雨的主页 滴答的雨 | 老鸟四级 | 园豆:3690
提问于:2015-05-12 16:05
< >
分享
所有回答(3)
0

最好能将完整的LINQ写出来了

dudu | 园豆:38830 (高人七级) | 2015-05-12 16:09

用你帖子里面现成的吧,我没做demo,直接在生产环境弄的,语句有点多

支持(0) 反对(0) 滴答的雨 | 园豆:3690 (老鸟四级) | 2015-05-12 16:14

@滴答的雨: 问题就在LINQ上,没有LINQ,无法判断。你可以精简一下,主要是看.Where的写法。

支持(0) 反对(0) dudu | 园豆:38830 (高人七级) | 2015-05-12 16:21

@滴答的雨: “问题是子查询等于要把整表检索出来”,这样的问题通常都是LINQ写法上的问题。

支持(0) 反对(0) dudu | 园豆:38830 (高人七级) | 2015-05-12 16:22

@dudu: 看来是写法问题。。。。

支持(0) 反对(0) 滴答的雨 | 园豆:3690 (老鸟四级) | 2015-05-12 16:30

@dudu: 如果一个   A集合.union(B集合).分页   这样的话,就必须A集合和B集合都要查出来了?(全部查出来后,才能进行排序)

支持(0) 反对(0) 滴答的雨 | 园豆:3690 (老鸟四级) | 2015-05-12 16:35

@滴答的雨: 没有实际的代码,很难进行讨论

支持(0) 反对(0) dudu | 园豆:38830 (高人七级) | 2015-05-12 16:48

@dudu: 代码回复在补充问题上了,你看看

支持(0) 反对(0) 滴答的雨 | 园豆:3690 (老鸟四级) | 2015-05-12 16:57

@滴答的雨: 第一次见到如此复杂的LINQ

支持(0) 反对(0) dudu | 园豆:38830 (高人七级) | 2015-05-12 17:04

@dudu: 這種寫法不好,因為union了,後面再改一改。只是今天從這個語句發現了一些問題

支持(0) 反对(0) 滴答的雨 | 园豆:3690 (老鸟四级) | 2015-05-12 17:09

@滴答的雨: 你先得把Clustered Index Scan优化掉,这是性能杀手。

支持(0) 反对(0) dudu | 园豆:38830 (高人七级) | 2015-05-12 17:13
0

如果我没记错的话 ef是真分页

小眼睛老鼠 | 园豆:2774 (老鸟四级) | 2015-05-12 16:15

真分页?

支持(0) 反对(0) 滴答的雨 | 园豆:3690 (老鸟四级) | 2015-05-12 16:18
0

rownumber并不会在数据量大时有影响

吴瑞祥 | 园豆:28830 (高人七级) | 2015-05-12 18:00
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,然後在外查詢再進行過濾的?

支持(0) 反对(0) 滴答的雨 | 园豆:3690 (老鸟四级) | 2015-05-13 09:26

@滴答的雨: 然而并不会

支持(0) 反对(0) 吴瑞祥 | 园豆:28830 (高人七级) | 2015-05-13 10:55
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册