LINQ子串过滤查询性能低下问题排查与优化求助

针对生产环境中LINQ查询性能差、DTU飙升的问题，以下是具体优化方案：

1. 拆分查询逻辑，规避CASE表达式导致的索引失效 #

原查询用CASE表达式合并Subject和Contact的Name字段，导致SQL无法有效利用两个表的Name索引。将条件拆为两个独立分支，让查询优化器分别处理：

修改后的LINQ代码：

if (!string.IsNullOrEmpty(Filter.Client))
{
    var searchedTerm = Filter.Client;
    query = query.Where(p =>
        p.AddressBookLinkage.Any(linkage => 
            // 分支1：匹配主体名称
            (linkage.MainContactId == null && linkage.MainSubject.Name.Contains(searchedTerm))
            // 分支2：匹配联系人名称
            || (linkage.MainContactId != null && linkage.MainContact.Name.Contains(searchedTerm))
        ));
}
// 统一在外部执行ToList()，避免逻辑嵌套错误
var sqlQuery = query.ToList();

此修改会生成两个独立的OR条件，查询优化器可分别尝试使用Subjects.Name和Contacts.Name的索引，减少全表扫描概率。

2. 创建计算列+专用索引，彻底解决子串查询性能问题 #

子串查询（%xxx%）无法利用普通前缀索引，可通过在AddressBookProjectLinkages表上创建计算列预存关联名称，再为该列建索引：

步骤1：添加计算列 #

执行SQL创建自动关联Subject/Contact名称的计算列：

ALTER TABLE AddressBookProjectLinkages
ADD LinkedName AS 
    CASE 
        WHEN MainContactId IS NULL THEN (SELECT s.Name FROM Subjects s WHERE s.Id = MainSubjectId)
        ELSE (SELECT c.Name FROM Contacts c WHERE c.Id = MainContactId)
    END

步骤2：为计算列建非聚集索引 #

CREATE NONCLUSTERED INDEX IX_AddressBookProjectLinkages_LinkedName
ON AddressBookProjectLinkages (LinkedName)
INCLUDE (ProjectId) -- 包含ProjectId，避免查询时的键查找

步骤3：修改LINQ查询使用计算列 #

if (!string.IsNullOrEmpty(Filter.Client))
{
    var searchedTerm = Filter.Client;
    query = query.Where(p => p.AddressBookLinkage.Any(linkage => linkage.LinkedName.Contains(searchedTerm)));
}
var sqlQuery = query.ToList();

该方案将关联查询提前到计算列维护阶段，查询时直接使用计算列索引，无需再关联Subject和Contact表，大幅降低IO开销。

3. 排查DTU资源瓶颈 #

虽然数据库仅275MB，但10 DTU的标准层可能无法应对生产查询负载：

• 临时升级到20 DTU标准层测试，若查询耗时明显降低且DTU不再满负荷，说明当前资源配置不足；
• 考虑切换至vCore模型，可更灵活调整CPU/内存资源，避免DTU硬性限制。

4. 过滤空字符串查询 #

原SQL包含(@__searchedTerm_2 LIKE N'')的无效条件，空搜索词会触发全表扫描。确保仅在搜索词非空时应用过滤逻辑，避免不必要的性能消耗。

内容的提问来源于stack exchange，提问作者user27910466