c# - 分页查询如何*慢*？-6ren

c# - 分页查询如何慢？

转载作者：行者123 更新时间：2023-11-30 17:15:31

我正在计时查询和同一个查询的执行时间，分页。

foreach (var x in productSource.OrderBy(p => p.AdminDisplayName)
       .Where(p => allIds.Any(val => val == p.SiteProductId))) ;

foreach (var x in productSource.OrderBy(p => p.AdminDisplayName)
       .Where(p => allIds.Any(val => val == p.SiteProductId)).Skip(20).Take(20)) ;

不知何故，第一个查询耗时 0.5 秒，而第二个查询耗时三倍。这怎么可能？不幸的是 allIds 相当复杂，因此生成的 SQL 非常长。我正在使用 Linq-to-SQL，这就是我使用 Any 而不是 Contains 的原因，因为后者会导致像这样的复杂查询出错。

编辑

当返回的结果集较大时，分页查询运行得更快(绝对时间)。当基本查询返回 6,000 行(分页前)时，分页版本运行 1.7 秒。当基本查询返回 200 行(分页前)时，分页版本运行 1.7 秒。这对我来说似乎很疯狂。

编辑 2

我被要求提供查询执行计划。我已经浏览过它们两个，它们看起来是相同的，除了最开始。以下是实际不同的部分。

未分页 enter image description here

分页 enter image description here

结束编辑

SELECT [t0].[SiteProductId], [t0].[SiteId], [t0].[SiteDivisionId], [t0].[ProductDisplayId], [t0].[ItemId], [t0].[SiteProductTypeId], [t0].[PrimaryParentSiteCategoryId], [t0].[PrimaryParentSiteProductId], [t0].[PrimaryChildSiteProductId], [t0].[UsesMasterPrice], [t0].[ListPrice], [t0].[SalePrice], [t0].[ShowWasIsPricing], [t0].[ArrivalDate], [t0].[SiteUrlKey], [t0].[IsDisplayedOnIndexPages], [t0].[HasDetailPage], [t0].[IsPersonalizable], [t0].[RequiresPersonalization], [t0].[ShowPersonalizationInline], [t0].[PzTemplateId], [t0].[AdminDisplayName], [t0].[DetailPageHeading], [t0].[SiteLabelForIndex], [t0].[SiteLabelForDetail], [t0].[UsesVariantAttributes], [t0].[VariantSelectionPrompt], [t0].[VariantSelectionOptionLabel], [t0].[VariantSortOrder], [t0].[VariantSelectionImageAssignmentId], [t0].[IndexImageAssignmentId], [t0].[DetailImageAssignmentId], [t0].[SiteProductDescription], [t0].[SiteTargetSearchTerms], [t0].[SiteWebPageTitle], [t0].[SiteWebPageKeywords], [t0].[SiteWebPageDescription], [t0].[ItemStatusId], [t0].[UsesMasterInventory], [t0].[CurrentInventory], [t0].[RestockDate], [t0].[IsBackorderable], [t0].[IsPreorderable], [t0].[OutOfStockLevel], [t0].[CreatedBy], [t0].[CreatedDT], [t0].[ModifiedBy], [t0].[ModifiedDT], [t0].[UsesPixamiPreview], [t0].[ShowsDynamicPreview], [t0].[IsNewProduct], [t0].[IsExclusiveProduct], [t0].[IsInternetOnlyProduct], [t0].[IsCustomerFavorite], [t0].[StartDate], [t0].[EndDate], [t0].[InternalKeywords], [t0].[ProductAlert], [t0].[AdditionalProductInfo], [t0].[UsesPixamiPz], [t0].[IsFreeGift], [t2].[test], [t2].[ItemId] AS [ItemId2], [t2].[ItemSku], [t2].[ErpItemId], [t2].[SupplierSku], [t2].[VendorSku], [t2].[UPC], [t2].[SerialNumber], [t2].[DisplayName], [t2].[IsPersonalizable] AS [IsPersonalizable2], [t2].[RequiresPersonalization] AS [RequiresPersonalization2], [t2].[ListPrice] AS [ListPrice2], [t2].[ItemTypeId], [t2].[ItemTypeCode], [t2].[DisplayIndividuallyOnSite], [t2].[ItemStatusId] AS [ItemStatusId2], [t2].[ItemStatusCode], [t2].[ParentItemId], [t2].[VariantTemplateCode], [t2].[PzFormatCode], [t2].[OmsPzTemplateId], [t2].[Height], [t2].[Width], [t2].[Depth], [t2].[Weight], [t2].[CurrentInventory] AS [CurrentInventory2], [t2].[RestockDate] AS [RestockDate2], [t2].[IsTaxable], [t2].[PostHand], [t2].[LastSyncDate], [t2].[CreatedBy] AS [CreatedBy2], [t2].[CreatedDT] AS [CreatedDT2], [t2].[ModifiedBy] AS [ModifiedBy2], [t2].[ModifiedDT] AS [ModifiedDT2]
FROM [dbo].[SiteProduct] AS [t0]
LEFT OUTER JOIN (
    SELECT 1 AS [test], [t1].[ItemId], [t1].[ItemSku], [t1].[ErpItemId], [t1].[SupplierSku], [t1].[VendorSku], [t1].[UPC], [t1].[SerialNumber], [t1].[DisplayName], [t1].[IsPersonalizable], [t1].[RequiresPersonalization], [t1].[ListPrice], [t1].[ItemTypeId], [t1].[ItemTypeCode], [t1].[DisplayIndividuallyOnSite], [t1].[ItemStatusId], [t1].[ItemStatusCode], [t1].[ParentItemId], [t1].[VariantTemplateCode], [t1].[PzFormatCode], [t1].[OmsPzTemplateId], [t1].[Height], [t1].[Width], [t1].[Depth], [t1].[Weight], [t1].[CurrentInventory], [t1].[RestockDate], [t1].[IsTaxable], [t1].[PostHand], [t1].[LastSyncDate], [t1].[CreatedBy], [t1].[CreatedDT], [t1].[ModifiedBy], [t1].[ModifiedDT]
    FROM [dbo].[ItemMaster] AS [t1]
    ) AS [t2] ON [t2].[ItemId] = [t0].[ItemId]
