T-SQL:双触发触发器、轮询还是 .NET 异步线程?
所以我敢打赌我让你对这个标题感到好奇。 =)
这是场景 - 简化原因。
Website: ASP.NET 4 Web Forms.
Database: SQL Server 2008.
DAL: LINQ-SQL.
在数据库中,我们有两个表:Foo
和 Bar
。
关系为 1-1
,并且 Bar
对 Foo
有外键约束。换句话说,您需要先创建Foo
,然后在创建Bar
时使用Foo
的PK。这两个表连接起来创建一个名为 FooBar
的业务实体。
现在,我们的网站有了一个评分系统。某些操作会导致需要刷新特定 FooBar
的评分。
目前,我们在 Foo
和 Bar
表上都有触发器。两个触发器都会调用一个存储过程来更新特定 FooBar
的分数。
所以问题就在这里——我们在网站上创建了一个新的FooBar
,这导致Foo
和Bar
中的条目被创建。两个触发器都被触发(首先是Foo
,然后是Bar
),并且相同的存储过程被执行两次。不会造成任何实际伤害,只是不利于性能。
我们怎样才能解决这个问题?
更新 FooBar
分数的存储过程很简单 - 接受 FooID
。
在 Bar
中的条目创建之后我们才能执行评分。尽管目前 Foo
触发器确实发生了这种情况,但在 Bar
触发器上重做之前,分数结果是不正确的。另外需要注意的是,在网站上执行某些操作可能不会影响两个表(即您可以在网站上编辑 FooBar
,但最终只会修改 Foo
表,或仅 Bar
表,其他时候两者都有)。
触发器是AFTER INSERT、UPDATE 或DELETE
。
这就是我对三种可能情况的意思:
用户创建一个新的 FooBar:
- 向 Foo 添加了新条目
- 在 Foo 上执行触发器
- 执行 SP 评分
- FooBar 得分不正确(因为还没有 Bar)
- 新条目已添加至栏
- 在柱上执行触发器
- 执行 SP 评分
- FooBar 得分正确
用户编辑 FooBar 的“Foo”:
- Foo 条目已更新。
- 在 Foo 上执行触发器
- 执行 SP 评分
- FooBar 得分正确。
用户编辑 FooBar 的“Bar”:
- 栏目已更新。
- 在柱上执行触发器
- 执行 SP 评分
- FooBar 得分正确。
所以场景 2 和 3 都很好 - 当添加新的 FooBar 时会导致问题。
到目前为止,我的想法是:
从
Foo
和Bar
中删除触发器。在成功创建Foo
和Bar
表条目后(即在LINQ
操作成功后),在 C# 中创建异步线程调用,调用存储过程。问题首先是,除非绝对需要,否则我总是尝试避免在网站中使用线程,此外,它添加了对 Web 服务器的依赖项,而实际上所有这些逻辑和所有权都应该(并且当前)位于数据库上。创建一个“临时”表,触发器将在临时表中创建一个条目(例如“FooID1”、“ReadyToScore”)。然后,SQL 作业将轮询该表(例如每 30 秒一次)并执行存储过程。明显的问题是由于轮询而产生额外负载,并且调试起来更加困难。
我知道这个问题很难理解,所以希望我能解释清楚。
有什么想法吗?
So i bet i got you curious with that title. =)
Here's the scenario - simplified for the cause.
Website: ASP.NET 4 Web Forms.
Database: SQL Server 2008.
DAL: LINQ-SQL.
In the DB, we have two tables: Foo
and Bar
.
Relationship is 1-1
, and Bar
has a foreign key constraint to Foo
. In other words, you need to create Foo
first, then use the PK for Foo
when creating Bar
. The two tables join to create a business entity called FooBar
.
Now, we have a scoring system in place for our website. Certain actions cause the need for scoring to be refreshed for particular FooBar
's.
At the moment, we have triggers on both Foo
and Bar
tables. Both triggers calls a stored procedure which updates the scores for the particular FooBar
.
So therein lies the problem - we create a new FooBar
on the website, which causes entries in Foo
and Bar
to be created. Both triggers get fired (Foo
first, then Bar
), and the same stored procedure is executed twice. Doesnt do any actual harm, just not good for performance.
How can we get around this?
The stored procedure that updates the scores for FooBar
's is simple - takes in a FooID
.
We cannot perform the scoring until after the entry in Bar
is created. Even though it currently does happen for the Foo
trigger, the score turns out to be incorrect until its redone on the Bar
trigger. Also a thing to note is that doing something on the website may not affect BOTH tables (ie you can edit a FooBar
on the website and only end up modifying the Foo
table, or only the Bar
table, other times both).
The triggers are AFTER INSERT, UPDATE or DELETE
.
Here's what i mean for three possible scenarios:
User creates a new FooBar:
- New entry added to Foo
- Trigger executed on Foo
- Scoring SP Executed
- FooBar scored incorrectly (because no Bar yet)
- New entry added to Bar
- Trigger executed on Bar
- Scoring SP Executed
- FooBar scored correctly
User edits the "Foo" for a FooBar:
- Foo entry updated.
- Trigger executed on Foo
- Scoring SP Executed
- FooBar scored correctly.
User edits the "Bar" for a FooBar:
- Bar entry updated.
- Trigger executed on Bar
- Scoring SP Executed
- FooBar scored correctly.
So scenarios 2 and 3 are fine - its when adding new FooBar's that causes the issue.
Here's my ideas so far:
Remove triggers from
Foo
andBar
. Create Asynchronous thread call in C# post-success creation ofFoo
andBar
table entries (ie after success ofLINQ
operations), calling stored procedure. Problem with this is firstly i always try to avoid threading in web sites unless absolutely required, furthermore its adding a dependency to the web server when in fact all of this logic and ownership should be (and currently is) on the database.Create a "staging" table, the triggers would create an entry in the staging table (something like
"FooID1", "ReadyToScore"
- for example). Then a SQL Job would poll this table (say every 30 seconds) and execute the stored procedure. Obvious problems with this is extra load due to the polling, and more difficulty to debug.
Tough question to understand i know, so hopefully ive explained it ok.
Any thoughts?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
您还可以考虑异步 T-SQL 过程,它消除了需要池化作业,甚至需要临时表。
You may also consider Asynchronous T-SQL Procedures, which eliminate the need for pooling job(s) and even for staging tables.
决定保持原样(进行两次处理)。
理由:
- 处理本身非常高效(< 1 秒)。
- 轮询、线程、异步 T-SQL VS 两个 1 秒调用。无需多想。
Decided to keep it as it is (doing processing twice).
Reasons:
- The processing itself is quite efficient (< 1 sec).
- Polling, threading, Async T-SQL VS two 1 second calls. No brainer there.