DTS包内更新语句的性能
我有一个 DTS 包，每天运行几年没有任何问题后，已经开始运行了。最初，它是将数据插入到表中，然后触发插入触发器。触发器使用 [inserted] 更新表中的三列。通常，它会更新大约 500,000 行插入的数据。
当问题开始时，触发器内的更新语句需要花费数小时才能运行，并且通常必须取消。禁用触发器允许 DTS 正常运行。在常规查询窗口中运行 UPDATE 语句不会导致任何问题 - 它运行时间不到 10 秒。
为了解决这个问题，DTS 中添加了另一个任务来在 INSERT 之后运行 UPDATE 来代替触发器。这在一段时间内工作正常，但单独的 UPDATE 任务现在开始表现出与触发器相同的问题。如果在常规查询窗口中完成，更新仍按预期运行。
DTS 包是在 SQL 2000 中创建的，并在 SQL 2005 Enterprise x64 上运行。
我有点不知所措地尝试解决这个问题。有什么想法吗？
I have a DTS package, which after running daily with no problems for a couple of years, has started to play up. Originally it was inserting data into a table which would then fire an insert trigger. The trigger used [inserted] to update three columns in the table. Usually, it was updating about 500,000 rows of inserted data.
When the problem started, the update statement inside the trigger was taking hours to run and usually had to be cancelled. Disabling the trigger allowed the DTS to run as normal. Running the UPDATE statement in a regular query window didn't cause any problems - it ran in less than 10 seconds.
To get around the problem, another task was added to the DTS to run the UPDATE after the INSERT in place of the trigger. This worked OK for a while, but the separate UPDATE task has now started to exhibit the same problem that the trigger had. The UPDATE still runs as expected if done in a regular query window.
The DTS package was created in SQL 2000 and is running on SQL 2005 Enterprise x64.
I'm at a bit of a loss to try and figure this one out. Any ideas?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
SQL 引擎本身很好地涵盖了查询 (SELECT) 的并行化，但是当涉及大量数据修改（UPDATE、INSERT、DELETE）时，标准引擎确实会并行化以最佳利用所有可用资源（磁盘、多个 CPU） -核心等）。
因此，您可以在以下位置查看 SQL Parallel Boost 的方法：
http://www.ibax.ch /-IBX-/plain.simpleimagetitletextlinklist.en.solutions.products.parallelboost/default.aspx
此方法还可用于并行执行多个 SQL 语句。
纯粹与 SQL 引擎相关的并行化解决方案利用了最小化的复杂性，并且不涉及 SSIS 等“外部”组件。此外，它是任务拆分和同步方面性能最佳的解决方案，因为它没有潜在的连接和通信开销。通过 SQL Parallel Boost 并行化，整体性能提升高达 10！
如果您不想重建自己的解决方案，SQL Parallel Boost 提供了一个独立的纯基于 T-SQL 的解决方案，可以轻松嵌入到现有应用程序和 ETL 流程任务中。
Parallelization for queries (SELECT) is covered quite well by the SQL engine itself, but when it comes to large volume data modifications (UPDATE, INSERT, DELETE), the standard engine does parallelize towards best use of all available resources (disk, multiple cpu-cores, etc.).
Therefore you may have a look into the approach of SQL Parallel Boost at
http://www.ibax.ch/-IBX-/plain.simpleimagetitletextlinklist.en.solutions.products.parallelboost/default.aspx
This approach can also be used to execute multiple SQL statements in parallel.
A purely SQL engine related parallelisation solution takes advantage of minimized complexity and has no 'external' components like SSIS involved, Furthermore it's the best performing solution regarding task splitting and synchronization, as it hasn't potential connection and communication overhead. The overall performance gain thru parallelisation with SQL Parallel Boost is up to 10 !
In case you don't wan't to rebuild your own solution, SQL Parallel Boost provides a self-contained pure T-SQL based solution, which can be easily embedded in existing applications and ETL process tasks.
您是否尝试过查看 UPDATE 语句的查询计划？您可以在查询分析器/ssms 中执行此操作。
该表有哪些索引？也许他们必须进行维护并减慢更新速度。
您能详细介绍一下更新吗？是一条 SQL 语句还是您使用的是 CURSOR？
将整个过程转换为 SSIS 很可能会提高性能，但这取决于您在 DTS 中实际执行的操作。
Have you tried looking at the query plan for the UPDATE statement? You can do that in query analyzer/ssms.
What indexes does the table have on it? Maybe they are having to be maintained and are slowing the update down.
Can you go into detail on the UPDATE? Is it one SQL statement or are you using a CURSOR?
Converting the whole thing to SSIS would most likely add performance, but that depends on what you're actually doing in DTS.