T-SQL，计划作业中的多次更新
我一直在开发一个用作计划作业的查询。简而言之，我要对一个表进行一些具体的计算，并根据计算结果更新一些列（例如 STATE）。
示例（目前的情况）可以演示如下：
UPDATE TEST_TABLE SET STATE = 1
WHERE {some condition}
UPDATE TEST_TABLE SET STATE = 2
WHERE {some condition}
UPDATE TEST_TABLE SET STATE = 3, SOME_OTHER_COLUMN={value}
WHERE {some condition}
WHILE(some condition)
BEGIN
UPDATE TEST_TABLE SET STATE = 4, SOME_OTHER_COLUMN={value}
WHERE {some condition}
END
我上面尝试演示的是查询的流程。从上面可以看出，我多次更新单个表，主要是根据不同的条件设置具有不同值的单个状态列。
我还必须使用 while 循环，而不是游标（因为性能较差），因为需要根据这些条件用小组更新此表中的数据。
假设所有查询都包含在事务和 try-catch 块中。
最后，这是我的问题： 由于这是一项安排在晚上执行的工作，所以性能对我来说并不是首要任务。但是，我无法弄清楚如何使用更干净、更高效（性能方面）的查询来进行相同的操作。我需要一些建议。 请注意，{some condition} 区域包含具有 EXISTS 功能的子查询。所以原来的代码看起来比这要混乱得多。提前致谢。 ——奥赞
I've been developing a query to be used as a scheduled job. To make it short, I'm to make some specific calculations on a table, and update a few columns (like STATE) according to the calculation results.
A sample (as currently how it is) could be demonstrated as follows :
UPDATE TEST_TABLE SET STATE = 1
WHERE {some condition}
UPDATE TEST_TABLE SET STATE = 2
WHERE {some condition}
UPDATE TEST_TABLE SET STATE = 3, SOME_OTHER_COLUMN={value}
WHERE {some condition}
WHILE(some condition)
BEGIN
UPDATE TEST_TABLE SET STATE = 4, SOME_OTHER_COLUMN={value}
WHERE {some condition}
END
What I've tried to demonstrate above is, the flow of the query. As It can be seen above, I'm updating a single table multiple times, mostly setting a single state column with different values depending on different conditions.
I also had to use a while loop, instead of a cursor (because of poor performance), because it was needed to update the data in this table with small groups depending on those conditions.
Assuming all the query is wrapped in a transaction and a try-catch block.
And finally, here's my question :
Since this is gonna be a scheduled job to be executed at night, the performance is not the top priority for me. However, i couldnt figure out how to make the same operation with a somewhat more cleaner and more efficient (performance wise) query. I'm in need of some advices.
Please note that, {some condition} areas hold subqueries with EXISTS functionality. So the original code looks far more messy than this. Thanks in advance.
--Ozan
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果表足够小，那么多次查询不会有什么坏处。
即使查询的
WHERE子句因索引而有效工作的位置相当大，您也可能没问题。您可以合并查询 - 如果
WHERE子句相同，那么您可以将它们放在一起。IMO，代码越容易阅读，就越容易维护。如果您没有获得重大的性能提升，那么整合就没有任何意义。
查询的效率是根据它消耗的资源量来衡量的，而不是它的整合程度或看起来有多复杂。您最好查看执行计划并找出表上所需的任何结构更改或索引。
最引起我注意的是您尝试了 CURSOR ，现在已将其更改为 WHILE 循环。
你会发现基于Set的操作比游标更有利于数据库工作。
根据 OP 评论进行编辑
由于该表预计会很快填满，因此您应该进行负载测试。向表中加载大量测试数据（假设一年的数据），然后查看查询的执行情况。
If the table is small enough, then multiple queries will not hurt.
Even if it is considerably large to where the
WHEREclause of the query works efficiently because of indexes, you may be OK.You could consolidate the queries - if the
WHEREclauses are the same, then you can put those together.IMO, the easier the code is to read, the easier it is to maintain. If you're not getting major performance gains then there's really no point in consolidating.
Efficiency of a query is measured in the amount of resources it consumes, not how nicely consolidated it is or how complex it looks. You may be better off looking at execution plans and figuring out any structural changes or indexes you need on the table.
The biggest thing that catches my eye is that you tried a
CURSORand now have changed it to aWHILEloop.You will find that Set-based operations are much more conducive to database work than cursors.
EDIT based on OPs comment
Since the table is expected to fill up quite fast, you should do a load test. Load the table up with a ton of test data (let's say one years worth) and see how your queries perform.