OData $top and PageSize has no impact on web API performance

I currently have a web API for an ASP.net WebApplication that queries a view of all of our Test Data (About 500k rows) in SQL server 2008 R2 which is referenced in the Web API via Entity Framework

public class TestDataController : ApiController
{
    TeraDiodeEntities dc = new TeraDiodeEntities();
    // GET api/<controller>

    [EnableQuery]
    public IQueryable<KPI_AllData_View> Get()
    {
        return dc.KPI_AllData_View;

    }
}

Filtering Data has acceptable performance, here is the client side code I am using to debug the Web API:

    function getdata() {
        startTime = new Date();
        $.ajax({
            url: "../api/TestData?$filter=DeviceTypeID eq 2 and DevicePartNumberID eq 74 and TestDateSmall gt 2017-01-01T23:59:59.99Z",
            type: 'GET',
            dataType: 'json',
            success: function (data, textStatus, xhr) {
                endTime = new Date();
                var timeDiff = endTime - startTime;
                timeDiff /= 1000;
                var seconds = Math.round(timeDiff);
                console.log(seconds + " seconds");
                console.log(data);
            },
            error: function (xhr, textStatus, errorThrown) {
                console.log('Error in Operation');
            }
        });
    }

This Query:

“../api/TestData?$filter=DeviceTypeID eq 2 and DevicePartNumberID eq 74 and TestDateSmall gt 2017-01-01T23:59:59.99Z”

returns 78575 rows in 21 seconds

compared to TSQL:

SELECT  *
  FROM [Teradiode].[dbo].[KPI_AllData_View]
  where DeviceTypeID = 2 and DevicePartNumberID = 74 and TestDateSmall > '1/1/17'

which takes 13 seconds for the same 78575 rows

I understand the increased time from overhead converting the data to json adding a few seconds. My biggest issue is when selecting the top N rows in Odata:

“../api/TestData?$filter=DeviceTypeID eq 2 and DevicePartNumberID eq 74 and TestDateSmall gt 2017-01-01T23:59:59.99Z&$top=100”

that query takes about 16 seconds to return the 100 rows, I believe that the extra time I have saved is just from the smaller payload.

Doing the same using TSQL:

SELECT  top 100 *
  FROM [Teradiode].[dbo].[KPI_AllData_View]
  where DeviceTypeID = 2 and DevicePartNumberID = 74 and TestDateSmall > '1/1/17'

returns the 100 rows in 1 second (it can also do 10000 rows in 5 seconds)

My guess is that OData isn’t taking the top 100 rows until after the Filter operation completes on the SQL database. I have also tried moving “$top100” before the filter and end up with the same results.

I have also tried setting the PageSize of the web API to 100 like so:

[EnableQuery(PageSize =100)]

But that has no affect on performance.

Anyone know what I might be missing or doing wrong that might be causing such a big performance hit? Or is this a disadvantage of Odata?

Thanks.

Edit 1: I captured the SQL generated from the Entity Framework, I also replaced the column names with * for readability purposes. It also orders by all of the columns which I omitted. It definitely looks like it isn’t properly optimized because it selects almost everything 3 times.

DECLARE @p__linq__0 BIGINT = 74 
DECLARE @p__linq__1 INT = 100 

SELECT TOP (@p__linq__1) * 
FROM   (SELECT * 
    FROM   (SELECT * 
            FROM   [dbo].[kpi_alldata_view] AS [KPI_AllData_View]) AS 
           [Extent1] 
    WHERE  ( [Extent1].[devicepartnumberid] = @p__linq__0 ) 
           AND ( [Extent1].[testdatesmall] > 
                 CONVERT(DATETIME2, '2017-01-01 18:59:59.9900000', 
                     121) )) AS [Project1]
ORDER BY [Project1].[testdatesmall] DESC, 
     [Project1].[devicepartnumber] ASC, 
     [Project1].[devicepartnumberid] ASC, 
     [Project1].[devicepartnumberprefix] ASC, 
     [Project1].[devicetypeid] ASC, 
     [Project1].[displayorder] ASC, 
     [Project1].[exclude] ASC, 
     [Project1].[fiitemno] ASC, 
     [Project1].[hold] ASC, 
     [Project1].[job] ASC, 
     [Project1].[lotid] ASC, 
     [Project1].[lotquantity] ASC, 
     [Project1].[maxvalue] ASC, 
     [Project1].[measurementname] ASC, 
     [Project1].[minvalue] ASC, 
     [Project1].[operatorid] ASC, 
     [Project1].[operatorinitials] ASC, 
     [Project1].[operatorname] ASC, 
     [Project1].[productionmode] ASC, 
     [Project1].[productionmodeid] ASC, 
     [Project1].[reason] ASC, 
     [Project1].[recievernumber] ASC, 
     [Project1].[rev] ASC, 
     [Project1].[reviewer] ASC, 
     [Project1].[serialnumber] ASC, 
     [Project1].[stationdescription] ASC, 
     [Project1].[stationid] ASC, 
     [Project1].[stationname] ASC, 
     [Project1].[testdataid] ASC, 
     [Project1].[testdate] ASC, 

Edit 2:

It seems the multiple selects are fine, its the ordering that kills performance. Now I just need to stop it from ordering everything.

Edit 3:

I sped things up a lot by disabling “EnsureStableOrdering” after reading this post (Entity framework with OData(Web API) is sending Order By clause By default to Sql Query)

[EnableQuery(EnsureStableOrdering = false)]

Now it only takes a second or less to return 100 rows. Sorting is slow but that is just an issue with the my indexes and view not OData.

Also the Generated SQL now looks like this:

DECLARE @p__linq__0 BIGINT = 74 
DECLARE @p__linq__1 INT = 100 

SELECT TOP (@p__linq__1) * 
FROM   (SELECT * 
    FROM   [dbo].[kpi_alldata_view] AS [KPI_AllData_View]) AS [Extent1] 
WHERE  ( [Extent1].[devicepartnumberid] = @p__linq__0 ) 
   AND ( [Extent1].[testdatesmall] > 
         CONVERT(DATETIME2, '2017-01-01 18:59:59.9900000', 
             121) ) 

Answer

The answer is in the post but I’ll add it here too.

Edit 3:

I sped things up a lot by disabling “EnsureStableOrdering” after reading this post (Entity framework with OData(Web API) is sending Order By clause By default to Sql Query)

[EnableQuery(EnsureStableOrdering = false)]

Now it only takes a second or less to return 100 rows. Sorting is slow but that is just an issue with my indexes and view not OData.

Also the Generated SQL now looks like this:

DECLARE @p__linq__0 BIGINT = 74 
DECLARE @p__linq__1 INT = 100 

SELECT TOP (@p__linq__1) * 
FROM   (SELECT * 
    FROM   [dbo].[kpi_alldata_view] AS [KPI_AllData_View]) AS [Extent1] 
WHERE  ( [Extent1].[devicepartnumberid] = @p__linq__0 ) 
   AND ( [Extent1].[testdatesmall] > 
         CONVERT(DATETIME2, '2017-01-01 18:59:59.9900000', 
             121) )