Nested loops causing slow performance?

Jason 2014-10-01 19:20:27

This query runs for a very long time (hours) and I think it is becase of the nested loops. Is there a way to write the query to force a more efficient join method?

I have updated the statistics of all the tables but the plan comes out the same.

Server configs:
CPU: 8 Sockets, 32 Virtual Processors
Windows Server 2012
SQL Server 2012 (11.0.5058)
Memory: 196 GB
Ample high speed storage
MAXDOP = 8

Row Counts:
Table 1: 15,925,580
Table 2: 10
Table 3: 154,778,966
Table 4: 73
Table 5: 20,202,279

Jason 2014-10-01 19:57:31
Reading further into this issue, I saw some have had success with using the "OPTION (FORCE ORDER)". Attached is the plan with this option active, it changed the plan completely and the update finished (with 0 rows updated but that is to be expected sometimes) in about five minutes.