WHERE EXISTS(
    SELECT NULL AS [EMPTY]
    FROM (
        SELECT [t43].[SiteProductId]
        FROM (
            SELECT [t28].[SiteProductId]
            FROM (
                SELECT [t13].[SiteProductId]
                FROM (
                    SELECT [t3].[SiteProductId]
                    FROM [dbo].[SiteProduct] AS [t3]
                    LEFT OUTER JOIN [dbo].[ItemMaster] AS [t4] ON [t4].[ItemId] = [t3].[ItemId]
                    WHERE ([t3].[SiteProductTypeId] <> @p0) AND (([t3].[AdminDisplayName] LIKE @p1) OR ([t4].[ItemSku] LIKE @p2)) AND ([t3].[SiteId] = @p3)
                    UNION
                    SELECT [t12].[SiteProductId]
                    FROM (
                        SELECT [t5].[SiteProductId]
                        FROM [dbo].[SiteProduct] AS [t5]
                        LEFT OUTER JOIN [dbo].[ItemMaster] AS [t6] ON [t6].[ItemId] = [t5].[ItemId]
                        WHERE ([t5].[PrimaryParentSiteProductId] IS NOT NULL) AND (EXISTS(
                            SELECT NULL AS [EMPTY]
                            FROM (
                                SELECT [t7].[PrimaryParentSiteProductId] AS [value], [t7].[PrimaryParentSiteProductId], [t7].[SiteProductTypeId], [t7].[AdminDisplayName], [t8].[ItemSku], [t7].[SiteId]
                                FROM [dbo].[SiteProduct] AS [t7]
                                LEFT OUTER JOIN [dbo].[ItemMaster] AS [t8] ON [t8].[ItemId] = [t7].[ItemId]
                                ) AS [t9]
                            WHERE ([t9].[value] = ([t5].[PrimaryParentSiteProductId])) AND ([t9].[PrimaryParentSiteProductId] IS NOT NULL) AND ([t9].[SiteProductTypeId] <> @p4) AND (([t9].[AdminDisplayName] LIKE @p5) OR ([t9].[ItemSku] LIKE @p6)) AND ([t9].[SiteId] = @p7)
                            ))
                        UNION
                        SELECT [t10].[PrimaryParentSiteProductId] AS [value]
                        FROM [dbo].[SiteProduct] AS [t10]
                        LEFT OUTER JOIN [dbo].[ItemMaster] AS [t11] ON [t11].[ItemId] = [t10].[ItemId]
                        WHERE ([t10].[PrimaryParentSiteProductId] IS NOT NULL) AND ([t10].[SiteProductTypeId] <> @p8) AND (([t10].[AdminDisplayName] LIKE @p9) OR ([t11].[ItemSku] LIKE @p10)) AND ([t10].[SiteId] = @p11)
                        ) AS [t12]
                    ) AS [t13]
                UNION
                SELECT [t14].[ParentSiteProductId]
                FROM [dbo].[SiteProductAssociation] AS [t14]
                INNER JOIN [dbo].[SiteProductAssociationType] AS [t15] ON [t15].[SiteProductAssociationTypeId] = [t14].[SiteProductAssociationTypeId]
                WHERE (EXISTS(
                    SELECT NULL AS [EMPTY]
                    FROM (
                        SELECT [t26].[SiteProductId]
                        FROM (
                            SELECT [t16].[SiteProductId]
                            FROM [dbo].[SiteProduct] AS [t16]
                            LEFT OUTER JOIN [dbo].[ItemMaster] AS [t17] ON [t17].[ItemId] = [t16].[ItemId]
                            WHERE ([t16].[SiteProductTypeId] <> @p12) AND (([t16].[AdminDisplayName] LIKE @p13) OR ([t17].[ItemSku] LIKE @p14)) AND ([t16].[SiteId] = @p15)
                            UNION
                            SELECT [t25].[SiteProductId]
                            FROM (
                                SELECT [t18].[SiteProductId]
                                FROM [dbo].[SiteProduct] AS [t18]
                                LEFT OUTER JOIN [dbo].[ItemMaster] AS [t19] ON [t19].[ItemId] = [t18].[ItemId]
                                WHERE ([t18].[PrimaryParentSiteProductId] IS NOT NULL) AND (EXISTS(
                                    SELECT NULL AS [EMPTY]
                                    FROM (
                                        SELECT [t20].[PrimaryParentSiteProductId] AS [value], [t20].[PrimaryParentSiteProductId], [t20].[SiteProductTypeId], [t20].[AdminDisplayName], [t21].[ItemSku], [t20].[SiteId]
                                        FROM [dbo].[SiteProduct] AS [t20]
                                        LEFT OUTER JOIN [dbo].[ItemMaster] AS [t21] ON [t21].[ItemId] = [t20].[ItemId]
                                        ) AS [t22]
                                    WHERE ([t22].[value] = ([t18].[PrimaryParentSiteProductId])) AND ([t22].[PrimaryParentSiteProductId] IS NOT NULL) AND ([t22].[SiteProductTypeId] <> @p16) AND (([t22].[AdminDisplayName] LIKE @p17) OR ([t22].[ItemSku] LIKE @p18)) AND ([t22].[SiteId] = @p19)
                                    ))
                                UNION
                                SELECT [t23].[PrimaryParentSiteProductId] AS [value]
                                FROM [dbo].[SiteProduct] AS [t23]
                                LEFT OUTER JOIN [dbo].[ItemMaster] AS [t24] ON [t24].[ItemId] = [t23].[ItemId]
                                WHERE ([t23].[PrimaryParentSiteProductId] IS NOT NULL) AND ([t23].[SiteProductTypeId] <> @p20) AND (([t23].[AdminDisplayName] LIKE @p21) OR ([t24].[ItemSku] LIKE @p22)) AND ([t23].[SiteId] = @p23)
                                ) AS [t25]
                            ) AS [t26]
                        ) AS [t27]
                    WHERE [t27].[SiteProductId] = [t14].[ChildSiteProductId]
                    )) AND ([t14].[SiteProductAssociationTypeId] = @p24)
                ) AS [t28]
            UNION
            SELECT [t29].[ChildSiteProductId]
            FROM [dbo].[SiteProductAssociation] AS [t29]
            INNER JOIN [dbo].[SiteProductAssociationType] AS [t30] ON [t30].[SiteProductAssociationTypeId] = [t29].[SiteProductAssociationTypeId]
            WHERE (EXISTS(
                SELECT NULL AS [EMPTY]
                FROM (
                    SELECT [t41].[SiteProductId]
                    FROM (
                        SELECT [t31].[SiteProductId]
                        FROM [dbo].[SiteProduct] AS [t31]
                        LEFT OUTER JOIN [dbo].[ItemMaster] AS [t32] ON [t32].[ItemId] = [t31].[ItemId]
                        WHERE ([t31].[SiteProductTypeId] <> @p25) AND (([t31].[AdminDisplayName] LIKE @p26) OR ([t32].[ItemSku] LIKE @p27)) AND ([t31].[SiteId] = @p28)
                        UNION
                        SELECT [t40].[SiteProductId]
                        FROM (
                            SELECT [t33].[SiteProductId]
                            FROM [dbo].[SiteProduct] AS [t33]
                            LEFT OUTER JOIN [dbo].[ItemMaster] AS [t34] ON [t34].[ItemId] = [t33].[ItemId]
                            WHERE ([t33].[PrimaryParentSiteProductId] IS NOT NULL) AND (EXISTS(
                                SELECT NULL AS [EMPTY]
                                FROM (
                                    SELECT [t35].[PrimaryParentSiteProductId] AS [value], [t35].[PrimaryParentSiteProductId], [t35].[SiteProductTypeId], [t35].[AdminDisplayName], [t36].[ItemSku], [t35].[SiteId]
                                    FROM [dbo].[SiteProduct] AS [t35]
                                    LEFT OUTER JOIN [dbo].[ItemMaster] AS [t36] ON [t36].[ItemId] = [t35].[ItemId]
                                    ) AS [t37]
                                WHERE ([t37].[value] = ([t33].[PrimaryParentSiteProductId])) AND ([t37].[PrimaryParentSiteProductId] IS NOT NULL) AND ([t37].[SiteProductTypeId] <> @p29) AND (([t37].[AdminDisplayName] LIKE @p30) OR ([t37].[ItemSku] LIKE @p31)) AND ([t37].[SiteId] = @p32)
                                ))
                            UNION
                            SELECT [t38].[PrimaryParentSiteProductId] AS [value]
                            FROM [dbo].[SiteProduct] AS [t38]
                            LEFT OUTER JOIN [dbo].[ItemMaster] AS [t39] ON [t39].[ItemId] = [t38].[ItemId]
                            WHERE ([t38].[PrimaryParentSiteProductId] IS NOT NULL) AND ([t38].[SiteProductTypeId] <> @p33) AND (([t38].[AdminDisplayName] LIKE @p34) OR ([t39].[ItemSku] LIKE @p35)) AND ([t38].[SiteId] = @p36)
                            ) AS [t40]
                        ) AS [t41]
                    ) AS [t42]
                WHERE [t42].[SiteProductId] = [t29].[ParentSiteProductId]
                )) AND ([t29].[SiteProductAssociationTypeId] = @p37)
            ) AS [t43]
        ) AS [t44]
    WHERE [t44].[SiteProductId] = [t0].[SiteProductId]
    )
