c# - 如何在 SQL Server Management Studio 中优化 Entity Framework 生成的 SQL 查询？

Question

我在 linq 中创建了一个查询，它返回我店里最活跃的销售人员的表格:

ProjectDB3Context db = new ProjectDB3Context();

db.Database.Log = message => Trace.WriteLine(message);
var result = db.tblUsers.Join(db.tblSales,
                              u => u.ID,
                              sl => sl.tblUserId,
                              (u, sl) => new { u, sl })
               .Select(o => new
                            {
                                UserId = o.u.ID,
                                Login = o.u.UserLogin,
                                FullName = o.u.Name + " " +  o.u.Surname,
                                ItemsToSell = db.tblSales.Where(x => x.tblUserId == o.u.ID).Count()
                            })
               .Distinct()
               .OrderByDescending(x => x.ItemsToSell)
               .ToList();

henerated SQL 查询如下所示:

SELECT 
    [Distinct1].[C1] AS [C1], 
    [Distinct1].[ID] AS [ID], 
    [Distinct1].[UserLogin] AS [UserLogin], 
    [Distinct1].[C2] AS [C2], 
    [Distinct1].[C3] AS [C3]
    FROM ( SELECT DISTINCT 
        [Project1].[ID] AS [ID], 
        [Project1].[UserLogin] AS [UserLogin], 
        1 AS [C1], 
        [Project1].[Name] + N' ' + [Project1].[Surname] AS [C2], 
        [Project1].[C1] AS [C3]
        FROM ( SELECT 
            [Extent1].[ID] AS [ID], 
            [Extent1].[UserLogin] AS [UserLogin], 
            [Extent1].[Name] AS [Name], 
            [Extent1].[Surname] AS [Surname], 
            (SELECT 
                COUNT(1) AS [A1]
                FROM [dbo].[tblSale] AS [Extent3]
                WHERE [Extent3].[tblUserId] = [Extent1].[ID]) AS [C1]
            FROM  [dbo].[tblUser] AS [Extent1]
            INNER JOIN [dbo].[tblSale] AS [Extent2] ON [Extent1].[ID] = [Extent2].[tblUserId]
        )  AS [Project1]
    )  AS [Distinct1]
    ORDER BY [Distinct1].[C3] DESC

统计:

SQL Server Execution Times:
CPU time = 359 ms,  elapsed time = 529 ms.

Execution plan screen shot

我想优化生成的 SQL 查询并将优化的查询插入存储过程。 SQL Server Management Studio 为我提供了在 tblSale 上创建非聚集索引 (tblUserId) 的提示(您可以在我提供的图像中看到该提示)。

当我使用命令创建它时:

CREATE NONCLUSTERED INDEX IX_ProductVendor_tblUserId 
ON tblSale (tblUserId); 

然后在 SQL Server Management Studio 中运行 SQL 查询，我得到:

SQL Server Execution Times:
CPU time = 328 ms,  elapsed time = 631 ms.

所以在我使用索引优化我的 SQL 查询后需要更长的时间。

有人可以帮助我使用索引在 SQL Server 中优化这个查询吗？

Answer 1

Can anybody help me with optimize this query in SQL Server using indexes?

首先，在尝试优化数据库中的 SQL 查询之前，请确保您的 LINQ 查询是最佳的。你的情况不是这样。有不必要的连接，这又需要不同的等。tblSales 被访问了两次(请参阅生成的 SQL)。

您要实现的目标是让销售量按销售量降序排列的用户。以下简单查询应该会产生所需的结果

var result = db.tblUsers
   .Select(u => new
   {
       UserId = u.ID,
       Login = u.UserLogin,
       FullName = u.Name + " " +  u.Surname,
       ItemsToSell = db.tblSales.Count(s => s.tblUserId == u.ID)
    })
    .Where(x => x.ItemsToSel > 0)
    .OrderByDescending(x => x.ItemsToSell)
    .ToList();

尝试查看新的执行计划/时间。