在 T-SQL 中处理来自 CLR 存储过程的多个结果

本文关键字：存储过程 结果 CLR T-SQL 处理 | 更新日期: 2023-09-27 17:57:44

我有一些用C#编写的复杂算法作为CLR存储过程。过程不是确定性的(取决于当前时间(。过程的结果是两个表。我没有找到任何解决方案来处理来自 T-SQL 中存储过程的多结果。此过程的性能是关键(每~2秒调用一次该过程(。

我发现更新表格的最快方法是：

UPDATE [db-table] SET ... SELECT * FROM [clr-func]

它比通过 ADO.NET 从 CLR 过程更新数据库表要快得多。

我使用静态字段来存储结果，并在执行 clr 存储过程后对其进行查询。

调用堆栈为：

T-SQL proc
    -> CLR proc (MyStoredProcedure)
        -> T-SQL proc (UpdateDataFromMyStoredProcedure)
            -> CLR func (GetFirstResultOfMyStoredProcedure)
            -> CLR func (GetSecondResultOfMyStoredProcedure)

问题是，有时CLR函数在静态字段result中为空，但在CLR过程中result不为空。我发现，有时CLR函数是在CLR过程以外的另一个AppDomain中调用的。但是，CLR 过程仍在运行，可以执行后续操作，并且不会引发异常。

有没有办法，如何强制CLR函数在与"父"CLR过程相同的AppDomain中调用？

还是有别的办法，如何实现我的意图？

