SQL 内连接排序性能较慢

Question

我们在 Azure 中有一个 .NET 应用程序，其中一个页面显示时间条目的概述。我们使用 Entity Framework 来构建和执行 SQL 查询。当我们按时间表的列(例如日期或小时)排序时，它已经足够快了。但是，当我们对来自其他表的列(来自内部联接，如用户 diplayname)进行排序时，速度非常慢。

此外，当我们在 Azure 门户中查看 DTU 指标时(在此查询之后)，其峰值达到 100% DTU。我们有包含 500 个 DTU 的 P4 服务计划，所以我认为这一定不是问题。

这是由 Entity Framework 执行的慢速查询的示例:

exec sp_executesql N'SELECT 
[Project1].[Id] AS [Id], 
[Project1].[Date] AS [Date], 
[Project1].[Hours] AS [Hours], 
[Project1].[Notes] AS [Notes], 
[Project1].[StartEnd] AS [StartEnd], 
[Project1].[Status] AS [Status], 
[Project1].[Timer] AS [Timer], 
[Project1].[TimeRowId] AS [TimeRowId], 
[Project1].[InvoiceId] AS [InvoiceId], 
[Project1].[Pause] AS [Pause], 
[Project1].[ClientStatus] AS [ClientStatus], 
[Project1].[ExternalUrl] AS [ExternalUrl], 
[Project1].[ExternalName] AS [ExternalName], 
[Project1].[ApprovedBy] AS [ApprovedBy], 
[Project1].[ApprovedDate] AS [ApprovedDate], 
[Project1].[ClientApprovedBy] AS [ClientApprovedBy], 
[Project1].[ClientApprovedDate] AS [ClientApprovedDate]
FROM ( SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Date] AS [Date], 
    [Extent1].[Hours] AS [Hours], 
    [Extent1].[Notes] AS [Notes], 
    [Extent1].[StartEnd] AS [StartEnd], 
    [Extent1].[Status] AS [Status], 
    [Extent1].[Timer] AS [Timer], 
    [Extent1].[TimeRowId] AS [TimeRowId], 
    [Extent1].[InvoiceId] AS [InvoiceId], 
    [Extent1].[Pause] AS [Pause], 
    [Extent1].[ClientStatus] AS [ClientStatus], 
    [Extent1].[ExternalUrl] AS [ExternalUrl], 
    [Extent1].[ExternalName] AS [ExternalName], 
    [Extent1].[ApprovedBy] AS [ApprovedBy], 
    [Extent1].[ApprovedDate] AS [ApprovedDate], 
    [Extent1].[ClientApprovedBy] AS [ClientApprovedBy], 
    [Extent1].[ClientApprovedDate] AS [ClientApprovedDate], 
    [Extent6].[DisplayName] AS [DisplayName]
    FROM      [dbo].[Time] AS [Extent1]
    INNER JOIN [dbo].[TimeRow] AS [Extent2] ON [Extent1].[TimeRowId] = [Extent2].[Id]
    INNER JOIN [dbo].[ProjectUser] AS [Extent3] ON [Extent2].[ProjectUserId] = [Extent3].[Id]
    INNER JOIN [dbo].[Project] AS [Extent4] ON [Extent3].[ProjectId] = [Extent4].[Id]
    INNER JOIN [dbo].[Customer] AS [Extent5] ON [Extent4].[CustomerId] = [Extent5].[Id]
    INNER JOIN [dbo].[User] AS [Extent6] ON [Extent3].[UserId] = [Extent6].[Id]
    WHERE ([Extent5].[CompanyId] = @p__linq__0) AND (@p__linq__1 <= [Extent1].[Date]) AND (@p__linq__2 >= [Extent1].[Date])
)  AS [Project1]
ORDER BY [Project1].[DisplayName] DESC
OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY ',N'@p__linq__0 int,@p__linq__1 datetime2(7),@p__linq__2 datetime2(7)',@p__linq__0=19218,@p__linq__1='2017-01-01 00:00:00',@p__linq__2='2017-12-31 00:00:00'

我们尝试在(用户表的)displayname 上添加非聚集索引，但这并没有带来任何不同。另外，我们在 Azure 中对数据库进行了自动调整，因此您一定认为 Azure 会自行添加必要的索引，对吗？

我们怎样才能执行这个查询？

评论 2017 年 5 月 12 日我认为我们解决了问题。我们在 Database Tuning Adviser 中执行查询，得到了 9 个推荐索引。在我们应用此建议后，查询速度非常快!

Answer 1

我无法回答您关于如何加快查询速度的问题，正如 Sean Lange 所说，我们需要为此提供表结构和执行计划。

我对 Azure 的经验不是很丰富，但您应该会看到正在使用的任何索引。

本文档:https://learn.microsoft.com/en-us/azure/sql-database/sql-database-advisor

Indexes created using recommendations are always flagged as auto_created indexes. You can see which indexes are auto_created by looking at sys.indexes view.

该文档可以帮助您了解 Azure 是否正在为您创建任何索引，或者您是否需要接受任何建议。