在 T-SQL 中处理来自 CLR 存储过程的多个结果 [英] Processing multiple results from CLR stored procedure in T-SQL

问题描述

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

I have some complex algorithm written in C# as CLR Stored procedure. Procedure is not deterministic (it depends on current time). The result of procedure are two tables. I didn't found any solution how to process multi-results from stored procedures in T-SQL. The performance of this procedure is key (procedure is called every ~2 seconds).

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

I found the fastest way how to update tables is:

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

它比通过 ADO.NET 从 CLR 过程更新 db-table 快得多.

It's much faster then update db-table from CLR procedure via ADO.NET.

我使用静态字段来存储结果并在执行clr存储过程后查询.

I used static field to store results and query it after the execution of clr stored procedure.

调用栈为:

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

问题是，有时 CLR 函数在静态字段 result 中为 null，但在 CLR 过程中 result 不为 null.我发现，有时在另一个 AppDomain 中调用 CLR 函数而不是 CLR 过程.但是 CLR 过程仍在运行，可以进行下一步操作，不会抛出异常.

The problem is, sometimes CLR functions has null in static field result, but in CLR procedure is result not null. I found, sometimes the CLR functions are called in another AppDomain than CLR procedure. However CLR procedure is still running and can do next operations and no exception is thrown.

有什么方法可以强制在与父"CLR 过程相同的 AppDomain 中调用 CLR 函数?

Is there some way, how to force CLR functions to be called in same AppDomain as "parent" CLR procedure?

或者有其他方法，如何实现我的意图?

Or is there some another way, how to achieve my intention?

P.S.:最初复杂的算法是用 T-SQL 编写的，但性能很差(比 C# 中的算法慢约 100 倍).

P.S.: Originally the complex algorithm was written in T-SQL, but performance was poor (~100x slower than algorithm in C#).

谢谢！

简化代码:

// 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();
            }
        }
    }
}

推荐答案

有两种可能:

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

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

However, there is a particular sequence-of-events nuance to how SQL Server handles unloading App Domains that you are experiencing. What is happening is that your system is experiencing memory pressure and is marking the App Domain to be unloaded. This can be seen in the SQL Server logs as it will tell you the exact name of the App Domain that is being unloaded.

AppDomain 61 ({database_name}.{owner_name}[runtime].60) 由于内存压力被标记为卸载.

AppDomain 61 ({database_name}.{owner_name}[runtime].60) is marked for unload due to memory pressure.

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

When an App Domain is marked for unload, it is allowed to continue running until all currently running process complete. At this point it has a "state" of E_APPDOMAIN_DOOMED instead of the normal E_APPDOMAIN_SHARED. If another process is started, even from within the doomed App Domain, a new App Domain is created. And this is what causes the behavior you are experiencing. The sequence of events is as follows (and yes, I have reproduced this behavior):

1. 执行 MyStoredProcedure:如果应用域 1 尚不存在，则创建它.应用域 1状态"是 E_APPDOMAIN_SHARED.result 设置为 null.

1. Execute MyStoredProcedure: App Domain 1 is created if not already existing. App Domain 1 "state" is E_APPDOMAIN_SHARED. result is set to null.

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

1. result is populated as expected
2. MyStoredProcedure executes GetFirstResultOfMyStoredProcedure: App Domain 1 "state" is still E_APPDOMAIN_SHARED. result is retrieved as expected.
3. App Domain 1 is marked for unload: App Domain 1 "state" is changed to E_APPDOMAIN_DOOMED
4. MyStoredProcedure executes GetSecondResultOfMyStoredProcedure: App Domain 1 "state" is still E_APPDOMAIN_DOOMED and so cannot be used. App Domain 2 is created. App Domain 2 "state" is E_APPDOMAIN_SHARED. result is set to null. This is why you sometimes get nothing back: this process is in App Domain 2 (even though it was initiated from App Domain 1), with no access to App Domain 1.

MyStoredProcedure 完成:应用域 1 已卸载.

MyStoredProcedure completes: App Domain 1 is unloaded.

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

And there is another possibility of how this sequence of events could occur: App Domain 1 could be marked for unload prior to executing GetFirstResultOfMyStoredProcedure. In this case, App Domain 2 is created when GetFirstResultOfMyStoredProcedure is executed, and both it and GetSecondResultOfMyStoredProcedure run in App Domain 2 and return nothing.

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

Hence, if you want / need an error to be thrown under these conditions, then your Get*ResultOfMyStoredProcedure methods need to check if result == null before attempting to retrieve, and if it is null, then throw an error. OR, if it is possible to recalculate the value of what is being stored in the static variable, then if it is null simply repopulate it (e.g. call ComputeComplexAlgorithm again).

一种不太可能的可能性是，由于应用程序域由该代码的所有会话/调用者共享，所以 可能，如果您没有以其他方式确保只有 1一次执行此过程，其他人或 SQL 代理作业或其他东西执行了 MyStoredProcedure，这将在它启动时将静态变量清零.

A less likely possibility is that since the App Domain is shared by all sessions / callers to that code, it is possible, if you have not otherwise ensured that there is only ever 1 execution of this process at a time, that someone else or a SQL Agent job or something, executed MyStoredProcedure which would null out the static variable as it starts.

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

Since you have already accepted using an UNSAFE Assembly in order to get the updateable static variable, you might as well add a locking mechanism to ensure that MyStoredProcedure is single-threaded.

除了需要注意的那些方面之外，这个过程很可能会以更快的速度完成，并且不会那么复杂.可以使用表值参数 (TVP) 将数据流式传输回 SQL Server，就像从应用程序代码中一样.只需创建一两个用户定义的表类型 (UDTT)，以匹配 TVF 返回的两个结果集的结构(GetFirstResultOfMyStoredProcedure 和 GetSecondResultOfMyStoredProcedure).请在此处查看我的回答，了解如何正确地流式传输结果.通过使用此模型，您可以:

Aside from those areas to look at, this process could most likely be done even faster and in a less convoluted manner. You can use Table-Valued Parameters (TVPs) to stream data back to SQL Server, just like you would from app code. Just create one or two User-Defined Table Types (UDTTs) that match the structures of the two result sets being returned by the TVFs (GetFirstResultOfMyStoredProcedure and GetSecondResultOfMyStoredProcedure). Please see my answer here regarding how to properly stream in the results. By using this model, you can:

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

• do both updates inline in the MyStoredProcedure CLR proc
• get rid of the static variable
• Possibly no more need for UNSAFE (if it was only being used for the static variable). You might still need EXTERNAL_ACCESS if you can't pass back the results over the Context Connection, in which case you would use a regular connection (i.e. connection string uses either "Server=(local)" or doesn't specify "Server").
• get rid of UpdateDataFromMyStoredProcedure method
• get rid of UpdateDataFromMyStoredProcedure T-SQL proc
• get rid of GetFirstResultOfMyStoredProcedure CLR function
• get rid of GetSecondResultOfMyStoredProcedure CLR function
• free up all of the memory that is currently being used by that static variable to hold two result sets!!

这种方法不仅更易于维护并且很可能更快，而且还不允许新的应用程序域出现您在这里遇到的未初始化的静态变量问题:-).

Not only is this approach easier to maintain and most likely faster, it also does not allow for the new App Domain with uninitialized static variable issue that you are running into here :-).

这篇关于在 T-SQL 中处理来自 CLR 存储过程的多个结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！