What alternative ideas are their when you have 9 update statements to a table variable?

Question

What alternative ideas are their when you have 9 update statements to a table variable?

John-250225

Mr or Mrs. 500

Points: 588
More actions
June 21, 2006 at 1:18 pm

#112677

What can i do to increase performance (reducing tempdb locks specifically) to a sproc that has 9 update statements to the same table variable with different where clauses. Is this a design flaw in the table schema? Any thoughts would help appreciate it.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

Replace the table variable with a temp table.

Create the table *outside* of any transactions, and create it via CREATE TABLE, or via SELECT INTO ... WHERE 0 = 1 so that the creation and the locks on system tables are almost instantaneous.

Index the temp table on the column or columns that are used in the subsequent update joins, so that the 9 updates don't tablescan and run faster.

If any of the columns in the table start out NULL, and later get updated to a value, consider initially populating them with a non-Null "placeholder" value.

This will allow your updates to run as UPDATE-IN-PLACE operations, instead of a behind the scenes DELETE-REINSERT. Doing this cuts the number of log writes in half.

John-250225 Mr or Mrs. 500 Points: 588 More actions · Answer 2

but isn't index going to slow down the updates cause with indexs you have to update not only the table but the indexs when an update occurs? I just remember that with indexs u have to be careful that it does increase retrieval of data but slows down inserts, updates and deletes cause u have to update the indexs as well. Let me know appreciate it.

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 3

I'm wondering if this is a spoof posting as I'm sure this is maybe the third time I've seen the same question posted in different ways. There are some excellent training courses available on T-SQL as well as a number of excellent publications.

Indexes are critical for update selection although you can only define a PK on a table variable and to be honest it rarely has much performance impact.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

The indexes will speed up the update because they greatly increase the speed of finding the records to update. The time taken to update the indexes is minimal.

How many records are in this temp table? Table variables can perfrom quite badly with lots of rows since statistics are not maintained on the table and hence the optimiser has no idea how many rows are in the table.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

stax68 SSChampion Points: 11711 More actions · Answer 5

You'll need to post code to get more specific suggestions.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

John-250225 Mr or Mrs. 500 Points: 588 More actions · Answer 6

I put 2 nonclustered indexs on the two most heavily joined columns on the temp table and it reduced the tempdb locks down to a bare minimum! But, when running ostress it now takes 20 minutes to complete when before it took only 2 minutes. I am using -n100 -r30 (100 threads at 30 times) for the ostress switches. Is this normal?