TSQL updating large table with other from TEMPDB causes enormous grow

Question

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

What grows to 21GB? The data file or the log file? What does the query plan look like? Are you using snapshot isolation on `theDB`? — Martin Smith, May 03 '11 at 15:55
The tempdb is growing 21db. The queryplan I will investigate just now. Also I'm trying out to turn of snapshot isolation for `the theDB` since the whole process is kind of like fire-and-forget. Thanks! — Casper Broeren, May 04 '11 at 07:06
The queryplan doesn't show any abnormal plans or optimize hints. Any thing I need to check for? Also the rewriten update statement with a join a where in doesn't have any good improvements — Casper Broeren, May 04 '11 at 09:00

score 0 · Answer 1 · answered May 03 '11 at 15:39

0

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.

answered May 03 '11 at 15:39

chuchu

1

The OP is talking about `tempdb`. Even assuming they are talking about logs rather than data file that is always in simple recovery model. – Martin Smith May 03 '11 at 15:41
That is correct. The temp and the target database have both simple backup plans. – Casper Broeren May 03 '11 at 15:50

p.campbell · Accepted Answer · 2011-05-03T15:46:34.243

0

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.

edited May 03 '11 at 15:46

answered May 03 '11 at 15:39

p.campbell

98,673
67
256
322

The reason for the tempdb is because we needed to insert the data in a lightweight table which always can dropped or revoked. The actual data which is new or update is put in the actual database table. After import completes the tempdb is shrinked again. We can't do such things with the target database (or with out hitting performance) – Casper Broeren May 03 '11 at 15:54

score 0 · Answer 3 · answered May 03 '11 at 15:53

0

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

answered May 03 '11 at 15:53

Tim

5,371
3
32
41

No. `update targetTable set ... from [tempDB].[dbo].[targettable] as temp where theDB.dbo.targetTable.hash=temp.hash COLLATE SQL_Latin1_General_CP1_CI_AS` is the same as an `inner join on theDB.dbo.targetTable.hash=temp.hash COLLATE ...` – Martin Smith May 03 '11 at 15:56

TSQL updating large table with other from TEMPDB causes enormous grow

3 Answers3