Bad OData Performance with Entity Framework and Top and OrderBy

Question

I am using OData 5.8.0 and EntityFramework 6.1.3, the query:

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

results in:

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

Which takes ~20 seconds to run against the DB for a large quantity of field A.

If I use the identical LINQ:

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

It results in:

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

Which takes 120ms to run.

How do I force OData to use the same expression (i.e. not to use TOP in the outer statement)?

Answer 1

I found this issue is that OData isn't very smart, and applies the query options in the wrong order. The code below applies the orderBy first, then the top:

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>;
}

If I ran the resulting SQL statements that were produces by using this IQueryable:

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

Re-ordering these statements resulted in:

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

Compared to if I don't re-order:

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

A speed boost of ~20,000x.