CLR存储程序中的多线程代码？ [英] Multi-threaded code in CLR Stored Procs?

问题描述

多线程CLR存储过程是否可能？

Are multi-threaded CLR stored procs possible?

我有一个数据密集型任务，有很多并行化的潜力。 CLR存储Procs将是伟大的，以消除将数据移出进程的开销，我恐怕我不得不放弃并行计算。

I have a data-intensive task with lots of potential for parallelization. CLR Stored Procs would be great to remove the overhead of moving the data out of process, my I fear that I'd have to give up parallel computing.

我的选项是什么？

注意：我们使用的是SQL Server 2005， 4个月的范围升级到SQL Server 2008

Note: We're on SQL Server 2005 with plans in the < 4 month range to upgrade to SQL Server 2008

推荐答案

如果是潜在的并行化的您应该以 set 为导向的方式处理它，并让SQL根据其看上去合并processign。对于每个CPU的数据访问分区，它只能访问您不需要的信息（缓冲池填充状态，页面生命期望，CPU / NUMA亲和性等）。

If is data intensive with potential to parallelization, you should process it in set oriented manner and let SQL paralelize the processign as it sees fit. You will not be able to do anything smarter than SQL already does in regard to partition the data access per CPU, it simply has access to information you don't (buffer pool fill state, page lifetime expectancy, CPU/NUMA affinity etC).

如果您的处理是面向标量和CPU密集型（甚至是面向集合的处理），请将处理放置在UDF CLR函数中，让查询执行并行化函数执行。

If your processing is scalar oriented and CPU intensive (and even some set oriented processing), place the processing in an UDF CLR function and again, let the query execution paralelize your function execution.

如果你的处理有任何I / O（即网络调用），不要

If your processing has any sort of I/O (ie. web calls), do not put it in SQL, place outside the server process.

如果您的处理真的不属于所有这些类别，并且您仍然相信您可以从多线程中获益，理论上是可能的在SQL中启动线程。请注意，SQL中的CLR主机不是您的正常CLR主机（即知名的应用程序主机或ASP主机）。 SQL CLR是第三种主机类型，提供自己的原语（线程，锁定，内存管理等）分层在 SOS 构造（工人，锁存器，内存管理员等）。我强烈建议不要在SQL中进行显式的多线程CLR处理。

If your processing really falls outside all these categories and you still believe you could benefit from multithreading, in theory is possible to start threads inside SQL. Be warned that the CLR host inside SQL is not your normal CLR host (ie. the well known application host or the ASP host). SQL CLR is a third host type, offering its own primitives (threads, locking, memory management etc) layered on top of SOS constructs (workers, latches, memory clerks etc). I would strongly advise against doing explicit multi-threaded CLR processing in SQL.

这篇关于CLR存储程序中的多线程代码？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！