Different ways to speed up an update statement?

Question

Hello. I'm trying to figure out an outside the box way to speed up the update statement at the bottom of this. It's in a warehouse type of DB and runs several times a day. In non-Prod I've brought it down to 6 minutes (from 8) with some good indexing, but was hoping for something more clever that will get it down further or even eliminate the need for it along the lines of persisted computed column , indexed view, filtered index, etc but keep running in to one problem or the other, depending on what exactly Im attempting.

Any ideas?

update a
set a.amrepidorig=a.amrepid
from db.schema.table a with (nolock)
	join db.schema.table b with (nolock)
		on a.id=b.id and b.isOriginal=1
where isnull(a.amrepidorig,1)<>b.amrepid and b.amrepid is not null

Any ideas?

update a
set a.amrepidorig=a.amrepid
from db.schema.table a with (nolock)
	join db.schema.table b with (nolock)
		on a.id=b.id and b.isOriginal=1
where isnull(a.amrepidorig,1)<>b.amrepid and b.amrepid is not null

Answer

Hi @chrisrdba

Some tips:

Be careful with the query hints. Sometimes they may cause more unexpected side effects.
Avoid using functions on indexed column. Functions or expressions on indexed columns can prevent the use of indexes.
Check if it is the slow disk I/O issue.
Analyze the execution plan for more details.

Best regards,

Cosmog

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Share via

Different ways to speed up an update statement?

1 answer

Your answer