c# - LINQ to Entities 查询编译时间长，SQL 运行速度快

Question

我正在编写一段由同事编写的代码，它与我们公司使用的 CRM 应用程序交互。这段代码中有两个 LINQ to Entities 查询在我们的应用程序中执行了很多次，我被要求优化它们，因为其中一个非常慢。

这些是查询:

第一个查询，这个查询几乎可以立即编译。它从 CRM 数据库获取关系信息，通过应用程序提供的关系 ID 列表进行过滤:

from relation in context.ADRELATION
where ((relationIds.Contains(relation.FIDADRELATION)) && (relation.FLDELETED != -1))
join addressTable in context.ADDRESS on relation.FIDADDRESS equals addressTable.FIDADDRESS
   into temporaryAddressTable
from address in temporaryAddressTable.DefaultIfEmpty()
join mailAddressTable in context.ADDRESS on relation.FIDMAILADDRESS equals
   mailAddressTable.FIDADDRESS into temporaryMailAddressTable
from mailAddress in temporaryMailAddressTable.DefaultIfEmpty()
select new { Relation = relation, Address = address, MailAddress = mailAddress };

第二个查询，编译大约需要 4-5 秒，并从数据库中获取有关人员的信息(再次通过 ID 列表过滤):

from role in context.ROLE
join relationTable in context.ADRELATION on role.FIDADRELATION equals relationTable.FIDADRELATION into temporaryRelationTable
from relation in temporaryRelationTable.DefaultIfEmpty()
join personTable in context.PERSON on role.FIDPERS equals personTable.FIDPERS into temporaryPersonTable
from person in temporaryPersonTable.DefaultIfEmpty()
join nationalityTable in context.TBNATION on person.FIDTBNATION equals nationalityTable.FIDTBNATION into temporaryNationalities
from nationality in temporaryNationalities.DefaultIfEmpty()
join titelTable in context.TBTITLE on person.FIDTBTITLE equals titelTable.FIDTBTITLE into temporaryTitles
from title in temporaryTitles.DefaultIfEmpty()
join suffixTable in context.TBSUFFIX on person.FIDTBSUFFIX equals suffixTable.FIDTBSUFFIX into temporarySuffixes
from suffix in temporarySuffixes.DefaultIfEmpty()
where ((rolIds.Contains(role.FIDROLE)) && (relation.FLDELETED != -1))
select new { Role = role, Person = person, relation = relation, Nationality = nationality, Title = title.FTXTBTITLE, Suffix = suffix.FTXTBSUFFIX };

我已经设置了 SQL Profiler 并从两个查询中获取了 SQL，然后在 SQL Server Management Studio 中运行它。两个查询都运行得非常快，即使有大量(~1000)个 ID。所以问题似乎出在 LINQ 查询的编译上。

我曾尝试使用编译查询，但由于这些查询只能包含原始参数，因此我不得不使用过滤器去除部分并在 Invoke() 调用之后应用它，所以我不确定这是否有帮助很多。此外，由于这段代码在 WCF 服务操作中运行，我不确定编译后的查询在后续调用中是否仍然存在。

最后我尝试的是在第二个查询中只选择一个列。虽然这显然不会给我所需的信息，但我认为它会比我们现在选择的大约 200 列更快。没有这种情况，还是需要4-5秒。

我根本不是 LINQ 高手，所以我几乎无法理解这段代码(我感觉它的编写不是最佳的，但我不能指手画脚)。任何人都可以提示我为什么会出现此问题吗？

我剩下的唯一解决方案是手动选择所有信息，而不是连接所有这些表。然后我会得到大约 5-6 个查询。我猜还不错，但由于我在这里处理的不是极其低效的 SQL(或者至少是可接受的低效水平)，我希望能够避免这种情况。

提前致谢，希望我说清楚了。如果没有，请随时询问，我会提供更多详细信息。

---

编辑:我最终在我的 Entity Framework 上添加了关联(目标数据库没有指定外键)并因此重写了查询:

context.ROLE.Where(role => rolIds.Contains(role.FIDROLE) && role.Relation.FLDELETED != -1)
            .Select(role => new 
                            { 
                                ContactId = role.FIDROLE, 
                                Person = role.Person, 
                                Nationality = role.Person.Nationality.FTXTBNATION,
                                Title = role.Person.Title.FTXTBTITLE,
                                Suffix = role.Person.Suffix.FTXTBSUFFIX
                            });

看起来更具可读性，而且速度也更快。

感谢您的建议，我一定会牢记关于为不同数量的参数进行多个编译查询的建议!

Answer 1

Gabriels 的回答是正确的:使用编译查询。

看起来您正在为每个 WCF 请求重新编译它，这当然违背了一次性初始化的目的。相反，将编译后的查询放入静态字段。

编辑:

执行此操作:向您的服务发送最大负载并暂停调试器 10 次。查看调用堆栈。它在 L2S 代码或 ADO.NET 代码中停止得更频繁吗？这将告诉您问题是否仍然出在 L2S 或 SQL Server 上。

接下来，让我们修复过滤器。我们需要将它推回到编译的查询中。这只有通过转换才能实现:

rolIds.Contains(role.FIDROLE)

为此:

role.FIDROLE == rolIds_0 || role.FIDROLE == rolIds_1 || ...

您需要为 rolId 的每个基数进行新的编译查询。这很讨厌，但有必要让它编译。在我的项目中，我已将此任务自动化，但您可以在此处执行一次性解决方案。

我想大多数查询的角色 ID 都很少，因此您可以为基数 1-10 实现 10 个编译查询，如果基数超过 10，您将退回到客户端过滤。