How to speed up simple UPDATE query with millions of rows?
<p>How can I speed up this rather simple UPDATE query?  It's been running for over 5 hours!</p>

<p>I'm basically replacing SourceID in a table by joining on a new table that houses the Old and New IDs.  All these fields are VARCHAR(72) and must stay that way.</p>

<p>Pub_ArticleFaculty table has 8,354,474 rows (8.3 million).  ArticleAuthorOldNew has 99,326,472 rows (99.3  million) and only the 2 fields you see below.</p>

<p>There are separate non-clustered indexes on all these fields.  Is there a better way to write this query to make it run faster?</p>

<pre><code>UPDATE PF
        SET PF.SourceId = AAON.NewSourceId
    FROM AA..Pub_ArticleFaculty PF WITH (NOLOCK)
        INNER JOIN AA2..ArticleAuthorOldNew AAON WITH (NOLOCK)  
                   ON AAON.OldFullSourceId = PF.SourceId
</code></pre>