使用“.AsSplitQuery()”会使结果加倍

我正在做一些测试来提高我正在开发的一个工具的性能,该工具可以生成Excel报告。数据正在net6.0项目中使用ef6加载。使用导航属性在数据上下文中配置了许多依赖项。正如您将在下面看到的,查询时间非常长,但这不是我想在这里询问的。在开始与数据库管理员讨论数据库中的优化之前,我希望我的代码尽可能具有性能。

未指定时记录 QuerySplittingBehavior :

2022-03-31 09:21:37.9156|INFO|Products.IdmpExporter.Tests.Integration.ManagerTests.ExcelExportManagerTests|Starting test GetByAuthorizationHolderNrAsync_OK_Test with authorizationHolderNr=301; language=German|
2022-03-31 09:21:39.3873|INFO|Products.IdmpExporter.Business.Managers.ExcelExportManager|Start generating excel export by 168 product numbers |
2022-03-31 09:21:39.5490|WARN|Microsoft.EntityFrameworkCore.Query|Compiling a query which loads related collections for more than one collection navigation, either via 'Include' or through projection, but no 'QuerySplittingBehavior' has been configured. By default, Entity Framework will use 'QuerySplittingBehavior.SingleQuery', which can potentially result in slow query performance. See https://go.microsoft.com/fwlink/?linkid=2134277 for more information. To identify the query that's triggering this warning call 'ConfigureWarnings(w => w.Throw(RelationalEventId.MultipleCollectionIncludeWarning))'.|
2022-03-31 09:30:45.8627|DEBUG|Products.IdmpExporter.Business.Managers.ExcelExportManager|Retrieved 168 prodcuts in 546.4741356s|
2022-03-31 09:30:46.0707|WARN|Microsoft.EntityFrameworkCore.Query|Compiling a query which loads related collections for more than one collection navigation, either via 'Include' or through projection, but no 'QuerySplittingBehavior' has been configured. By default, Entity Framework will use 'QuerySplittingBehavior.SingleQuery', which can potentially result in slow query performance. See https://go.microsoft.com/fwlink/?linkid=2134277 for more information. To identify the query that's triggering this warning call 'ConfigureWarnings(w => w.Throw(RelationalEventId.MultipleCollectionIncludeWarning))'.|
2022-03-31 09:31:01.2852|DEBUG|Products.IdmpExporter.Business.Managers.ExcelExportManager|Aggregated additional prodcut information in 15.4218421s|
2022-03-31 09:31:01.8898|DEBUG|Products.IdmpExporter.Business.Managers.ExcelExportManager|Generated excel report in 0,6045199s|
2022-03-31 09:31:01.8898|INFO|Products.IdmpExporter.Business.Managers.ExcelExportManager|Excel report has been generated|

它(正确地)在546秒内返回168个产品。

指定时记录 .AsSplitQuery() (如上述警告所示):

2022-03-31 09:46:00.3521|INFO|Products.IdmpExporter.Tests.Integration.ManagerTests.ExcelExportManagerTests|Starting test GetByAuthorizationHolderNrAsync_OK_Test with authorizationHolderNr=301; language=German|
2022-03-31 09:46:01.6031|INFO|Products.IdmpExporter.Business.Managers.ExcelExportManager|Start generating excel export by 168 product numbers |
2022-03-31 09:48:37.3670|DEBUG|Products.IdmpExporter.Business.Managers.ExcelExportManager|Retrieved 333 prodcuts in 155.7634441s|
2022-03-31 09:48:56.2646|DEBUG|Products.IdmpExporter.Business.Managers.ExcelExportManager|Aggregated additional prodcut information in 18.8974323s|
2022-03-31 09:48:56.9089|DEBUG|Products.IdmpExporter.Business.Managers.ExcelExportManager|Generated excel report in 0,644222s|
2022-03-31 09:48:56.9089|INFO|Products.IdmpExporter.Business.Managers.ExcelExportManager|Excel report has been generated|

它在155秒内退货333件产品。几乎所有(为什么几乎?)产品都包含两次;两者具有相同的PK值(类型为int)

导致第二个结果的代码更改是添加 .AsSplitQuery() 在该存储库方法的return语句中:

public async Task<ICollection<TModel>> GetAllAsync(Expression<Func<TModel, bool>> predicate, IEnumerable<string> includeProperties = null)
{
    var query = Set.Include(includeProperties);

    if (predicate != null)
        query = query.Where(predicate);

    return await query.AsSplitQuery().ToListAsync();
}

