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

Question

我目前有一个用于 ASP.net Web应用程序的Web API，该API查询SQL Server 2008 R2中所有测试数据(约500k行)的视图，该视图通过实体框架在Web API中引用

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和测试日期小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 和测试日期小 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 行。我还尝试在过滤器之前移动"$top 100"，最终得到相同的结果。

我还尝试将 Web API 的页面大小设置为 100，如下所示：

[EnableQuery(PageSize =100)]

但这对性能没有影响。

有谁知道我可能错过了什么或做错了什么，可能会导致如此大的性能下降？还是这是 Odata 的缺点？

谢谢。

编辑 1：我捕获了从实体框架生成的 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：

阅读这篇文章后，我通过禁用"确保稳定排序"来加快速度(默认情况下，带有OData(Web API)的实体框架正在向Sql Query发送Order By 子句)

[EnableQuery(EnsureStableOrdering = false)]

现在只需一秒钟或更短的时间即可返回 100 行。排序很慢，但这只是我的索引和视图的问题，而不是 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：

阅读这篇文章后，我通过禁用"确保稳定排序"来加快速度(默认情况下，带有OData(Web API)的实体框架正在向Sql Query发送Order By 子句)

[EnableQuery(EnsureStableOrdering = false)]

现在只需一秒钟或更短的时间即可返回 100 行。排序很慢，但这只是我的索引和视图的问题，而不是 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) )