CODEWITHSUNDEEP
sqlsql-serverperformancesql-server-2008ssis
user1049008
user1049008

Reputation: 61

Performance tuning an update statement in SQL SERVER

I have 2 update statements. when executed both together is taking more than 12hrs from SSIS. All the indexes on this tables were disabled before executing this update statements. I need to improve the performance. I need suggestions:

1) First update statement

Update Db1.table1
Set Db1.table1.col1 = Db2.table2.col1
from Db1.table1, Db2.table2
where Db1.table1.col2 = Db2.table2.col2

2) Second update statement

update table1
set table1.col3 = 0
from table1
where table1.col3 is null

Can update in batches help improving the performance of 1st update statement? I see having a default on col3 is sufficient instead of running the second update. But I am not sure if it effects insert queries. The table is having a lot of data. I am not sure of altering the table to include default value on a column where table is having a lot of data.

Please provide your suggestions to performance tune the above statements. PLease also note that I am not having proper permissions on the DB to verify the execution plan.

Upvotes: 1

Views: 8617

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

First, it would help to know which update is taking longer. But, indexes are not necessarily your enemy when doing updates.

The first update:

Update t1
    Set t1.col1 = t2.col2
    from Db1.table1 t1 join
         Db2.table2 t2
         on t1.col2 = t2.col2;

This update really needs an index on db2.table2(col2). Otherwise, it will need to do a nested loop join.

The second update:

update table1
set table1.col3 = 0
from table1
where table1.col3 is null

is a bit trickier. You are updating the column in the where clause. My sense is that if relatively few of the values are null -- up to a few percent -- then an index on table1(col3) would help. If many of the columns are null, then an index would be less useful. Without an index, this requires a full table scan and should not be absurdly slow.

You may find that batching the updates on this table would help performance.

Upvotes: 2

user4622594
user4622594

Reputation:

When running the 1st Statement you should:

  • have a clustered index on table1.col2
  • have a clustered index on table2.col2

Be ware: you can only have 1 clustered index per table (because this index says how to physically store the data) and it HAS TO BE THE FIRST INDEX created on a table (vice-versa: the last one you delete)

coorect order of actions:

  1. create table
  2. create clustered index (unique or not, doesn't matter)
  3. create nonclustred indexes

respectively:

  1. delete nonclustered indexes
  2. delete clustered index
  3. drop table

Upvotes: 2

Related Questions