CLR sql server performance

本文关键字：performance server sql CLR | 更新日期: 2023-09-27 18:26:23

我们在ETL过程中使用CLR函数来集中特定的数据转换和数据检查逻辑。这些功能是相当基本的，不需要数据访问，并且是确定性的，因此允许并行性。

例如：

[SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true, SystemDataAccess = SystemDataAccessKind.None, IsPrecise = true)]
public static bool check_smallint(string input)
{
    string teststring;
    try
    {
        teststring = input.Trim(' ').Replace('-', '0');
        if (teststring.Length == 0)
        {
            teststring = "0";
        }
        Convert.ToInt16(teststring);
    }
    catch (NullReferenceException)
    {
        return true;
    }
    catch (FormatException)
    {
        return false;
    }
    catch (OverflowException)
    {
        return false;
    }
    return true;
}

除了性能外，这一切都很好。查询的速度大大减慢，这给处理大型数据集（数百万行甚至更多）带来了麻烦。

到目前为止，我们还没有发现真正了解SQL CLR体系结构的人，但我们收到的一个建议是，这可能是由创建新连接或为每个函数调用分配内存的开销引起的。因此，一个解决方案可以是连接/内存池。

请不要提出不同的解决方案，我们已经在考虑了，比如内联sql，或者完全不同的方法。在许多情况下，标准sql函数是没有选择的，因为没有引发错误。

PS。我们使用的是SQL 2008R2。

CLR sql server performance

通过创建新连接或为每个函数调用分配内存的开销。因此，一个解决方案可以是连接/内存池。

在C#方面，这不是你必须担心的事情。您没有分配内存（当然，您分配的是字符串和函数中需要的东西，没有任何东西可以池化/重用）。此外，连接也不是你必须担心的事情。

除了性能外，这一切都很好。

您的代码正在做一些令人难以置信的事情。。。异常。。。slow：抛出异常而不是执行检查异常是一种可扩展的操作，应用于处理异常情况（只有100/200条记录具有null或无效值，这将减慢超过1000000条记录的查询速度）。数据库列中的输入格式或null值错误。。。不例外（这种编程风格-例外而不是检查-是允许的，甚至在Python等其他语言中也是鼓励的。我通常会在C#中避免它。当然，在性能有问题的地方，这是不合适的）。

public static bool check_smallint(string input)
{
    if (String.IsNullOrWhiteSpace(input))
        return true;
    short value;
    return Int16.TryParse(input, out value);
}

注意：对于null输入或仅由空格组成的字符串（替换Trim()和NullReferenceException内容），String.IsNullOrWhiteSpace(input)将返回true。所有其他内容（对于不是整数或具有OverflowException的过大数字的输入文本的FormatException）都由Int16.TryParse()处理。对于有效的输入，代码更短（并且稍微快一点），但对于无效的则快很多倍。

我将这作为一个单独的答案，而不是对@Adriano的答案的评论，这样就不太可能错过它（因为不是每个人都阅读所有评论）

除了按照@Adriano的建议更改方法外，您还应该为所有输入/输出参数和返回值使用System.Data.SqlTypes命名空间中的适当数据类型。使用它们有一些重要的区别和好处，例如它们都具有.IsNull属性。完整的差异列表太多了，无法放在这里，但我在下面的文章中记录了它：通往SQLCLR Level 5的阶梯：开发（在SQL Server中使用.NET）

调整@Adriano的代码以使用正确的类型会给你以下信息：

public static SqlBoolean check_smallint(SqlString input)
{
    if (input.IsNull)
        return true;
    if (input.Value.Trim() == String.Empty)
        return true;
    short value;
    return Int16.TryParse(input.Value, out value);
}
相关文章：