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

问题描述

加入在330秒增加选择的时间的两个表的结果相比，40秒。将要加入本表由唯一的ID和文本。我没想到的是连接两个表时选择的时间将增加8倍。这有什么错我的JOIN或这是正常的SQL Server的行为？

主表中充满了35万条记录，看看它如何执行时的SQL Server Express达到10 GB的限制。一个附加的指数上的场LOGTIMESTAMP并在字段LOGTYPE创建。连接表

内容：

  VAR queryList =消息
。加入（类型，
型=> type.LogType，
的TypeText => typeText.LogType，（味精，MSGTYPE）=>新建
 {
 msg.LogID，
 msg.LogTimeStamp，
 msg.LogUser，
 msg.LogType，
 msgType.LogTypeName，
 msg.LogMessage 
}）
。凡（ T => System.Data.Entity.DbFunctions.TruncateTime（t.LogTimeStamp）> =寄件者）
。凡（T => System.Data.Entity.DbFunctions.TruncateTime（t.LogTimeStamp）LT; = TODATE）
。凡（T =>！t.LogType = 4）
 .OrderBy（M = GT; 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] 
从[DBO]。[AuditTrailMessages] AS [Extent1] 
 INNER JOIN [DBO]。[AuditTrailLogTypes] AS [Extent2] ON [Extent1。 [LOGTYPE] = [Extent2] [LOGTYPE] 
 WHERE（（转换（DATETIME2，转换（VARCHAR（255），[Extent1] [LOGTIMESTAMP]，102），102））方式> = @ p__linq__0）
和（（转换（DATETIME2，转换（VARCHAR（255），[Extent1] [LOGTIMESTAMP]，102），102））< = @ p__linq__1）
 AND（NOT（（4 = CAST（ [Extent1]。[LOGTYPE]为int））AND（CAST（[Extent1]。[LOGTYPE]为int）IS NOT NULL）））
  

相比，

  VAR queryList =消息
。凡（T => ; System.Data.Entity.DbFunctions.TruncateTime（t.LogTimeStamp）> =寄件者）
。凡（T => System.Data.Entity.DbFunctions.TruncateTime（t.LogTimeStamp）LT = TODATE）
。凡（T =>！t.LogType = 4）
 .OrderBy（M = GT; 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] 
从[DBO]。[AuditTrailMessages] AS [ Extent1] 
 WHERE（（转换（DATETIME2，转换（VARCHAR（255），[Extent1] [LOGTIMESTAMP]，102），102））方式> = @ p__linq__0）
和（（转换（DATETIME2 ，转换（VARCHAR（255），[Extent1] [LOGTIMESTAMP]，102），102））< = @ p__linq__1）
 AND（NOT（（4 = CAST（[Extent1] [LOGTYPE]为int。 ））AND（CAST（[Extent1]。[LOGTYPE]为int）IS NOT NULL）））
  

解决方案

你看到所有这些：

（（转换（DATETIME2，转换（VARCHAR（ 255）[Extent1]。[LOGTIMESTAMP]，102）

他们是坏的，真的不错。他们基本上说：不要用。一个索引，做一个全表扫描

他们跑到你这样做的：

T => System.Data.Entity.DbFunctions.TruncateTime（t.LogTimeStamp）> =寄件者

这是不不错。它是没有必要的。在日期的时间戳比每个定义，比下一个日期较小的日期大于或等于

所以：

T => System.Data.Entity.DbFunctions.TruncateTime（t.LogTimeStamp）> =寄件者

变成

T => t.LogTimeStamp> =寄件者

和

T => System.Data.Entity.DbFunctions.TruncateTime（t.LogTimeStamp）LT = TODATE

变成

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

。凡（T => t.LogType！= 4）

< /块引用>

看起来像一个类型不匹配 - 让我猜，这是不是在数据库的int。然后使用equals方法。这是EF一个已知的bug。但应该没有关系 - 在这一点上，你应该是下来了不少项目，您的问题可能是日期时间比较的苏佩低效的代码

永远不会做一个在比较的现场一侧的功能。决不。他们杀死任何指数的使用（除非有与正是这种功能的指标）。永远重写查询会对恒端的所有功能。

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

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?

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.

Content of joined table:

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();

with resulting 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)))  

compared to

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();

with resulting 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)))

解决方案

Do you see all of those:

((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".

THey run down to you doing:

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.

So:

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

turns into

t => t.LogTimeStamp >= fromDate

and

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

turns into

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

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

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.

Not an EF issue - a general SQL beginner mistake.

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