TSQL 与 TEMPDB 中的其他表更新大型表会导致巨大的增长

发布于 11-04 11:00 字数 771 浏览 8 评论 0原文

我有一个自定义导入工具,可以将数据批量插入到 temp 中(421776 行)。之后,该工具将未知行插入到目标表中,并根据哈希键(2 列的组合)更新现有行。目标数据库具有几乎相同的行数。更新查询看起来像这样(大约少了 20 个更新列)

  update targetTable set 
        theDB.dbo.targetTable.code=temp.code,
        theDB.dbo.targetTable.name=temp.name,
  from [tempDB].[dbo].[targettable] as temp 
  where theDB.dbo.targetTable.hash=temp.hash COLLATE SQL_Latin1_General_CP1_CI_AS

我知道 nvarchar 与排序规则比较有点糟糕,但不容易避免。哈希列仍然有它自己的唯一索引。在本地它也运行良好,但在我的这台服务器上,临时数据库不断增长到 21 GB。重新索引和收缩根本不起作用。

对于其他面临 tempdb 问题的人来说只是一个旁注。很好的读物是 http://bradmcgehee.com/wp-content/uploads/presentations /Optimizing_tempdb_Performance_chicago.pdf

I have a custom import tool which bulk-insert the data in temp (421776 rows). After that the tool inserts unknown rows into the target table and updates existing rows based on a hash key(combination of 2 columns). The target db has nearly the same row count. The update query looks something like this (about 20 less update columns)

  update targetTable set 
        theDB.dbo.targetTable.code=temp.code,
        theDB.dbo.targetTable.name=temp.name,
  from [tempDB].[dbo].[targettable] as temp 
  where theDB.dbo.targetTable.hash=temp.hash COLLATE SQL_Latin1_General_CP1_CI_AS

I know the nvarchar compare with a collate is a bit bad but not easy to avoid. Still the hash column has it's own unique index. Also locally it works well but on this server of mine the temp DB keeps growing to 21 gig. Reindexing and shrinking won't work at all.

Just a side note for others who face tempdb problems. A good read is http://bradmcgehee.com/wp-content/uploads/presentations/Optimizing_tempdb_Performance_chicago.pdf

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

春夜浅2024-11-11 11:00:51

看起来您正在明确使用 tempdb 来处理您放置在那里的数据。是否有理由像使用自己的数据库一样使用 tempdb?

tempdb 增长的原因是您明确地将数据放在那里。 420k 行听起来并不重,但最好将其保留在您自己的用户数据库中。

建议更改您的业务逻辑,从 [tempDB].[dbo].[targettable] 迁移到您自己的用户数据库上的内容。

It looks like you're using tempdb explicitly with data you've put there. Is there are a reason to use tempdb as if it was your own database?

The reason tempdb is growing is because you're explicitly putting data there. 420k rows doesn't sound heavy, but it's best to keep it within your own user db.

Suggest changing your business logic to move away from [tempDB].[dbo].[targettable] to something on your own user database.

倥絔2024-11-11 11:00:51

您可以暂时将事务日志记录从完整或批量记录更改为简单。这将防止所有内容被记录以进行回滚。

You can temporarily change the transaction logging from Full or Bulk logged down to simple. That will keep everything from getting logged for a rollback.

演出会有结束2024-11-11 11:00:51

当没有显式连接时,这是笛卡尔积吗?

Is this a cartesian product when there's no explicit join?

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文