CLR 触发器状态
我考虑使用 CLR 触发器而不是传统的 T-SQL 触发器，因为我需要使用一些已经在 C# 中实现的逻辑。我知道 SQL Server 支持 CLR 集成，就我而言，这似乎是一个值得一试的解决方案。
但是，我想要执行的操作可能会有点慢。速度还不够慢，无法完全排除在触发操作中使用它们，但在插入数十万条记录时可能会明显慢。最慢的部分可以从缓存中受益匪浅，我想这将是很少的缓存未命中和数千次缓存命中。这一切都引出了一个问题：CLR 触发器可以有任何状态吗？而且，更重要的是，这个状态的生命周期是怎样的？
我想我可以使用触发器类的静态字段来保存某些状态，但我不知道它何时初始化（服务器何时启动？事务启动时？未指定？）。我不确定这是否是安全途径，因此询问在 CLR 触发器中使用某些状态的常见做法是什么（如果有）。
为了避免混淆：我需要缓存CLR对象，而不是一些SQL查询的结果，所以这不是SQL Server本身在缓存方面有多好，我想缓存一些不属于的数据到数据库。另外，我认为 CLR 并不是因为我无法在 T-SQL 中进行字符串操作和边界检查。我需要执行一些在 CLR 类库中实现的逻辑，并且有很多依赖项。在这种情况下我是否应该使用触发器是另一个与此几乎无关的问题。
非常感谢。
PS：我将不胜感激任何有关该主题的评论和见解，即使是那些没有直接回答我的问题的评论和见解，但请不要全都说“触发器是邪恶的，不应该是”使用过”和“CLR 集成很慢并且是一个主要的兼容性问题”。另外，我知道它可能会对某人尖叫“过早的优化”，但目前我只想知道我的优化选项是什么，因为我是 SQL Server 中的 CLR 集成的新手。除非分析结果表明如此，否则我不会对其进行优化，但我不想在实现整个过程时意识到它太慢并且我对此无能为力。
我使用 SQL Server 2008 和 .NET 3.5。
I consider using CLR trigger instead of traditional T-SQL one because I need to use some logic that is already implemented in C#. I'm aware that SQL server supports CLR integration and in my case it seems like a solution that's worth a shot.
However, the operations I want to perform can be somewhat slow. Not slow enough to rule out using them in triggered actions completely, but probably noticeably slow when it comes to inserting hundreds of thousands of records. The slowest part can strongly benefit from caching, I suppose that it will be very few cache misses and thousands of cache hits. At this point it all leads to a question: can CLR triggers have any state? And, more important, what's the life cycle of this state?
I suppose I could use static fields of trigger class to hold some state, but I have no idea when it gets initialized (When the server is started? At transaction start? Not specified?). I am not sure if it's the safe route and therefore ask what the common practices for using some state in CLR triggers are (if any).
To avoid confusion: I need to cache CLR objects, not the results of some SQL queries, so it's not about how good SQL Server itself is at caching, I want to cache some data that doesn't belong to database. Also, I consider CLR not because I can't do string manipulations and bound checking in T-SQL. I need to execute some logic that is implemented in CLR class library and has a lot of dependencies. Wether I should use triggers in this case is another question that has almost nothing to do with this one.
Many thanks in advance.
PS: I will appreciate any comments and insights on topic, even the ones that don't answer my question directly, but please don't make it all about "triggers are evil and shouldn't ever be used" and "CLR integration is slow and a major compatibility pain". Also, I know that it may scream "premature optimization" to someone, but at the moment I just want to know what my optimization options are going in since I'm new to CLR integration in SQL server. I won't optimize it unless profiling results suggest so, but I don't want to implement the whole thing to realize it's too slow and there is nothing I can do about it.
I use SQL Server 2008 and .NET 3.5.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
虽然可以在 SQLCLR 触发器类中使用
静态类字段来缓存值，但有几件事您需要非常小心：有多少数据你打算缓存吗？您不想占用太多内存，而 SQL Server 应该将其用于查询。
每个程序集所有者的每个数据库都有一个AppDomain（即程序集上的
AUTHORIZATION）。这意味着任何特定程序集中的代码在所有 SQL Server 会话（即 SPID）之间共享。如果数据只是查找数据，不会根据与静态字段交互的进程而改变，那么这很好。但是，如果每个进程的数据不同，那么这将产生“奇怪”的行为，除非您将当前 TransactionID 之类的值与进程关联起来。如果数据是每个进程的，假设您找到了区分每个特定 SPID / SESSION 的方法，那么您将如何清理旧数据？它将存在于内存中，直到显式删除或卸载 AppDomain。对于旨在与每个人共享的常见查找数据来说，这不是问题，因为该类型的数据不会随着每个新进程而增加。但是每个进程的数据会不断增加，除非清除掉。
AppDomains 可以出于各种原因随时卸载（内存压力、删除/重新创建程序集、与程序集相关的安全更改、与数据库相关的安全更改、运行 DBCC FREESYSTEMCACHE('ALL ') 等）。如果正在缓存的数据可能会导致顺序进程之间出现不同的结果（如果一个进程依赖于前一进程缓存的数据），则不能保证这可以正常工作。如果在进程之间删除缓存仅导致需要重新加载缓存，那么应该没问题。
其他注意事项（但没有什么需要注意的）：
在程序集中调用第一个方法时加载 AppDomain，其中程序集所在的数据库当前没有正在运行的 AppDomain，并且用户是该程序集的授权者程序集。
AppDomains 将保持加载状态，直到它们因上述原因之一被 SQL Server 卸载，但这些情况都不一定会发生。这意味着，AppDomain可以在很长一段时间内保持加载状态（即直到服务器/服务重新启动）。
每个程序集在第一次引用其内部的方法时加载。
为了利用加载事件，您可以将代码放置在静态类构造中。请注意，没有可用的
SqlContext，因此您无法在使用进程内上下文连接的静态类构造函数中创建任何SqlConnection（即>Context Connection = true)。While it is possible to use
staticclass fields in the SQLCLR Trigger class to cache values, there are several things you need to be very cautious about:How much data do you plan on caching? You don't want to take up too much memory that SQL Server should instead be using for queries.
There is a single AppDomain per Database per Assembly Owner (i.e.
AUTHORIZATIONon the Assembly). This means that the code in any particular Assembly is shared across all SQL Server Sessions (i.e. SPIDs). If the data is just lookup data that won't change based on which process is interacting with the static field, then this is fine. But if the data is different per process, then this will produce "odd" behavior unless you associate a value such as the current TransactionID with the process.If the data is per process, assuming you find a way to differentiate each particular SPID / SESSION, how are you going to clean up the old data? It will exist in memory until explicitly removed or the AppDomain is unloaded. This is not a problem for common lookup data that is meant to be shared with everyone as that type of data doesn't increase with each new process. But per-process data will continually increase unless cleared out.
AppDomains can be unloaded at any time and for a variety of reasons (memory pressure, drop/recreate of the Assembly, security change related to the Assembly, security change related to the DB, running
DBCC FREESYSTEMCACHE('ALL'), etc). If the data being cached can cause different outcomes between sequential processes if one process relies upon data cached by a prior process, then this cannot be guaranteed to work. If the cache being dropped between processes results in nothing more than the need to reload the cache, then it should be fine.Other notes (but nothing to be cautious about):
AppDomains are loaded when the first method is called in an Assembly where there is no currently running AppDomain for the Database that the Assembly exists in and the User that is the Authorizer of that Assembly.
AppDomains will remain loaded until they are unloaded by SQL Server for the one of the reasons noted above, but none of those scenarios will necessarily occur. Meaning, the AppDomain can remain loaded for a very long time (i.e. until server / service restart).
Each Assembly is loaded the first time a method inside of it is referenced.
In order to make use of the loading event, you can place code in the static class construct. Just be aware that there is no
SqlContextavailable, so you can't make anySqlConnections in a static class constructor that use the in-process Context Connection (i.e.Context Connection = true).