CODEWITHSUNDEEP
sqlsql-server-2008t-sqlindexing
Emman Bangis
Emman Bangis

Reputation: 85

Does index can affect update?

I have a table with one million rows. I am trying to update a specific column based on the value from the other column.

update m
set repname = e.LastName + ', ' + e.FirstName, 
    empNo = i.empNo,
    DateHired = e.DateHired, 
    DateTerminated = e.DateTerminated,  
    TeamCode = e.TeamCode
from 
    nyox m
inner join 
    nyerk e on m.orderSplitRepNumber = e.EmployeeNumber
inner join 
    choks i on  (m.orderSplitRepNumber) = i.full_empNo

Would it be faster if I create index on nyox on the column with a relationship on other table?

CREATE Nonclustered INDEX ix_orderSplitRepNumber
ON nyox (orderSplitRepNumber)

Upvotes: 0

Views: 55

Answers (2)

paparazzo
paparazzo

Reputation: 45096

test with this
clearly indexes on joins will help
but locks and set are going to dominate that update

select m.orderSplitRepNumber, 
       e.LastName,
       e.FirstName, 
       i.empNo,
       e.DateHired, 
       e.DateTerminated,  
       e.TeamCode
  from nyox m
  join nyerk e 
    on m.orderSplitRepNumber = e.EmployeeNumber
  join choks i 
    on m.orderSplitRepNumber = i.full_empNo

Is there a chance it already has the values?
Taking the write lock is the expensive part.
If you use the top clause then you will need to loop but at 1 million you may fill up a transaction log.
with (nolock) is optional and only if you know it is safe 

update top (10000) m
   set m.repname = e.LastName + ', ' + e.FirstName, 
       m.empNo = i.empNo,
       m.DateHired = e.DateHired, 
       m.DateTerminated = e.DateTerminated,  
       m.TeamCode = e.TeamCode
  from nyox m
  join nyerk e with (nolock)
    on m.orderSplitRepNumber = e.EmployeeNumber
  join choks i with (nolock) 
    on m.orderSplitRepNumber = i.full_empNo
 where m.repname <> e.LastName + ', ' + e.FirstName, 
   and m.empNo <> i.empNo,
   and m.DateHired <> e.DateHired, 
   and m.DateTerminated <> e.DateTerminated,  
   and m.TeamCode <> e.TeamCode

Upvotes: 0

D Stanley
D Stanley

Reputation: 152566

Would it be faster if I create index on nyox on the column with a relationship on other table?

It depends on how many row's don't have matching rows in the related tables. If most rows have related records, the optimizer may decide that a table scan would perform just as well, so an index wouldn't make any difference.

Indexes on nyerk.EmployeeNumber and choks.full_empNo, however, would likely make the update faster.

Upvotes: 1

Related Questions