我正在开发一个使用 SQL Server 和 EF Core v3 的项目。
我有 4 个彼此相关的表。这是我的表格方案:
我针对这些表编写了 2 个 Linq 查询 - 其中一个使用像这样的连接:
var result = (from emailTemplate in _context.EmailTemplates.Include(et => et.EmailTemplateContents)
join priorityLookup in _context.Lookups on new { GroupKey = "PRIORITIES", DetailKey = emailTemplate.Priority, emailTemplate.CompanyId } equals new { priorityLookup.GroupKey, priorityLookup.DetailKey, priorityLookup.CompanyId }
join statusLookup in _context.Lookups on new { GroupKey = "STATUSES", DetailKey = emailTemplate.StatusCode, emailTemplate.CompanyId } equals new { statusLookup.GroupKey, statusLookup.DetailKey, statusLookup.CompanyId }
join priorityLookupLabel in _context.LookupLabels on new { Locale = 1033, priorityLookup.DetailKey, priorityLookup.CompanyId } equals new { priorityLookupLabel.Locale, priorityLookupLabel.DetailKey, priorityLookupLabel.CompanyId }
join statusLookupLabel in _context.LookupLabels on new { Locale = 1033, statusLookup.DetailKey, statusLookup.CompanyId } equals new { statusLookupLabel.Locale, statusLookupLabel.DetailKey, statusLookupLabel.CompanyId }
where emailTemplate.CompanyId == 3
select new EmailTemplateModel
{
Code = emailTemplate.Code,
TemplateName = emailTemplate.TemplateName,
FromEmail = emailTemplate.FromEmail,
BccEmail = emailTemplate.BccEmail,
CcEmail = emailTemplate.CcEmail,
PriorityCode = emailTemplate.Priority,
Priority = priorityLookupLabel.Label,
Subject = emailTemplate.EmailTemplateContents.Subject,
Body = HttpUtility.HtmlDecode(emailTemplate.EmailTemplateContents.Body),
StatusCode = emailTemplate.StatusCode,
Status = statusLookupLabel.Label,
ToEmail = emailTemplate.ToEmail,
TriggerSqlCommand = emailTemplate.TriggerSqlCommand,
TriggerType = emailTemplate.TriggerType,
ModifDate = emailTemplate.ModifDate
}).ToList();
其中之一使用.Include
像这样:
var results = _context.EmailTemplates
.Where(e => e.CompanyId == 3)
.Include(e => e.EmailTemplateContents)
.Include(e => e.Lookups)
.ThenInclude(g => g.LookupLabels)
.Include(e => e.LookupsNavigation)
.ThenInclude(g => g.LookupLabels)
.Select(e => new EmailTemplateModel
{
Code = e.Code,
TemplateName = e.TemplateName,
FromEmail = e.FromEmail,
BccEmail = e.BccEmail,
CcEmail = e.CcEmail,
PriorityCode = e.Priority,
PriorityLabel = e.Lookups.LookupLabels.FirstOrDefault(l => l.Locale == 1033),
Subject = e.EmailTemplateContents.Subject,
Body = HttpUtility.HtmlDecode(e.EmailTemplateContents.Body),
StatusCode = e.StatusCode,
StatusLabel = e.LookupsNavigation.LookupLabels.FirstOrDefault(l => l.Locale == 1033),
ToEmail = e.ToEmail,
TriggerSqlCommand = e.TriggerSqlCommand,
TriggerType = e.TriggerType,
ModifDate = e.ModifDate
}).ToList();
我试图了解这两种类型之间是否存在任何性能差异,因此我使用探查器检查了 EF 生成的查询。
从 Join 语句生成的 SQL 脚本是:
SELECT
[e].[Code], [e].[TemplateName], [e].[FromEmail], [e].[BccEmail],
[e].[CcEmail], [e].[Priority], [l1].[Label],
[e0].[Subject], [e0].[Body], [e].[StatusCode], [l2].[Label],
[e].[ToEmail], [e].[TriggerSqlCommand], [e].[TriggerType],
[e].[ModifDate]
FROM [EmailTemplates] AS [e]
INNER JOIN [Lookups] AS [l] ON (('PRIORITIES' = [l].[GroupKey]) AND ([e].[Priority] = [l].[DetailKey])) AND ([e].[CompanyId] = [l].[CompanyId])
INNER JOIN [Lookups] AS [l0] ON (('STATUSES' = [l0].[GroupKey]) AND ([e].[StatusCode] = [l0].[DetailKey])) AND ([e].[CompanyId] = [l0].[CompanyId])
INNER JOIN [LookupLabels] AS [l1] ON ((1033 = [l1].[Locale]) AND ([l].[DetailKey] = [l1].[DetailKey])) AND ([l].[CompanyId] = [l1].[CompanyId])
INNER JOIN [LookupLabels] AS [l2] ON ((1033 = [l2].[Locale]) AND ([l0].[DetailKey] = [l2].[DetailKey])) AND ([l0].[CompanyId] = [l2].[CompanyId])
LEFT JOIN [EmailTemplateContents] AS [e0] ON ([e].[CompanyId] = [e0].[CompanyId]) AND ([e].[Code] = [e0].[EmailTemplateCode])
WHERE [e].[CompanyId] = 3
生成的 SQL 脚本.Include
声明是:
SELECT
[e].[Code], [e].[TemplateName], [e].[FromEmail], [e].[BccEmail],
[e].[CcEmail], [e].[Priority], [t0].[DetailKey], [t0].[CompanyId],
[t0].[Locale], [t0].[Label], [t0].[OrderNo], [e0].[Subject], [e0].[Body],
[e].[StatusCode], [t2].[DetailKey], [t2].[CompanyId], [t2].[Locale],
[t2].[Label], [t2].[OrderNo], [e].[ToEmail], [e].[TriggerSqlCommand],
[e].[TriggerType], [e].[ModifDate]
FROM [EmailTemplates] AS [e]
INNER JOIN [Lookups] AS [l] ON ([e].[Priority] = [l].[DetailKey]) AND ([e].[CompanyId] = [l].[CompanyId])
LEFT JOIN [EmailTemplateContents] AS [e0] ON ([e].[CompanyId] = [e0].[CompanyId]) AND ([e].[Code] = [e0].[EmailTemplateCode])
INNER JOIN [Lookups] AS [l0] ON ([e].[StatusCode] = [l0].[DetailKey]) AND ([e].[CompanyId] = [l0].[CompanyId])
LEFT JOIN (
SELECT [t].[DetailKey], [t].[CompanyId], [t].[Locale], [t].[Label], [t].[OrderNo]
FROM (
SELECT [l1].[DetailKey], [l1].[CompanyId], [l1].[Locale], [l1].[Label], [l1].[OrderNo], ROW_NUMBER() OVER(PARTITION BY [l1].[DetailKey], [l1].[CompanyId] ORDER BY [l1].[DetailKey], [l1].[CompanyId], [l1].[Locale]) AS [row]
FROM [LookupLabels] AS [l1]
WHERE [l1].[Locale] = 1033
) AS [t]
WHERE [t].[row] <= 1
) AS [t0] ON ([l].[DetailKey] = [t0].[DetailKey]) AND ([l].[CompanyId] = [t0].[CompanyId])
LEFT JOIN (
SELECT [t1].[DetailKey], [t1].[CompanyId], [t1].[Locale], [t1].[Label], [t1].[OrderNo]
FROM (
SELECT [l2].[DetailKey], [l2].[CompanyId], [l2].[Locale], [l2].[Label], [l2].[OrderNo], ROW_NUMBER() OVER(PARTITION BY [l2].[DetailKey], [l2].[CompanyId] ORDER BY [l2].[DetailKey], [l2].[CompanyId], [l2].[Locale]) AS [row]
FROM [LookupLabels] AS [l2]
WHERE [l2].[Locale] = 1033
) AS [t1]
WHERE [t1].[row] <= 1
) AS [t2] ON ([l0].[DetailKey] = [t2].[DetailKey]) AND ([l0].[CompanyId] = [t2].[CompanyId])
WHERE [e].[CompanyId] = 3
我比较了两者的实际执行计划,看看两者之间有什么区别。
这是连接执行计划:
这是包含执行计划:
两个查询的成本相同 50%。
现在我有几个问题:
- 基于查询成本(50%),我应该考虑这两个相同的性能吗?
- 是否有任何建议使用 include 或 join 来使其中之一更快或成本更低?
- 使用 Join(语法/维护)有哪些优点和缺点?
- 使用 Include(语法/维护)有哪些优点和缺点?
- 如果表有少量记录或有大量记录,我应该使用哪一个?