TSQL 与 TEMPDB 中的其他表更新大型表会导致巨大的增长
我有一个自定义导入工具,可以将数据批量插入到 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
看起来您正在明确使用 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.