#c# #sql-server #sql-server-2008 #linq-to-sql
#c# #sql-сервер #sql-server-2008 #linq-to-sql
Вопрос:
Я рассчитываю время выполнения как запроса, так и одного и того же запроса, выгружаемого по страницам.
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 , поскольку последнее вызывает ошибку для сложных запросов, подобных этому.
Редактировать
Похоже, что выгружаемый запрос выполняется быстрее (в абсолютное время), когда возвращаемый результирующий набор больше. Когда базовый запрос возвращает 6000 строк (до разбиения на страницы), выгружаемая версия выполняется за 1,7 секунды. Когда базовый запрос возвращает 200 строк (перед подкачкой), выгружаемая версия выполняется со скоростью 1,7 секунды. Мне это кажется безумием.
ПРАВКА 2
Меня попросили предоставить планы выполнения запросов. Я просмотрел их оба, и они кажутся идентичными, за исключением самого начала. Вот те части, которые на самом деле отличаются друг от друга.
НЕ ВЫГРУЖАЕТСЯ НА ПЕЙДЖЕР 
РАЗБИЕНИЕ на СТРАНИЦЫ 
ЗАВЕРШИТЬ РЕДАКТИРОВАНИЕ
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
Комментарии:
1. Является ли SQL, который вы показали нам для первого запроса LINQ, для второго или для обоих? Т.е. Есть ли какие-либо различия в сгенерированном SQL?
2. @Branko — оба. Единственное отличие, которое я вижу, заключается в том, что второе включает функцию ROW_NUMBER OVER, используемую для подкачки.
3. Не могли бы вы скопировать и вставить обе версии SQL (с ROW_NUMBER и без ROW_NUMBER) в SQL Server Management Studio и посмотреть, есть ли какая-либо разница в плане выполнения? Вам не нужно указывать фактические значения параметров для создания планов выполнения, поэтому вы должны иметь возможность вставлять текст SQL без каких-либо изменений.
4. Вы пробовали «объективировать»
allIds. IOW Добавить ToList() где-нибудь? В качестве альтернативы может помочь сопоставление словаря.5. @AdamRackis Еще один вопрос — когда вы говорите, что запрос «выполняется» и занимает некоторое время, вы имеете в виду «возвращает первую строку» или вы действительно извлекаете все возвращенные строки перед измерением времени?
Ответ №1:
Вместо использования синтаксиса «select top X from» сгенерированный SQL использует функцию 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.1.
Комментарии:
1. есть ли у вас какие-либо идеи, почему выгружаемая версия с ROW_NUMBER() на самом деле будет выполняться очень быстро, когда мои параметры поиска дают большие результаты? Когда мой запрос возвращает 6000 строк, выгружаемая версия выполняется так же быстро, как и не выгружаемая версия. Но когда поиск возвращает только 100 элементов, выгружаемая версия замедляется.
2. Не могу сказать вам наверняка. Вам понадобится ваша база данных для запуска запроса и просмотра плана выполнения. Что я могу вам сказать наверняка, так это то, что с «SELECT TOP X» выгружаемая версия будет работать быстрее, чем обычная.