使用时结果不应该完全相同吗 AsSplitQuery() ?各自为什么会有所不同?

[edit]试图通过在产品的PK列上指定顺序来解决这个问题,但结果仍然是双倍的。

    public override async Task<ICollection<Product>> GetAllAsync(Expression<Func<Product, bool>> predicate, IEnumerable<string> includeProperties = null)
    {
        var query = Set.Include(includeProperties);

        if (predicate != null)
            query = query.Where(predicate);

        return await query.AsSplitQuery()
                          .OrderBy(product => product.ProductNr)
                          .ToListAsync();
    }

[edit2]如果它是相关的,下面是如何调用存储库方法:

每个导航属性(用点分隔)将导致 JOIN 当执行单个查询时:

    private readonly string[] _productPropertiesToInclude =
    {
        nameof(Product.Descriptions),
        $"{nameof(Product.ProductGroupCode)}.{nameof(Code.Descriptions)}",
        $"{nameof(Product.Articles)}.{nameof(Article.Descriptions)}",
        $"{nameof(Product.Articles)}.{nameof(Article.QuantityUnit)}.{nameof(Code.Descriptions)}",
        $"{nameof(Product.Articles)}.{nameof(Article.Chainings)}.{nameof(ArticleChaining.ChainingCode)}.{nameof(Code.Descriptions)}",
        $"{nameof(Product.Articles)}.{nameof(Article.Chainings)}.{nameof(ArticleChaining.ChainedArticle)}.{nameof(Article.Product)}",
        $"{nameof(Product.Components)}.{nameof(Component.GenericTree)}.{nameof(GenericTree.Descriptions)}",
        $"{nameof(Product.Components)}.{nameof(Component.Roas)}.{nameof(ComponentRoa.RoaCode)}.{nameof(Code.Descriptions)}"
    };

谓词将导致子句 WHERE ProductNr IN (...)

var products = await _productSupersetProductRepository.GetAllAsync(product => productNumbers.Contains(product.ProductNr), _productPropertiesToInclude);

[edit3]正如评论中所要求的,我添加了EF执行的SQL。由于对EF在使用拆分查询时应该如何查询没有足够深入的了解,我不确定在哪里可以找到问题。这个 WHERE 子句适用于所有拆分查询;对象实例化似乎是原因。

SingleQuery:

SELECT [v].[ProductNr], [v].[AtcCode], [v].[AuthorizationHolder], [v].[AuthorizationHolderNr], [v].[FiNr], [v].[Orphan], [v].[ItCode], [v].[PiNr], [v].[ProductGroupCodeType], [v].[ProductGroupCode], [v].[SwissmedicCode], [v].[SwissmedicNr], [v].[SwissmedicStrengthNr], 
[v0].[CodeType], [v0].[Code], [p].[ProductNr], [p].[Lang], [p].[Product_Descr], [p].[ProductLong_Descr], [p].[ProductShort_Descr], [p].[Swissmedic_Descr], [v1].[CodeType], [v1].[Code], [v1].[Lang], [v1].[Descr], [t0].[Pharmacode], [t0].[CategoryType], [t0].[CountingUnit], [t0].[NumberOfPieces], [t0].[PackDepth], [t0].[PackHeight], [t0].[PackWeight], [t0].[PackWidth], [t0].[ProductNr], [t0].[QuantityUnitCodeType], [t0].[QuantityUnitCodeValue], [t0].[MaxStorageTemp], [t0].[MinStorageTemp], [t0].[SwissMedicNr], [t0].[CodeType], [t0].[Code], [t0].[Pharmacode0], [t0].[Lang], [t0].[Pharmacode_Descr], [t0].[CodeType0], [t0].[Code0], [t0].[Lang0], [t0].[Descr], [t0].[Pharmacode1], [t0].[ChainedPharmacode], [t0].[ChainingCodeType], [t0].[ChainingCodeValue], [t0].[CodeType1], [t0].[Code1], [t0].[Pharmacode00], [t0].[ProductNr0], [t0].[CodeType00], [t0].[Code00], [t0].[Lang1], [t0].[Descr0], [t0].[CategoryType0], [t0].[CountingUnit0], [t0].[NumberOfPieces0], [t0].[PackDepth0], [t0].[PackHeight0], [t0].[PackWeight0], [t0].[PackWidth0], [t0].[ProductNr00], [t0].[QuantityUnitCodeType0], [t0].[QuantityUnitCodeValue0], [t0].[MaxStorageTemp0], [t0].[MinStorageTemp0], [t0].[SwissMedicNr0], [t0].[AtcCode], [t0].[AuthorizationHolder], [t0].[AuthorizationHolderNr], [t0].[FiNr], [t0].[Orphan], [t0].[ItCode], [t0].[PiNr], [t0].[ProductGroupCodeType], [t0].[ProductGroupCode], [t0].[SwissmedicCode], [t0].[SwissmedicNr00], [t0].[SwissmedicStrengthNr], [t1].[ProductNr], [t1].[ComponentNr], [t1].[GenericTreeNr], [t1].[PharmaDoseFormCode], [t1].[GenericTreeNr0], [t1].[Code], [t1].[GenericTreeNr1], [t1].[Lang], [t1].[Descr], [t1].[ProductNr0], [t1].[ComponentNr0], [t1].[RoaCodeValue], [t1].[RoaCodeType], [t1].[CodeType], [t1].[Code0], [t1].[CodeType0], [t1].[Code00], [t1].[Lang0], [t1].[Descr0]
FROM [v_IdmpExport_Product] AS [v]
LEFT JOIN [v_Code] AS [v0] ON ([v].[ProductGroupCodeType] = [v0].[CodeType]) AND ([v].[ProductGroupCode] = [v0].[Code])
LEFT JOIN [Products_Descriptions] AS [p] ON [v].[ProductNr] = [p].[ProductNr]
LEFT JOIN [v_IdmpExport_CodeDescriptions] AS [v1] ON ([v0].[CodeType] = [v1].[CodeType]) AND ([v0].[Code] = [v1].[Code])
LEFT JOIN (
    SELECT [v2].[Pharmacode], [v2].[CategoryType], [v2].[CountingUnit], [v2].[NumberOfPieces], [v2].[PackDepth], [v2].[PackHeight], [v2].[PackWeight], [v2].[PackWidth], [v2].[ProductNr], [v2].[QuantityUnitCodeType], [v2].[QuantityUnitCodeValue], [v2].[MaxStorageTemp], [v2].[MinStorageTemp], [v2].[SwissMedicNr], [v3].[CodeType], [v3].[Code], [p0].[Pharmacode] AS [Pharmacode0], [p0].[Lang], [p0].[Pharmacode_Descr], [v4].[CodeType] AS [CodeType0], [v4].[Code] AS [Code0], [v4].[Lang] AS [Lang0], [v4].[Descr], [t].[Pharmacode] AS [Pharmacode1], [t].[ChainedPharmacode], [t].[ChainingCodeType], [t].[ChainingCodeValue], [t].[CodeType] AS [CodeType1], [t].[Code] AS [Code1], [t].[Pharmacode0] AS [Pharmacode00], [t].[ProductNr] AS [ProductNr0], [t].[CodeType0] AS [CodeType00], [t].[Code0] AS [Code00], [t].[Lang] AS [Lang1], [t].[Descr] AS [Descr0], [t].[CategoryType] AS [CategoryType0], [t].[CountingUnit] AS [CountingUnit0], [t].[NumberOfPieces] AS [NumberOfPieces0], [t].[PackDepth] AS [PackDepth0], [t].[PackHeight] AS [PackHeight0], [t].[PackWeight] AS [PackWeight0], [t].[PackWidth] AS [PackWidth0], [t].[ProductNr0] AS [ProductNr00], [t].[QuantityUnitCodeType] AS [QuantityUnitCodeType0], [t].[QuantityUnitCodeValue] AS [QuantityUnitCodeValue0], [t].[MaxStorageTemp] AS [MaxStorageTemp0], [t].[MinStorageTemp] AS [MinStorageTemp0], [t].[SwissMedicNr] AS [SwissMedicNr0], [t].[AtcCode], [t].[AuthorizationHolder], [t].[AuthorizationHolderNr], [t].[FiNr], [t].[Orphan], [t].[ItCode], [t].[PiNr], [t].[ProductGroupCodeType], [t].[ProductGroupCode], [t].[SwissmedicCode], [t].[SwissmedicNr0] AS [SwissmedicNr00], [t].[SwissmedicStrengthNr]
    FROM [v_IdmpExport_Article] AS [v2]
    LEFT JOIN [v_Code] AS [v3] ON ([v2].[QuantityUnitCodeType] = [v3].[CodeType]) AND ([v2].[QuantityUnitCodeValue] = [v3].[Code])
    LEFT JOIN [Pharmacodes_Descriptions] AS [p0] ON [v2].[Pharmacode] = [p0].[Pharmacode]
    LEFT JOIN [v_IdmpExport_CodeDescriptions] AS [v4] ON ([v3].[CodeType] = [v4].[CodeType]) AND ([v3].[Code] = [v4].[Code])
    LEFT JOIN (
        SELECT [v5].[Pharmacode], [v5].[ChainedPharmacode], [v5].[ChainingCodeType], [v5].[ChainingCodeValue], [v6].[CodeType], [v6].[Code], [v7].[Pharmacode] AS [Pharmacode0], [v8].[ProductNr], [v9].[CodeType] AS [CodeType0], [v9].[Code] AS [Code0], [v9].[Lang], [v9].[Descr], [v7].[CategoryType], [v7].[CountingUnit], [v7].[NumberOfPieces], [v7].[PackDepth], [v7].[PackHeight], [v7].[PackWeight], [v7].[PackWidth], [v7].[ProductNr] AS [ProductNr0], [v7].[QuantityUnitCodeType], [v7].[QuantityUnitCodeValue], [v7].[MaxStorageTemp], [v7].[MinStorageTemp], [v7].[SwissMedicNr], [v8].[AtcCode], [v8].[AuthorizationHolder], [v8].[AuthorizationHolderNr], [v8].[FiNr], [v8].[Orphan], [v8].[ItCode], [v8].[PiNr], [v8].[ProductGroupCodeType], [v8].[ProductGroupCode], [v8].[SwissmedicCode], [v8].[SwissmedicNr] AS [SwissmedicNr0], [v8].[SwissmedicStrengthNr]
        FROM [v_IdmpExport_ArticleChaining] AS [v5]
        LEFT JOIN [v_Code] AS [v6] ON ([v5].[ChainingCodeType] = [v6].[CodeType]) AND ([v5].[ChainingCodeValue] = [v6].[Code])
        INNER JOIN [v_IdmpExport_Article] AS [v7] ON [v5].[ChainedPharmacode] = [v7].[Pharmacode]
        INNER JOIN [v_IdmpExport_Product] AS [v8] ON [v7].[ProductNr] = [v8].[ProductNr]
        LEFT JOIN [v_IdmpExport_CodeDescriptions] AS [v9] ON ([v6].[CodeType] = [v9].[CodeType]) AND ([v6].[Code] = [v9].[Code])
    ) AS [t] ON [v2].[Pharmacode] = [t].[Pharmacode]
) AS [t0] ON [v].[ProductNr] = [t0].[ProductNr]
LEFT JOIN (
    SELECT [v10].[ProductNr], [v10].[ComponentNr], [v10].[GenericTreeNr], [v10].[PharmaDoseFormCode], [g].[GenericTreeNr] AS [GenericTreeNr0], [g].[Code], [g0].[GenericTreeNr] AS [GenericTreeNr1], [g0].[Lang], [g0].[Descr], [t2].[ProductNr] AS [ProductNr0], [t2].[ComponentNr] AS [ComponentNr0], [t2].[RoaCodeValue], [t2].[RoaCodeType], [t2].[CodeType], [t2].[Code] AS [Code0], [t2].[CodeType0], [t2].[Code0] AS [Code00], [t2].[Lang] AS [Lang0], [t2].[Descr] AS [Descr0]
    FROM [v_IdmpExport_Component] AS [v10]
    LEFT JOIN [GenericTree] AS [g] ON [v10].[GenericTreeNr] = [g].[GenericTreeNr]
    LEFT JOIN [GenericTree_Description] AS [g0] ON [g].[GenericTreeNr] = [g0].[GenericTreeNr]
    LEFT JOIN (
        SELECT [v11].[ProductNr], [v11].[ComponentNr], [v11].[RoaCodeValue], [v11].[RoaCodeType], [v12].[CodeType], [v12].[Code], [v13].[CodeType] AS [CodeType0], [v13].[Code] AS [Code0], [v13].[Lang], [v13].[Descr]
        FROM [v_IdmpExport_ComponentRoa] AS [v11]
        LEFT JOIN [v_Code] AS [v12] ON ([v11].[RoaCodeType] = [v12].[CodeType]) AND ([v11].[RoaCodeValue] = [v12].[Code])
        LEFT JOIN [v_IdmpExport_CodeDescriptions] AS [v13] ON ([v12].[CodeType] = [v13].[CodeType]) AND ([v12].[Code] = [v13].[Code])
    ) AS [t2] ON ([v10].[ProductNr] = [t2].[ProductNr]) AND ([v10].[ComponentNr] = [t2].[ComponentNr])
) AS [t1] ON [v].[ProductNr] = [t1].[ProductNr]
WHERE [v].[ProductNr] IN (61542, 61543, 61544, 71154, 1034754, 1039805, 1039806, 1207145, 1207146, 1207147, 1214525, 1214526, 1237724, 1255179, 1267950, 1267951, 1384681, 1384682, 1423891, 1453588, 1484851, 1484852)
ORDER BY [v].[ProductNr], [v0].[CodeType], [v0].[Code], [p].[ProductNr], [p].[Lang], [v1].[CodeType], [v1].[Code], [v1].[Lang], [t0].[Pharmacode], [t0].[CodeType], [t0].[Code], [t0].[Pharmacode0], [t0].[Lang], [t0].[CodeType0], [t0].[Code0], [t0].[Lang0], [t0].[Pharmacode1], [t0].[ChainedPharmacode], [t0].[CodeType1], [t0].[Code1], [t0].[Pharmacode00], [t0].[ProductNr0], [t0].[CodeType00], [t0].[Code00], [t0].[Lang1], [t1].[ProductNr], [t1].[ComponentNr], [t1].[GenericTreeNr0], [t1].[GenericTreeNr1], [t1].[Lang], [t1].[ProductNr0], [t1].[ComponentNr0], [t1].[RoaCodeValue], [t1].[CodeType], [t1].[Code0], [t1].[CodeType0], [t1].[Code00]