ORDER BY [t0].[AdminDisplayName]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1

SELECT [t45].[SiteProductId], [t45].[SiteId], [t45].[SiteDivisionId], [t45].[ProductDisplayId], [t45].[ItemId], [t45].[SiteProductTypeId], [t45].[PrimaryParentSiteCategoryId], [t45].[PrimaryParentSiteProductId], [t45].[PrimaryChildSiteProductId], [t45].[UsesMasterPrice], [t45].[ListPrice], [t45].[SalePrice], [t45].[ShowWasIsPricing], [t45].[ArrivalDate], [t45].[SiteUrlKey], [t45].[IsDisplayedOnIndexPages], [t45].[HasDetailPage], [t45].[IsPersonalizable], [t45].[RequiresPersonalization], [t45].[ShowPersonalizationInline], [t45].[PzTemplateId], [t45].[AdminDisplayName], [t45].[DetailPageHeading], [t45].[SiteLabelForIndex], [t45].[SiteLabelForDetail], [t45].[UsesVariantAttributes], [t45].[VariantSelectionPrompt], [t45].[VariantSelectionOptionLabel], [t45].[VariantSortOrder], [t45].[VariantSelectionImageAssignmentId], [t45].[IndexImageAssignmentId], [t45].[DetailImageAssignmentId], [t45].[SiteProductDescription], [t45].[SiteTargetSearchTerms], [t45].[SiteWebPageTitle], [t45].[SiteWebPageKeywords], [t45].[SiteWebPageDescription], [t45].[ItemStatusId], [t45].[UsesMasterInventory], [t45].[CurrentInventory], [t45].[RestockDate], [t45].[IsBackorderable], [t45].[IsPreorderable], [t45].[OutOfStockLevel], [t45].[CreatedBy], [t45].[CreatedDT], [t45].[ModifiedBy], [t45].[ModifiedDT], [t45].[UsesPixamiPreview], [t45].[ShowsDynamicPreview], [t45].[IsNewProduct], [t45].[IsExclusiveProduct], [t45].[IsInternetOnlyProduct], [t45].[IsCustomerFavorite], [t45].[StartDate], [t45].[EndDate], [t45].[InternalKeywords], [t45].[ProductAlert], [t45].[AdditionalProductInfo], [t45].[UsesPixamiPz], [t45].[IsFreeGift], [t45].[test], [t45].[ItemId2], [t45].[ItemSku], [t45].[ErpItemId], [t45].[SupplierSku], [t45].[VendorSku], [t45].[UPC], [t45].[SerialNumber], [t45].[DisplayName], [t45].[IsPersonalizable2], [t45].[RequiresPersonalization2], [t45].[ListPrice2], [t45].[ItemTypeId], [t45].[ItemTypeCode], [t45].[DisplayIndividuallyOnSite], [t45].[ItemStatusId2], [t45].[ItemStatusCode], [t45].[ParentItemId], [t45].[VariantTemplateCode], [t45].[PzFormatCode], [t45].[OmsPzTemplateId], [t45].[Height], [t45].[Width], [t45].[Depth], [t45].[Weight], [t45].[CurrentInventory2], [t45].[RestockDate2], [t45].[IsTaxable], [t45].[PostHand], [t45].[LastSyncDate], [t45].[CreatedBy2], [t45].[CreatedDT2], [t45].[ModifiedBy2], [t45].[ModifiedDT2]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[AdminDisplayName]) AS [ROW_NUMBER], [t0].[SiteProductId], [t0].[SiteId], [t0].[SiteDivisionId], [t0].[ProductDisplayId], [t0].[ItemId], [t0].[SiteProductTypeId], [t0].[PrimaryParentSiteCategoryId], [t0].[PrimaryParentSiteProductId], [t0].[PrimaryChildSiteProductId], [t0].[UsesMasterPrice], [t0].[ListPrice], [t0].[SalePrice], [t0].[ShowWasIsPricing], [t0].[ArrivalDate], [t0].[SiteUrlKey], [t0].[IsDisplayedOnIndexPages], [t0].[HasDetailPage], [t0].[IsPersonalizable], [t0].[RequiresPersonalization], [t0].[ShowPersonalizationInline], [t0].[PzTemplateId], [t0].[AdminDisplayName], [t0].[DetailPageHeading], [t0].[SiteLabelForIndex], [t0].[SiteLabelForDetail], [t0].[UsesVariantAttributes], [t0].[VariantSelectionPrompt], [t0].[VariantSelectionOptionLabel], [t0].[VariantSortOrder], [t0].[VariantSelectionImageAssignmentId], [t0].[IndexImageAssignmentId], [t0].[DetailImageAssignmentId], [t0].[SiteProductDescription], [t0].[SiteTargetSearchTerms], [t0].[SiteWebPageTitle], [t0].[SiteWebPageKeywords], [t0].[SiteWebPageDescription], [t0].[ItemStatusId], [t0].[UsesMasterInventory], [t0].[CurrentInventory], [t0].[RestockDate], [t0].[IsBackorderable], [t0].[IsPreorderable], [t0].[OutOfStockLevel], [t0].[CreatedBy], [t0].[CreatedDT], [t0].[ModifiedBy], [t0].[ModifiedDT], [t0].[UsesPixamiPreview], [t0].[ShowsDynamicPreview], [t0].[IsNewProduct], [t0].[IsExclusiveProduct], [t0].[IsInternetOnlyProduct], [t0].[IsCustomerFavorite], [t0].[StartDate], [t0].[EndDate], [t0].[InternalKeywords], [t0].[ProductAlert], [t0].[AdditionalProductInfo], [t0].[UsesPixamiPz], [t0].[IsFreeGift], [t2].[test], [t2].[ItemId] AS [ItemId2], [t2].[ItemSku], [t2].[ErpItemId], [t2].[SupplierSku], [t2].[VendorSku], [t2].[UPC], [t2].[SerialNumber], [t2].[DisplayName], [t2].[IsPersonalizable] AS [IsPersonalizable2], [t2].[RequiresPersonalization] AS [RequiresPersonalization2], [t2].[ListPrice] AS [ListPrice2], [t2].[ItemTypeId], [t2].[ItemTypeCode], [t2].[DisplayIndividuallyOnSite], [t2].[ItemStatusId] AS [ItemStatusId2], [t2].[ItemStatusCode], [t2].[ParentItemId], [t2].[VariantTemplateCode], [t2].[PzFormatCode], [t2].[OmsPzTemplateId], [t2].[Height], [t2].[Width], [t2].[Depth], [t2].[Weight], [t2].[CurrentInventory] AS [CurrentInventory2], [t2].[RestockDate] AS [RestockDate2], [t2].[IsTaxable], [t2].[PostHand], [t2].[LastSyncDate], [t2].[CreatedBy] AS [CreatedBy2], [t2].[CreatedDT] AS [CreatedDT2], [t2].[ModifiedBy] AS [ModifiedBy2], [t2].[ModifiedDT] AS [ModifiedDT2]
    FROM [dbo].[SiteProduct] AS [t0]
    LEFT OUTER JOIN (
        SELECT 1 AS [test], [t1].[ItemId], [t1].[ItemSku], [t1].[ErpItemId], [t1].[SupplierSku], [t1].[VendorSku], [t1].[UPC], [t1].[SerialNumber], [t1].[DisplayName], [t1].[IsPersonalizable], [t1].[RequiresPersonalization], [t1].[ListPrice], [t1].[ItemTypeId], [t1].[ItemTypeCode], [t1].[DisplayIndividuallyOnSite], [t1].[ItemStatusId], [t1].[ItemStatusCode], [t1].[ParentItemId], [t1].[VariantTemplateCode], [t1].[PzFormatCode], [t1].[OmsPzTemplateId], [t1].[Height], [t1].[Width], [t1].[Depth], [t1].[Weight], [t1].[CurrentInventory], [t1].[RestockDate], [t1].[IsTaxable], [t1].[PostHand], [t1].[LastSyncDate], [t1].[CreatedBy], [t1].[CreatedDT], [t1].[ModifiedBy], [t1].[ModifiedDT]
        FROM [dbo].[ItemMaster] AS [t1]
        ) AS [t2] ON [t2].[ItemId] = [t0].[ItemId]
    WHERE EXISTS(
        SELECT NULL AS [EMPTY]
        FROM (
            SELECT [t43].[SiteProductId]
            FROM (
                SELECT [t28].[SiteProductId]
                FROM (
                    SELECT [t13].[SiteProductId]
                    FROM (
                        SELECT [t3].[SiteProductId]
                        FROM [dbo].[SiteProduct] AS [t3]
                        LEFT OUTER JOIN [dbo].[ItemMaster] AS [t4] ON [t4].[ItemId] = [t3].[ItemId]
                        WHERE ([t3].[SiteProductTypeId] <> @p0) AND (([t3].[AdminDisplayName] LIKE @p1) OR ([t4].[ItemSku] LIKE @p2)) AND ([t3].[SiteId] = @p3)
                        UNION
                        SELECT [t12].[SiteProductId]
                        FROM (
                            SELECT [t5].[SiteProductId]
                            FROM [dbo].[SiteProduct] AS [t5]
                            LEFT OUTER JOIN [dbo].[ItemMaster] AS [t6] ON [t6].[ItemId] = [t5].[ItemId]
                            WHERE ([t5].[PrimaryParentSiteProductId] IS NOT NULL) AND (EXISTS(
                                SELECT NULL AS [EMPTY]
                                FROM (
                                    SELECT [t7].[PrimaryParentSiteProductId] AS [value], [t7].[PrimaryParentSiteProductId], [t7].[SiteProductTypeId], [t7].[AdminDisplayName], [t8].[ItemSku], [t7].[SiteId]
                                    FROM [dbo].[SiteProduct] AS [t7]
                                    LEFT OUTER JOIN [dbo].[ItemMaster] AS [t8] ON [t8].[ItemId] = [t7].[ItemId]
                                    ) AS [t9]
                                WHERE ([t9].[value] = ([t5].[PrimaryParentSiteProductId])) AND ([t9].[PrimaryParentSiteProductId] IS NOT NULL) AND ([t9].[SiteProductTypeId] <> @p4) AND (([t9].[AdminDisplayName] LIKE @p5) OR ([t9].[ItemSku] LIKE @p6)) AND ([t9].[SiteId] = @p7)
                                ))
                            UNION
                            SELECT [t10].[PrimaryParentSiteProductId] AS [value]
                            FROM [dbo].[SiteProduct] AS [t10]
                            LEFT OUTER JOIN [dbo].[ItemMaster] AS [t11] ON [t11].[ItemId] = [t10].[ItemId]
                            WHERE ([t10].[PrimaryParentSiteProductId] IS NOT NULL) AND ([t10].[SiteProductTypeId] <> @p8) AND (([t10].[AdminDisplayName] LIKE @p9) OR ([t11].[ItemSku] LIKE @p10)) AND ([t10].[SiteId] = @p11)
                            ) AS [t12]
                        ) AS [t13]
                    UNION
                    SELECT [t14].[ParentSiteProductId]
                    FROM [dbo].[SiteProductAssociation] AS [t14]
                    INNER JOIN [dbo].[SiteProductAssociationType] AS [t15] ON [t15].[SiteProductAssociationTypeId] = [t14].[SiteProductAssociationTypeId]
                    WHERE (EXISTS(
                        SELECT NULL AS [EMPTY]
                        FROM (
                            SELECT [t26].[SiteProductId]
                            FROM (
                                SELECT [t16].[SiteProductId]
                                FROM [dbo].[SiteProduct] AS [t16]
                                LEFT OUTER JOIN [dbo].[ItemMaster] AS [t17] ON [t17].[ItemId] = [t16].[ItemId]
                                WHERE ([t16].[SiteProductTypeId] <> @p12) AND (([t16].[AdminDisplayName] LIKE @p13) OR ([t17].[ItemSku] LIKE @p14)) AND ([t16].[SiteId] = @p15)
                                UNION
                                SELECT [t25].[SiteProductId]
                                FROM (
                                    SELECT [t18].[SiteProductId]
                                    FROM [dbo].[SiteProduct] AS [t18]
                                    LEFT OUTER JOIN [dbo].[ItemMaster] AS [t19] ON [t19].[ItemId] = [t18].[ItemId]
                                    WHERE ([t18].[PrimaryParentSiteProductId] IS NOT NULL) AND (EXISTS(
                                        SELECT NULL AS [EMPTY]
                                        FROM (
                                            SELECT [t20].[PrimaryParentSiteProductId] AS [value], [t20].[PrimaryParentSiteProductId], [t20].[SiteProductTypeId], [t20].[AdminDisplayName], [t21].[ItemSku], [t20].[SiteId]
                                            FROM [dbo].[SiteProduct] AS [t20]
                                            LEFT OUTER JOIN [dbo].[ItemMaster] AS [t21] ON [t21].[ItemId] = [t20].[ItemId]
                                            ) AS [t22]
                                        WHERE ([t22].[value] = ([t18].[PrimaryParentSiteProductId])) AND ([t22].[PrimaryParentSiteProductId] IS NOT NULL) AND ([t22].[SiteProductTypeId] <> @p16) AND (([t22].[AdminDisplayName] LIKE @p17) OR ([t22].[ItemSku] LIKE @p18)) AND ([t22].[SiteId] = @p19)
                                        ))
                                    UNION
                                    SELECT [t23].[PrimaryParentSiteProductId] AS [value]
                                    FROM [dbo].[SiteProduct] AS [t23]
                                    LEFT OUTER JOIN [dbo].[ItemMaster] AS [t24] ON [t24].[ItemId] = [t23].[ItemId]
                                    WHERE ([t23].[PrimaryParentSiteProductId] IS NOT NULL) AND ([t23].[SiteProductTypeId] <> @p20) AND (([t23].[AdminDisplayName] LIKE @p21) OR ([t24].[ItemSku] LIKE @p22)) AND ([t23].[SiteId] = @p23)
                                    ) AS [t25]
                                ) AS [t26]
                            ) AS [t27]
                        WHERE [t27].[SiteProductId] = [t14].[ChildSiteProductId]
                        )) AND ([t14].[SiteProductAssociationTypeId] = @p24)
                    ) AS [t28]
                UNION
                SELECT [t29].[ChildSiteProductId]
                FROM [dbo].[SiteProductAssociation] AS [t29]
                INNER JOIN [dbo].[SiteProductAssociationType] AS [t30] ON [t30].[SiteProductAssociationTypeId] = [t29].[SiteProductAssociationTypeId]
                WHERE (EXISTS(
                    SELECT NULL AS [EMPTY]
                    FROM (
                        SELECT [t41].[SiteProductId]
                        FROM (
                            SELECT [t31].[SiteProductId]
                            FROM [dbo].[SiteProduct] AS [t31]
                            LEFT OUTER JOIN [dbo].[ItemMaster] AS [t32] ON [t32].[ItemId] = [t31].[ItemId]
                            WHERE ([t31].[SiteProductTypeId] <> @p25) AND (([t31].[AdminDisplayName] LIKE @p26) OR ([t32].[ItemSku] LIKE @p27)) AND ([t31].[SiteId] = @p28)
                            UNION
                            SELECT [t40].[SiteProductId]
                            FROM (
                                SELECT [t33].[SiteProductId]
                                FROM [dbo].[SiteProduct] AS [t33]
                                LEFT OUTER JOIN [dbo].[ItemMaster] AS [t34] ON [t34].[ItemId] = [t33].[ItemId]
                                WHERE ([t33].[PrimaryParentSiteProductId] IS NOT NULL) AND (EXISTS(
                                    SELECT NULL AS [EMPTY]
                                    FROM (
                                        SELECT [t35].[PrimaryParentSiteProductId] AS [value], [t35].[PrimaryParentSiteProductId], [t35].[SiteProductTypeId], [t35].[AdminDisplayName], [t36].[ItemSku], [t35].[SiteId]
                                        FROM [dbo].[SiteProduct] AS [t35]
                                        LEFT OUTER JOIN [dbo].[ItemMaster] AS [t36] ON [t36].[ItemId] = [t35].[ItemId]
                                        ) AS [t37]
                                    WHERE ([t37].[value] = ([t33].[PrimaryParentSiteProductId])) AND ([t37].[PrimaryParentSiteProductId] IS NOT NULL) AND ([t37].[SiteProductTypeId] <> @p29) AND (([t37].[AdminDisplayName] LIKE @p30) OR ([t37].[ItemSku] LIKE @p31)) AND ([t37].[SiteId] = @p32)
                                    ))
                                UNION
                                SELECT [t38].[PrimaryParentSiteProductId] AS [value]
                                FROM [dbo].[SiteProduct] AS [t38]
                                LEFT OUTER JOIN [dbo].[ItemMaster] AS [t39] ON [t39].[ItemId] = [t38].[ItemId]
                                WHERE ([t38].[PrimaryParentSiteProductId] IS NOT NULL) AND ([t38].[SiteProductTypeId] <> @p33) AND (([t38].[AdminDisplayName] LIKE @p34) OR ([t39].[ItemSku] LIKE @p35)) AND ([t38].[SiteId] = @p36)
                                ) AS [t40]
                            ) AS [t41]
                        ) AS [t42]
                    WHERE [t42].[SiteProductId] = [t29].[ParentSiteProductId]
                    )) AND ([t29].[SiteProductAssociationTypeId] = @p37)
                ) AS [t43]
            ) AS [t44]
        WHERE [t44].[SiteProductId] = [t0].[SiteProductId]
        )
    ) AS [t45]