PS：最初复杂的算法是用T-SQL编写的，但性能很差(~比C#中的算法慢100倍(。

谢谢！

简化代码：

// T-SQL
CREATE PROC [dbo].[UpdateDataFromMyStoredProcedure] AS BEGIN
    UPDATE [dbo].[tblObject]
        SET ...
        SELECT * FROM [dbo].[GetFirstResultOfMyStoredProcedure]()
    UPDATE [dbo].[tblObjectAction]
        SET ...
        SELECT * FROM [dbo].[GetSecondResultOfMyStoredProcedure]()
END
// ... somewhere else
EXEC [dbo].[MyStoredProcedure]

-

// C#
public class StoredProcedures {
    // store for result of "MyStoredProcedure ()"
    private static MyStoredProcedureResult result;
    [SqlProcedure]
    public static int MyStoredProcedure() {
        result = null;
        result = ComputeComplexAlgorithm();
        UpdateDataFromMyStoredProcedure();
        result = null;
    }
    [SqlFunction(...)]
    public static IEnumerable GetFirstResultOfMyStoredProcedure() {
        return result.First;
    }
    [SqlFunction(...)]
    public static IEnumerable GetSecondResultOfMyStoredProcedure() {
        return result.Second;
    }
    private static void UpdateDataFromMyStoredProcedure() {
        using(var cnn = new SqlConnection("context connection=true")) {
            using(var cmd = cnn.CreateCommand()) {
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CommandText = "[dbo].[UpdateDataFromMyStoredProcedure]";
                cmd.ExecuteNonQuery();
            }
        }
    }
}

在 T-SQL 中处理来自 CLR 存储过程的多个结果

有两种可能性：

  • 更可能的情况与应用程序域由于内存压力而被卸载有关。一般来说，特定程序集(因此其中的代码(只有一个应用程序域，因为应用程序域是按数据库、每个所有者的。因此，您的代码不会在两个应用程序域中调用，至少在概念上没有。

    但是，对于 SQL Server 处理卸载您遇到的应用程序域的方式，存在特定的事件顺序细微差别。正在发生的事情是，您的系统遇到内存压力，并且正在将应用程序域标记为要卸载。这可以在 SQL Server 日志中看到，因为它会告诉您正在卸载的应用程序域的确切名称。

    AppDomain 61 ({database_name}.{owner_name}[运行时].60( 由于内存压力而被标记为卸载。

    当应用程序域标记为卸载时，允许它继续运行，直到所有当前正在运行的进程完成。此时，它的"状态"为E_APPDOMAIN_DOOMED，而不是正常的E_APPDOMAIN_SHARED。如果启动了另一个进程，即使从注定要失败的应用程序域中启动，也会创建一个新的应用程序域。这就是导致您正在经历的行为的原因。事件的顺序如下(是的，我已经重现了这种行为(：

    1. 执行MyStoredProcedure：如果应用程序域 1 尚不存在，则创建应用程序域 1。应用域 1 "状态"为 E_APPDOMAIN_SHARED 。 result设置为 null 。
      1. result按预期填充
      2. MyStoredProcedure执行GetFirstResultOfMyStoredProcedure：应用域 1 的"状态"仍E_APPDOMAIN_SHARED。 result按预期检索。
        3. 应用程序域 1
      3. 标记为卸载：应用程序域 1"状态"更改为E_APPDOMAIN_DOOMED
      4. MyStoredProcedure执行GetSecondResultOfMyStoredProcedure：应用程序域 1 "状态"仍E_APPDOMAIN_DOOMED，因此无法使用。应用程序域 2 已创建。应用程序域 2 "状态"E_APPDOMAIN_SHARED 。 result设置为 null 。这就是为什么您有时什么也得不到回复的原因：此过程位于应用程序域 2 中(即使它是从应用程序域 1 启动的(，无法访问应用程序域 1。
    2. MyStoredProcedure完成：应用程序域 1 已卸载。


    这一系列事件还有另一种可能发生的可能性：可以在执行GetFirstResultOfMyStoredProcedure之前将应用程序域 1 标记为卸载。在这种情况下，应用程序域 2 是在执行 GetFirstResultOfMyStoredProcedure 时创建的，它和 GetSecondResultOfMyStoredProcedure 都在应用程序域 2 中运行，不返回任何内容。

    因此，如果您希望/需要在这些条件下抛出错误，那么您Get*ResultOfMyStoredProcedure方法需要在尝试检索之前检查是否result == null，如果为 null，则抛出错误。或者，如果可以重新计算存储在静态变量中的值，那么如果null只需重新填充它(例如再次调用ComputeComplexAlgorithm(。

    • 不太可能的是，

  • 由于应用程序域由该代码的所有会话/调用方共享，因此，如果您没有以其他方式确保一次只执行 1 次此过程，则可能会执行其他人或 SQL 代理作业或其他内容MyStoredProcedure这会在静态变量启动时将其清空。

    由于您已经接受使用 UNSAFE 程序集来获取可更新的静态变量，因此不妨添加一个锁定机制以确保MyStoredProcedure是单线程的。

除了这些需要研究的领域之外，这个过程很可能以更快、不那么复杂的方式完成。可以使用表值参数 (TVP( 将数据流式传输回 SQL Server，就像从应用代码流式传输一样。只需创建一个或两个用户定义的表类型 (UDTT(，它们与 TVF(GetFirstResultOfMyStoredProcedure 和 GetSecondResultOfMyStoredProcedure(返回的两个结果集的结构相匹配。请在此处查看我关于如何正确流式传输结果的答案。通过使用此模型，您可以：

  • 在 MyStoredProcedure CLR 进程中内联执行这两个更新
  • 摆脱静态变量
  • 可能不再需要UNSAFE(如果它仅用于静态变量(。如果无法通过上下文连接传回结果，则可能仍需要EXTERNAL_ACCESS，在这种情况下，您将使用常规连接(即连接字符串使用"Server=(local("或未指定"Server"(。
  • 摆脱UpdateDataFromMyStoredProcedure方法
  • 摆脱UpdateDataFromMyStoredProcedure T-SQL 进程
  • 摆脱GetFirstResultOfMyStoredProcedure CLR 函数
  • 摆脱GetSecondResultOfMyStoredProcedure CLR 函数
  • 释放该静态变量当前正在使用的所有内存来保存两个结果集！！

这种方法不仅更容易维护，而且很可能更快，而且还不允许您在此处遇到的具有未初始化静态变量问题的新应用程序域：-(。

根据

Bob Beauchemin的说法，"SQLCLR为每个程序集所有者创建一个应用程序域，而不是每个数据库创建一个应用程序域"两个 SQLCLR 程序集是否具有相同的所有者？

相关文章：