If i have a stored procedure or a trigger in Sql Server 2008, can it do some sql calculations 'in another non-blocking thread'? ie. something in the background
如果我在Sql Server 2008中有一个存储过程或触发器,它可以在另一个非阻塞线程中进行一些sql计算吗?即。在背景中的东西
also, can two sql code blocks be ran in parallel? or two stored procs be ran in parallel?
还有,两个sql代码块可以并行运行吗?或两个存储过程并行运行?
for example. Imagine we are given the job calculating the scores for each Stack Overflow user (and please leave all 'do that elsehwere/service/batch/overnight/etc, elswhere) after a user does some 'action'.
例如。想象一下,我们得到了计算每个Stack Overflow用户的分数的工作(并且在用户做了一些“动作”之后,请留下所有'做那个其他/服务/批/过夜/等,elswhere)。
so we have a trigger on the Post table, so when a new post is INSERTED, the trigger fires off and part of that logic, it calculates the user's latest score. Instead of waiting for the stored proc to finish and block the current sql thread / executire, can we ask it to calc the score in the background OR parallel.
所以我们在Post表上有一个触发器,所以当一个新帖子被INSERTED时,触发器触发并且该逻辑的一部分,它会计算用户的最新分数。我们可以要求它在后台或并行计算得分,而不是等待存储的proc完成并阻止当前的sql thread / executire。
cheers!
2 个解决方案
#1
2
SQL Server does not have parallel or deferred execution: each block of running code in a connection is serial, one line after the other.
SQL Server没有并行或延迟执行:连接中的每个运行代码块都是串行的,一行接一行。
To decouple processing, you usually have to use SQL Server Agent jobs or use Service broker. These start executing in a new connection, new session etc
要解耦处理,通常必须使用SQL Server代理作业或使用服务代理。这些开始在新连接,新会话等中执行
This makes sense:
这是有道理的:
- What if you want to rollback your changes? What does the background thread do and how does it know?
- What data does it use? New, Old, lock wait, snapshot?
- What if it gets ahead of the main thread and uses stale data?
如果要回滚更改怎么办?后台线程做了什么以及它如何知道?
它使用了什么数据?新旧,锁定等待,快照?
如果它超前于主线程并使用陈旧数据会怎么样?
#2
1
No, but you could write the request to a queue. Service Broker, a SQL Server component, provides support for this kind of thing. It's probably the best option available for asynchronous processing.
不,但您可以将请求写入队列。 Service Broker是一个SQL Server组件,它为这类东西提供支持。它可能是异步处理的最佳选择。
#1
2
SQL Server does not have parallel or deferred execution: each block of running code in a connection is serial, one line after the other.
SQL Server没有并行或延迟执行:连接中的每个运行代码块都是串行的,一行接一行。
To decouple processing, you usually have to use SQL Server Agent jobs or use Service broker. These start executing in a new connection, new session etc
要解耦处理,通常必须使用SQL Server代理作业或使用服务代理。这些开始在新连接,新会话等中执行
This makes sense:
这是有道理的:
- What if you want to rollback your changes? What does the background thread do and how does it know?
- What data does it use? New, Old, lock wait, snapshot?
- What if it gets ahead of the main thread and uses stale data?
如果要回滚更改怎么办?后台线程做了什么以及它如何知道?
它使用了什么数据?新旧,锁定等待,快照?
如果它超前于主线程并使用陈旧数据会怎么样?
#2
1
No, but you could write the request to a queue. Service Broker, a SQL Server component, provides support for this kind of thing. It's probably the best option available for asynchronous processing.
不,但您可以将请求写入队列。 Service Broker是一个SQL Server组件,它为这类东西提供支持。它可能是异步处理的最佳选择。