c# - Entity Framework 的表连接性能问题

Question

加入两个表会导致选择时间从 330 秒增加到 40 秒。将要连接的表仅包含 ID 和文本。没想到两张表join的时候select时间增加了8倍。我的 JOIN 有什么问题吗？或者这是正常的 SQL Server 行为吗？

主表填充了 3500 万条记录，以查看在达到 10 GB 的 SQL Server Express 限制时它的性能。在字段 LogTimeStamp 和字段 LogType 上创建了一个附加索引。

连接表的内容:

var queryList = messages
    .Join(types,
    type => type.LogType,
    typeText => typeText.LogType, (msg, msgType) => new
    {
        msg.LogID,
        msg.LogTimeStamp,
        msg.LogUser,
        msg.LogType,
        msgType.LogTypeName,
        msg.LogMessage
    })
    .Where(t => System.Data.Entity.DbFunctions.TruncateTime(t.LogTimeStamp) >= fromDate)
    .Where(t => System.Data.Entity.DbFunctions.TruncateTime(t.LogTimeStamp) <= toDate)
    .Where(t => t.LogType != 4)
    .OrderBy(m => m.LogID)
    .ToList();

生成 SQL

SELECT 
  1 AS [C1], 
  [Extent1].[LogID] AS [LogID], 
  [Extent1].[LogTimeStamp] AS [LogTimeStamp], 
  [Extent1].[LogUser] AS [LogUser], 
  [Extent1].[LogType] AS [LogType], 
  [Extent2].[LogTypeName] AS [LogTypeName], 
  [Extent1].[LogMessage] AS [LogMessage]
  FROM  [dbo].[AuditTrailMessages] AS [Extent1]
  INNER JOIN [dbo].[AuditTrailLogTypes] AS [Extent2] ON [Extent1].[LogType] = [Extent2].[LogType]
WHERE ((convert (datetime2, convert(varchar(255), [Extent1].[LogTimeStamp], 102) ,  102)) >= @p__linq__0) 
  AND ((convert (datetime2, convert(varchar(255), [Extent1].[LogTimeStamp], 102) ,  102)) <= @p__linq__1) 
  AND ( NOT ((4 =  CAST( [Extent1].[LogType] AS int)) AND ( CAST( [Extent1].[LogType] AS int) IS NOT NULL)))  

相比于

var queryList = messages
    .Where(t => System.Data.Entity.DbFunctions.TruncateTime(t.LogTimeStamp) >= fromDate)
    .Where(t => System.Data.Entity.DbFunctions.TruncateTime(t.LogTimeStamp) <= toDate)
    .Where(t => t.LogType != 4)
    .OrderBy(m => m.LogID)
    .ToList();

生成 SQL

SELECT 
  [Extent1].[LogID] AS [LogID], 
  [Extent1].[LogTimeStamp] AS [LogTimeStamp], 
  [Extent1].[LogUser] AS [LogUser], 
  [Extent1].[LogMessage] AS [LogMessage], 
  [Extent1].[LogType] AS [LogType]
  FROM [dbo].[AuditTrailMessages] AS [Extent1]
  WHERE ((convert (datetime2, convert(varchar(255), [Extent1].[LogTimeStamp], 102) ,  102)) >= @p__linq__0) 
  AND ((convert (datetime2, convert(varchar(255), [Extent1].[LogTimeStamp], 102) ,  102)) <= @p__linq__1) 
  AND ( NOT ((4 =  CAST( [Extent1].[LogType] AS int)) AND ( CAST( [Extent1].[LogType] AS int) IS NOT NULL)))

Answer 1

你看到所有这些了吗:

((convert (datetime2, convert(varchar(255), [Extent1].[LogTimeStamp], 102)

他们很糟糕。特别糟糕。他们基本上说“不要使用索引，进行全表扫描”。

他们跑到你身边做:

t => System.Data.Entity.DbFunctions.TruncateTime(t.LogTimeStamp) >= fromDate

这不是很好。不需要。日期中的任何时间戳大于或等于每个定义的日期并小于下一个日期。

所以:

t => System.Data.Entity.DbFunctions.TruncateTime(t.LogTimeStamp) >= fromDate

变成

t => t.LogTimeStamp >= fromDate

和

t => System.Data.Entity.DbFunctions.TruncateTime(t.LogTimeStamp) <= toDate

变成

t => t.LogTimeStamp < toDate.AddDays(1)

.Where(t => t.LogType != 4)

看起来像类型不匹配 - 让我猜猜，它不是数据库中的 int。然后使用 Equals 方法。这是 EF 中的一个已知错误。但没关系 - 此时您应该减少到相当多的条目，您的问题可能是日期时间比较的 super 低效代码。

切勿在比较的字段侧执行函数。绝不。他们会杀死任何索引使用(除非有一个索引恰好具有此功能)。始终重写查询以使所有函数都在常量端。

不是 EF 问题 - 一个常见的 SQL 初学者错误。