拆分查询(前3个查询):

SELECT [v].[ProductNr], [v].[AtcCode], [v].[AuthorizationHolder], [v].[AuthorizationHolderNr], [v].[FiNr], [v].[Orphan], [v].[ItCode], [v].[PiNr], [v].[ProductGroupCodeType], [v].[ProductGroupCode], [v].[SwissmedicCode], [v].[SwissmedicNr], [v].[SwissmedicStrengthNr], [v0].[CodeType], [v0].[Code]
FROM [v_IdmpExport_Product] AS [v]
LEFT JOIN [v_Code] AS [v0] ON ([v].[ProductGroupCodeType] = [v0].[CodeType]) AND ([v].[ProductGroupCode] = [v0].[Code])
WHERE [v].[ProductNr] IN (61542, 61543, 61544, 71154, 1034754, 1039805, 1039806, 1207145, 1207146, 1207147, 1214525, 1214526, 1237724, 1255179, 1267950, 1267951, 1384681, 1384682, 1423891, 1453588, 1484851, 1484852)
ORDER BY [v].[ProductNr], [v0].[CodeType], [v0].[Code]

SELECT [p].[ProductNr], [p].[Lang], [p].[Product_Descr], [p].[ProductLong_Descr], [p].[ProductShort_Descr], [p].[Swissmedic_Descr], [v].[ProductNr], [v0].[CodeType], [v0].[Code]
FROM [v_IdmpExport_Product] AS [v]
LEFT JOIN [v_Code] AS [v0] ON ([v].[ProductGroupCodeType] = [v0].[CodeType]) AND ([v].[ProductGroupCode] = [v0].[Code])
INNER JOIN [Products_Descriptions] AS [p] ON [v].[ProductNr] = [p].[ProductNr]
WHERE [v].[ProductNr] IN (61542, 61543, 61544, 71154, 1034754, 1039805, 1039806, 1207145, 1207146, 1207147, 1214525, 1214526, 1237724, 1255179, 1267950, 1267951, 1384681, 1384682, 1423891, 1453588, 1484851, 1484852)
ORDER BY [v].[ProductNr], [v0].[CodeType], [v0].[Code]

SELECT [v1].[CodeType], [v1].[Code], [v1].[Lang], [v1].[Descr], [v].[ProductNr], [v0].[CodeType], [v0].[Code]
FROM [v_IdmpExport_Product] AS [v]
LEFT JOIN [v_Code] AS [v0] ON ([v].[ProductGroupCodeType] = [v0].[CodeType]) AND ([v].[ProductGroupCode] = [v0].[Code])
INNER JOIN [v_IdmpExport_CodeDescriptions] AS [v1] ON ([v0].[CodeType] = [v1].[CodeType]) AND ([v0].[Code] = [v1].[Code])
WHERE [v].[ProductNr] IN (61542, 61543, 61544, 71154, 1034754, 1039805, 1039806, 1207145, 1207146, 1207147, 1214525, 1214526, 1237724, 1255179, 1267950, 1267951, 1384681, 1384682, 1423891, 1453588, 1484851, 1484852)
ORDER BY [v].[ProductNr], [v0].[CodeType], [v0].[Code]