c# - Entity Framework 以及 Top 和 OrderBy 的糟糕 OData 性能

Question

我正在使用 OData 5.8.0 和 EntityFramework 6.1.3，查询:

&$filter=fieldA eq 'ABCDEFG'&$skip=0&$top=10&$orderby=fieldB desc

结果:

SELECT TOP (10) 
    [Project1].[FieldA] AS [FieldA], 
    [Project1].[FieldB] AS [FieldB], 
    FROM ( SELECT [Project1].[FieldA] AS [FieldA], [Project1].[FieldB] AS [FieldB], row_number() OVER (ORDER BY [Project1].[FieldB] DESC, [Project1].[FieldA] ASC) AS [row_number]
        FROM ( SELECT 
            [Extent1].[FieldA] AS [FieldA], 
            [Extent1].[FieldB] AS [FieldB], 
            FROM [dbo].[table] AS [Extent1]
            WHERE ([Extent1].[FieldA] = 'ABCDEFG') OR (([Extent1].[FieldA] IS NULL) AND ('ABCDEFG' IS NULL))
        )  AS [Project1]
    )  AS [Project1]
    WHERE [Project1].[row_number] > 0
    ORDER BY [Project1].[FieldB] DESC, [Project1].[FieldA] ASC

对于大量字段 A，针对数据库运行大约需要 20 秒。

如果我使用相同的 LINQ:

var newList = table.Where(f => f.fieldA == 'ABCDEFG').OrderByDescending(f => f.fieldB).Take(10).Skip(0).ToList();

结果是:

SELECT 
    [Limit1].[FieldA] AS [FieldA], 
    [Limit1].[FieldB] AS [FieldB]
    FROM ( SELECT [Limit1].[FieldA] AS [FieldA], [Limit1].[FieldB] AS [FieldB], row_number() OVER (ORDER BY [Limit1].[FieldB] DESC) AS [row_number]
        FROM ( SELECT TOP (10) [Project1].[FieldA] AS [FieldA], [Project1].[FieldB] AS [FieldB]
            FROM ( SELECT 
                [Extent1].[FieldA] AS [FieldA], 
                [Extent1].[FieldB] AS [FieldB]
                FROM [dbo].[table] AS [Extent1]
                WHERE ([Extent1].[FieldA] = 'ABCDEFG') OR (([Extent1].[FieldA] IS NULL) AND ('ABCDEFG' IS NULL))
            )  AS [Project1]
            ORDER BY [Project1].[FieldB] DESC
        )  AS [Limit1]
    )  AS [Limit1]
    WHERE [Limit1].[row_number] > 0
    ORDER BY [Limit1].[FieldB] DES

运行需要 120 毫秒。

如何强制 OData 使用相同的表达式(即不在外部语句中使用 TOP)？

Answer 1

我发现这个问题是 OData 不是很智能，并且以错误的顺序应用查询选项。下面的代码首先应用 orderBy，然后是顶部:

private static IQueryable<Item> ApplyOptimizedOdataOptions(IQueryable<Item> origQuery,  ODataQueryOptions<Item> options)
{
    var defaultOdataQuerySettings = new ODataQuerySettings();
    if (options.Top != null && options.OrderBy != null)
    {
        // We can optimze this query. Apply the OrderBy first, then Top.
        IQueryable results = options.OrderBy.ApplyTo(origQuery, defaultOdataQuerySettings);
        results = options.Top.ApplyTo(results, defaultOdataQuerySettings);
        results = options.ApplyTo(results, defaultOdataQuerySettings, AllowedQueryOptions.Top | AllowedQueryOptions.OrderBy);

        return results as IQueryable<Item>;
    }

    return options.ApplyTo(origQuery, defaultOdataQuerySettings) as IQueryable<Item>;
}

如果我运行使用此 IQueryable 生成的结果 SQL 语句:

SET STATISTICS TIME ON;
// Run SQL here
SET STATISTICS TIME OFF;

重新排序这些语句导致:

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms.

与我不重新订购相比:

SQL Server Execution Times: CPU time = 1213 ms, elapsed time = 20112ms.

速度提升约 20,000 倍。