SQL Server中非常大的表的更新或合并
我需要执行非常大(300M记录)和广泛TABLE1
的每日更新。更新的源数据位于另一个表UTABLE
中,即TABLE1
行的10%-25%,但较窄。这两个表都有record_id
作为主键。SQL Server中非常大的表的更新或合并
目前,我使用下面的方法重建TABLE1
:
<!-- language: sql -->
1) SELECT (required columns) INTO TMP_TABLE1
FROM TABLE1 T join UTABLE U on T.record_id=U.record_id
2) DROP TABLE TABLE1
3) sp_rename 'TMP_TABLE1', 'TABLE1'
但是这需要我的服务器(的RAM为SQL Server 60GB)在将近40分钟。我想实现50%的性能提升 - 我可以尝试其他选择吗?
-
MERGE
和UPDATE
- 类似下面的代码更快只有一个非常小的UTABLE
表的工作 - 在全尺寸,一切都只是挂起:<!-- language: SQL --> MERGE TABLE1 as target USING UTABLE as source ON target.record_id = source.record_id WHEN MATCHED THEN UPDATE SET Target.columns=source.columns
听说我可以执行批MERGE通过使用ROWCOUNT - 但我不认为它可以足够快的300M行表。
任何SQL查询提示,可以帮助吗?
其实我已经找到了这种查询的一般建议:主意,使用SQL合并或更新是一个非常聪明的一个,但它失败时,我们需要更新一个大又宽多条记录(即75M)表(即240M)。
查看下面查询的查询计划,我们可以说TABLE1的TABLE SCAN
和最终的MERGE
占用了90%的时间。
MERGE TABLE1 as Target
USING UTABLE as source
ON Target.record_id = source.record_id
WHEN MATCHED AND (condition) THEN
UPDATE SET Target.columns=source.columns
所以为了使用MERGE我们需要:
- 减少,我们需要更新和正确地将此信息传递到SQL Server的行数。这可以通过缩小
UTABLE
或指定缩小要合并的部分的其他condition
来完成。 - 确保要合并的零件适合内存,否则查询运行速度会变慢。减少
TABLE1
两次减少了我的真实查询时间从11小时减少到40分钟。
正如Mark提到的,您可以使用UPDATE
语法并使用WHERE
子句来缩小要合并的部分 - 这会得到相同的结果。也请避免索引TABLE1
,因为这会导致在MERGE
首先,我会找出你的瓶颈在哪里 - 你的CPU是挂钩还是闲置?换句话说 - 您的IO子系统能够正确处理负载吗?
重新创建整个表是很多的IO负载,更不用说它会占用大量的空间,基本上有表暂时存储两次。
你需要执行一个合并 - 从我可以看到一个简单的更新应该就足够了。示例:
UPDATE
TABLE1
SET
ColumnX = UTABLE.ColumnX
...
FROM
TABLE1
INNER JOIN
UTABLE ON TABLE1.record_id = UTABLE.record_id
您可以使用ROWCOUNT批量更新但不会加速执行,它只会帮助减少整体锁定。
另外 - 你有什么样的索引在桌子上?在更新之前禁用索引可能会更快,然后从头开始重新构建索引(仅限非聚簇)。
嗨马克,感谢您的回应,我没有索引,因为只会慢MERGE或UPDATE查询。至于基于平均磁盘队列计数器的IO - 磁盘非常繁忙,看起来TABLE1不适合内存。现在我正在尝试使用CTE进行预过滤输入,然后执行MERGE,我会用结果回答我自己的问题。 – 2011-05-15 18:48:31
+1很好的答案。无法理解为什么没有人赞成它呢。 – 2013-05-23 18:45:08
期间重建索引的额外工作您可以发布查询计划吗? – 2011-05-16 08:54:04
嗨@chris,查询计划很简单:** 1 **表扫描TABLE1,** 2 **表扫描UTABLE,** 3 HASH JOIN,** 4 MERGE。第一步和最后一步占用了90%的时间。不过,我已经理解如何解决我的问题 - 请看我自己的答案。 – 2011-05-17 09:59:55