如何优化中实体框架生成的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();

受约束的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.

执行计划屏幕截图

我希望优化生成的SQL查询并将优化查询插入到存储过程中。为我提供了在tblSale上创建非聚集索引(TblSale)的技巧(您可以在我所包含的图像中看到此技巧)。

当我使用命令创建它时：

CREATE NONCLUSTERED INDEX IX_ProductVendor_tblUserId 
ON tblSale (tblUserId); 

然后在中运行SQL查询：

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

因此，在使用索引优化SQL查询之后，需要花费更长的时间。

有人能帮助我使用索引优化SQL Server中的查询吗？

Answer 1

有人能帮助我使用索引优化SQL Server中的查询吗？

首先，在尝试优化数据库中的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();

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

Answer 2

我希望优化生成的SQL查询并将优化查询插入到存储过程中。

Bzzt不对。

您的查询已经“优化”了--因为您无法对查询本身做任何事情来提高其运行时性能。

Server 中的存储过程与立即执行的查询相比，没有任何神奇的优化或其他实际优势。存储过程确实受益于缓存的执行计划，但是在第一次执行之后立即查询也是如此，而且执行计划生成并不是一项昂贵的操作。

无论如何，使用存储过程进行只读SELECT操作是不明智的，最好使用UDF (CREATE FUNCTION)，这样您就可以利用函数组合，这可以比嵌套存储过程调用更好地优化和运行时。

如果Server的Show功能告诉您创建一个不属于EF职责范围的索引，那么它也不属于存储过程的责任范围。只需在数据库中定义索引并将其包含在安装脚本中即可。您的EF生成的查询将运行得更快，而不是存储过程。