表加入实体框架的性能问题 [英] Table Join performance issue with Entity Framework

问题描述

连接两个表会导致从330秒增加的选择时间，而40秒。将被加入的表只包含一个ID和文本。我没想到加入两张表时选择的时间会增加8倍。我的JOIN有什么问题，还是这个正常的SQL Server行为？

Joining two tables results in an increased select time from 330 seconds compared to 40 seconds. The table that will be joined consists only of an ID and text. I didn't expect that the select time will increase 8 times when joining the two tables. Is there anything wrong in my JOIN or is this normal SQL Server behaviour?

主表填满了3500万条记录，以查看SQL Server Express如何执行达到10 GB的极限。在LogTimeStamp字段和LogType字段上创建了一个附加索引。

The main table is filled up with 35 million records to see how it performs when the SQL Server Express limit of 10 GB is reached. An additional index was created on the field LogTimeStamp and on the field 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)))

推荐答案

你看到所有这些：

（（convert（datetime2，convert（varchar 255），[Extent1]。[LogTimeStamp]，102）

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

他们是坏的，真的很糟糕，他们基本上说做不要使用索引，进行全表扫描。

They are bad. Really bad. They basically say "do not use an index, make a full table scan".

这样做会让你失望：

t => System.Data.Entity.DbFunctions.TruncateTime（t.LogTimeStamp）> = fromDate

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

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

which is not nice. It is not needed. any timestamp in a date is larger or equal than the date per definition and smaller than the next date.

所以：

t => System.Data.Entity.DbFunctions.TruncateTime（t.LogTimeStamp）> = fromDate

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

变成

t => t.LogTimeStamp> = fromDate

t => t.LogTimeStamp >= fromDate

和

t => System.Data.Entity.DbFunctions.TruncateTime（t.LogTimeStamp）< ; = toDate

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

变成

t => t.LogTimeStamp< ; toDate.AddDays（1）

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

.Where（t => t.LogType！= 4）

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

看起来像一个类型不匹配 - 让我猜，它不是数据库中的一个int。然后使用Equals方法。这是EF中已知的错误。不要紧，应该不要紧，因为这个时候你应该有很多条目，你的问题可能是日期时间比较效率低下的代码。

looks like a type mismatch - let me guess, it is not an int in the database. Then use an Equals method. THis is a known bug in EF. BUt it should not matter - at this point you should be down to quite a few entries, your problem likely is the supe inefficient code for the datetime comparisons.

功能在场侧进行比较。决不。他们杀死任何索引使用（除非有一个与此功能完全相同的索引）。始终重写查询以使所有函数都保持不变。

NEVER do a function on the field side of a comparison. Never. They kill any index use (unless there is an index with exactly this function). Always rewrite the query to have all functions on the constant side.

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

Not an EF issue - a general SQL beginner mistake.

这篇关于表加入实体框架的性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！