WHERE [t45].[ROW_NUMBER] BETWEEN @p38 + 1 AND @p38 + @p39
ORDER BY [t45].[ROW_NUMBER]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1

最佳答案

生成的 SQL 没有使用“select top X from”语法，而是使用 ROW_NUMBER() 函数，然后根据它进行选择。
这当然是一个非常繁重的操作，因为为每一行调用该函数，然后才评估“WHERE”子句，而“SELECT TOP X”语法只是在选择 X 行后停止执行。

我不能告诉你是否可以在 LINQ2SQL 中修复这个问题，但我很确定 Entity Framework 使用“SELECT TOP X FROM”语法，虽然我不能肯定地告诉你。

我不知道迁移到 Entity Framework 对您来说有多复杂，但我可以肯定地告诉您，如果您使用 .NET 4.0，它将在很多方面让您的生活更轻松，并且性能会更好 Entity Framework 而不是 .NET 3.5 SP1。

关于c# - 分页查询如何*慢*？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/7955626/

文章推荐： javascript - Passport 文档中的 var User 来自哪里

文章推荐： javascript - 有没有办法将多个函数传递给 AngularJS 指令？

文章推荐： javascript - checkbox 有 checked 属性但不显示 checked

Mysql 查询 JOIN 查询
我有三张 table 。表 A 有选项名称(即颜色、尺寸)。表 B 有选项值名称(即蓝色、红色、黑色等)。表C通过将选项名称id和选项名称值id放在一起来建立关系。我的查询需要显示值和选项的名称，而
查询
在mysql中，如何计算一行中的非空单元格？我只想计算某些列之间的单元格，比如第 3-10 列之间的单元格。不是所有的列...同样，仅在该行中。最佳答案如果你想这样做，只能在 sql 中使用名称而
sql - 查询、 native 查询、命名查询和类型化查询之间的区别
关闭。这个问题需要多问focused 。目前不接受答案。想要改进此问题吗？更新问题，使其仅关注一个问题 editing this post . 已关闭 7 年前。 Improve this ques
elasticsearch - 在Elasticsearch查询中没有为[查询]注册的[查询]
我正在为版本7.6进行Elasticsearch查询我的查询是这样的: { "query": { "bool": { "should": [ {
sql - 查询、 native 查询、命名查询和类型化查询之间的区别
关闭。这个问题需要多问focused 。目前不接受答案。想要改进此问题吗？更新问题，使其仅关注一个问题 editing this post . 已关闭 7 年前。 Improve this ques
php - Mysql WHERE NOT EXISTS(查询)OR(查询)
是否可以编写一个查询来检查任一子查询(而不是一个子查询)是否正确？ SELECT * FROM employees e WHERE NOT EXISTS (
javascript - 查询。为表中的每一行发送 ajax 查询
我找到了很多关于我的问题的答案，但问题没有解决我有表格，有数据，例如: Data 1 Data 2 Data 3
salesforce - SOQL 查询 - 如何通过将字段设为小写并进行比较来编写 SOQL 查询？
以下查询返回错误: 查询: SELECT Id, FirstName, LastName, OwnerId, PersonEmail FROM Account WHERE lower(PersonEm
salesforce - SOQL 查询 - 如何通过将字段设为小写并进行比较来编写 SOQL 查询？
以下查询返回错误: 查询: SELECT Id, FirstName, LastName, OwnerId, PersonEmail FROM Account WHERE lower(PersonEm
Android SQLite 查询(我想解析一般的 SQL 查询)
我从 EditText 中获取了 String 值。以及提交查询的按钮。 String sql=editQuery.getText().toString();// SELECT * FROM empl
mysql 查询 - 为一个巨大的表优化现有的 MAX-MIN 查询
我有一个或多或少有效的查询(关于结果)，但处理大约需要 45 秒。这对于在 GUI 中呈现数据来说肯定太长了。所以我的需求是找到一个更快/更高效的查询(几毫秒左右会很好)我的数据表大约有 3000
SQL 查询 - 将 NULL 结果添加到 SELECT 查询
这是我第一次使用 Stack Overflow，所以我希望我以正确的方式提出这个问题。我有 2 个 SQL 查询，我正在尝试比较和识别缺失值，尽管我无法将 NULL 字段添加到第二个查询中以识别缺失
sql - 什么是动态 SQL 查询？何时需要使用动态 SQL 查询？
什么是动态 SQL 查询？何时需要使用动态 SQL 查询？我使用的是 SQL Server 2005。最佳答案这里有几篇文章: Introduction to Dynamic SQL Dynami
php - 在另一个 mysql 查询 while 循环中调用 mysql 查询
include "mysql.php"; $query= "SELECT ID,name,displayname,established,summary,searchlink,im
java - MySQL 查询 "select top 5"查询
我有一个查询要“转换”为 mysql。这是查询: select top 5 * from (select id, firstName, lastName, sum(fileSize) as To
c# - Entity Framework 查询 ToString 不会产生 SQL 查询
通过我的研究，我发现至少从 EF 4.1 开始，EF 查询上的 .ToString() 方法将返回要运行的 SQL。事实上，这对我来说非常有用，使用 Entity Framework 5 和 6。但
MySQL 查询(或 Doctrine 1.2 查询)- 从连接表和过滤器中获取最新项目
我在构造查询来执行以下操作时遇到问题: 按activity_type_id过滤联系人，仅显示最近事件具有所需activity_type_id或为NULL(无事件)的联系人表格结构如下: 一个联系人可
php - 如何在执行另一个 SQL 查询 x 分钟后执行一个 SQL 查询？
如何让我输入数据库的信息在输入数据 5 分钟后自行更新？假设我有一张 table : +--+--+-----+ |id|ip|count| +--+--+-----+ |
database - 如何在 N1QL 查询(Couchbase 查询)中使用 LENGTH() 字符串函数
我正在尝试搜索正好是 4 位数字的 ID，我知道我需要使用 LENGTH() 字符串函数，但找不到如何使用它的示例。我正在尝试以下(和其他变体)但它们不起作用。 SELECT max(car_id)
php - 将 SQL 查询 (+JOIN) 转换为 Symfony Propel 查询
我有一个在 mysql 上运行良好的 sql 查询(查询 + 连接): select sum(pa.price) from user u , purchase pu , pack pa where (

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

c# - 分页查询如何慢？