javascript - OData $top 和 PageSize 对 Web API 性能没有影响

Question

我目前有一个 ASP.net WebApplication 的 Web API，它查询 SQL Server 2008 R2 中我们所有测试数据(大约 500k 行)的 View ，Web API 通过 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;

    }
}

过滤数据具有可接受的性能，这是我用来调试 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');
            }
        });
    }

这个查询:

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

在 21 秒内返回 78575 行

与 TSQL 相比:

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

同样的 78575 行需要 13 秒

我理解将数据转换为 json 的开销增加了几秒钟。我最大的问题是在 Odata 中选择前 N 行时:

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

该查询大约需要 16 秒来返回 100 行，我相信我节省的额外时间只是来自较小的负载。

使用 TSQL 做同样的事情:

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

1 秒返回 100 行(它也可以在 5 秒内返回 10000 行)

我的猜测是，在 SQL 数据库上完成筛选操作之前，OData 不会获取前 100 行。我也试过在过滤器之前移动“$top100”，结果相同。

我也试过将 Web API 的 PageSize 设置为 100，如下所示:

[EnableQuery(PageSize =100)]

但这对性能没有影响。

有谁知道我可能遗漏了什么或做错了什么可能会导致如此大的性能损失？或者这是 Odata 的缺点？

谢谢。

编辑 1:我捕获了从 Entity Framework 生成的 SQL，为了便于阅读，我还用 * 替换了列名。它还按我省略的所有列排序。它看起来确实没有得到适当的优化，因为它几乎选择了所有内容 3 次。

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, 

编辑 2:

看起来多重选择很好，它的排序会降低性能。现在我只需要阻止它订购所有东西。

编辑 3:

阅读这篇文章后，我通过禁用“EnsureStableOrdering”大大加快了速度 (Entity framework with OData(Web API) is sending Order By clause By default to Sql Query)

[EnableQuery(EnsureStableOrdering = false)]

现在返回 100 行只需要一秒或更短的时间。排序很慢，但这只是我的索引和 View 的问题，而不是 OData。

此外，生成的 SQL 现在看起来像这样:

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 1

答案在帖子中，但我也会在这里添加。

编辑 3:

阅读这篇文章后，我通过禁用“EnsureStableOrdering”大大加快了速度 (Entity framework with OData(Web API) is sending Order By clause By default to Sql Query)

[EnableQuery(EnsureStableOrdering = false)]

现在返回 100 行只需要一秒或更短的时间。排序很慢，但这只是我的索引和 View 的问题，而不是 OData。

此外，生成的 SQL 现在看起来是这样的:

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) )