June 21, 2006 at 1:18 pm
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.
June 21, 2006 at 1:59 pm
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.
June 21, 2006 at 6:07 pm
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.
June 22, 2006 at 5:29 am
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/
June 22, 2006 at 8:09 am
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
June 22, 2006 at 12:12 pm
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
June 22, 2006 at 12:51 pm
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?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply