CODEWITHSUNDEEP
c#sqlsql-serverentity-frameworkjoin
MarkusEgle
MarkusEgle

Reputation: 3065

Table Join performance issue with Entity Framework

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:

Content of table Logtypes

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

Upvotes: 1

Views: 2685

Answers (1)

TomTom
TomTom

Reputation: 62159

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.

Upvotes: 7

Related Questions