How to speed up this query?
When table1 has 110 million rows, this took about 24 hours to finish! Can someone please help speeding this up? Thanks.
The uploaded plan shows the query ran for 67.232 seconds, performed relatively little I/O, and used hardly any CPU. From the information given, it seems most likely the 24 hour execution was simply blocked by another transaction.
As Paul (SQLKiwi) mentioned, there's not actually a lot of work going on here. There is a decent amount of time spent on PAGEIOLATCH_EX, that is buffer cache IO operations of an exclusionary nature. So it looks like the query is spending a lot of time waiting to get update locks on rows/pages that are already locked by other processes. (I also noticed a TOP operator. Why would you want a TOP operation on an Update?)
There are also a few index statistics that are out of date.
Aside from those clues, I can't tell you much without the query text.
Hope that helps,