SQL Server 2019 SQLCLR函数foreach性能优化咨询

我有一个用于SQL Server 2019的SQLCLR函数，会被调用数十亿次，当前执行耗时较长，希望针对foreach相关逻辑做性能优化。

原函数代码 #

[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.None,
                                        SystemDataAccess = SystemDataAccessKind.None,
                                        IsDeterministic = true,
                                        IsPrecise = true)]
public static SqlInt16 func(
                            [SqlFacet(MaxSize = 1750)] SqlString cB,
                            [SqlFacet(MaxSize = 760)] SqlString sC
                           )
{
    int cnt = 0; // 补充原代码缺失的变量声明
    foreach (string code_div in cB.Value.Split('/'))
    {
        cnt = (code_div.Length - code_div.Replace("+", string.Empty).Length) - (code_div.Length - code_div.Replace("+-", string.Empty).Length) / 2;
        bool flag = true;
        foreach (string code_plus in code_div.Split(new[] { '+' }, StringSplitOptions.RemoveEmptyEntries))
        {
            if (code_plus[0] == '-')
            {
                if ((',' + sC.Value + ',').IndexOf(',' + code_plus.Substring(1) + ',') != -1)
                {
                    flag = false;
                    break;
                }
            }
            else
            {
                if ((',' + sC.Value + ',').IndexOf(',' + code_plus + ',') == -1)
                {
                    flag = false;
                    break;
                }
            }
        }

        if (flag)
        {
            return (SqlInt16)cnt;
        }
    }
    return (SqlInt16)(-1);
}

逻辑说明 #

• '/'代表OR逻辑：只要有一个以'/'分隔的片段满足条件，就返回该片段的计数
• '+'代表AND逻辑：片段内所有以'+'分隔的条件都需满足
• '-'代表NOT逻辑：带'-'的条件表示sC中不能包含该值
• 计数规则：片段中'+'的总出现次数，减去'+-'连续出现次数的一半（因为每个'+-'会多算一个'+'）

示例数据 #

string sC = "021A,775U,000A,021A,1U2,206B,240,249,255B,260,263B,280,294,2U1,306B,336B,345,427,440,442,474,477,4U4,500,508,523,543L,580,584,58U,5XXL,600,608,622,677,690,775U,802,831,909,928,953,966,99N,A20,A62,A66,A86,A89,B03,B09,B12,F204,FW,G996,GA,H80,HA,J82,K11,K13,L,M013,M22,M651,P49,R01,R75,U01,U18,U41,V22,VL,VR";

// func返回2
string cB = "+240+802/+240+803";

// func返回2
string cB = "-772+240+802/-P55+240+802/-772+240+803/-P55+240+803/-772+240+804/-P55+240+804/-772+240+805/-P55+240+805/-772+240+806/-P55+240+806";

// func返回-1
string cB = "+-220+-299+-301+-355+-409+-518+-537+-540+-551+-810+-873+-889+-950+808";

性能瓶颈分析 #

1. 重复Split操作：两次Split会生成大量字符串数组，频繁触发GC，在高调用量下开销极大
2. 字符串拼接与IndexOf：每次判断都拼接,xxx,并调用IndexOf，属于O(n)查找，重复执行数十亿次后累计开销惊人
3. 字符串Replace计数：用Replace计算'+'和'+-'的数量，会创建多个临时字符串，增加内存分配

优化方案与代码 #

核心优化措施 #

• 预解析sC为HashSet<string>，将O(n)的IndexOf查找改为O(1)的Contains判断
• 改用逐字符遍历解析cB，避免Split产生的大量字符串对象
• 遍历过程中直接计数'+'和'+-'，避免Replace操作

优化后的代码 #

[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.None,
                                        SystemDataAccess = SystemDataAccessKind.None,
                                        IsDeterministic = true,
                                        IsPrecise = true)]
public static SqlInt16 OptimizedFunc(
                            [SqlFacet(MaxSize = 1750)] SqlString cB,
                            [SqlFacet(MaxSize = 760)] SqlString sC
                           )
{
    // 空值处理（增强鲁棒性）
    if (cB.IsNull || sC.IsNull)
        return (SqlInt16)-1;

    string cBValue = cB.Value;
    string sCValue = sC.Value;

    // 预解析sC为HashSet，一次拆分终身受益
    HashSet<string> sCSet = new HashSet<string>(sCValue.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries));

    int currentPos = 0;
    int length = cBValue.Length;

    while (currentPos < length)
    {
        int orEnd = cBValue.IndexOf('/', currentPos);
        orEnd = orEnd == -1 ? length : orEnd;

        // 处理当前OR片段，同时计算cnt和'+-'数量
        int cnt = 0;
        int plusMinusCount = 0;
        bool segmentValid = true;
        int conditionStart = currentPos;

        for (int i = currentPos; i < orEnd; i++)
        {
            char c = cBValue[i];
            if (c == '+')
            {
                cnt++;
                // 统计'+-'连续出现次数
                if (i + 1 < orEnd && cBValue[i + 1] == '-')
                {
                    plusMinusCount++;
                }
                // 处理之前的条件
                if (conditionStart < i)
                {
                    string condition = cBValue.Substring(conditionStart, i - conditionStart);
                    if (!CheckCondition(condition, sCSet))
                    {
                        segmentValid = false;
                        break;
                    }
                }
                conditionStart = i + 1;
            }
        }

        // 处理最后一个条件
        if (segmentValid && conditionStart < orEnd)
        {
            string condition = cBValue.Substring(conditionStart, orEnd - conditionStart);
            segmentValid = CheckCondition(condition, sCSet);
        }

        // 调整cnt：减去'+-'的数量（每个'+-'多算一个'+'）
        cnt -= plusMinusCount;

        if (segmentValid)
        {
            return (SqlInt16)cnt;
        }

        currentPos = orEnd + 1;
    }

    return (SqlInt16)-1;
}

// 提取条件判断逻辑，复用代码
private static bool CheckCondition(string condition, HashSet<string> sCSet)
{
    if (string.IsNullOrEmpty(condition))
        return false;

    if (condition[0] == '-')
    {
        // NOT逻辑：sC中不能包含该值
        return !sCSet.Contains(condition.Substring(1));
    }
    else
    {
        // AND逻辑：sC中必须包含该值
        return sCSet.Contains(condition);
    }
}

优化效果说明 #

1. 查找效率提升：HashSet的Contains是O(1)操作，替代原O(n)的IndexOf，高调用量下差异巨大
2. 减少内存分配：避免Split和Replace产生的大量临时字符串，降低GC频率
3. 减少遍历次数：逐字符遍历一次性完成条件解析和计数，避免多次遍历同一字符串

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