title: Update Query with NVARCHAR(max) in SQL Server
body: <p>Have an issue with SQL Server performance and wanted to see if anyone can give some tips about improving the performance of an update query. </p>
  
  <p>What I'm doing is updating one table with data from another table. Here's some of the basics:</p>
  
  <ul>
  <li>SQL Server 2008 R2</li>
  <li>Data is pumped to <code>WO</code> table originally from other system (pumped in using datareader and sqlbulkcopy in ADO.NET)</li>
  <li>Additional data is pumped to <code>TEMP_REMARKS</code> (pumped in using datareader and sqlbulkcopy in ADO.NET)</li>
  <li>Unfortunately, combining the <code>WO</code> and <code>REMARKS</code> in the originating system (via the reader query) is not possible (mainly performance reasons)</li>
  <li>Update to <code>WO</code> occurs using value from <code>TEMP_REMARKS</code> where two columns are updated</li>
  <li>Note that the column being transferred from <code>TEMP_REMARKS</code> to <code>REMARKS</code> is a <code>nvarchar(max)</code> and is being placed into another <code>nvarchar(max)</code> column (actually two - see query)</li>
  <li><code>WO</code> has 4m+ records</li>
  <li><code>TEMP_REMARKS</code> has 7m+ records</li>
  </ul>
  
  <p>For the join between the two, the following is what is being used: </p>
  
  <pre><code>/* === UPDATE THE DESCRIPTION  */     
  UPDATE WO
  SET WO_DESCRIPTION = TEMP_REMARKS.REMARKS
  FROM WO 
  INNER JOIN TEMP_REMARKS ON WO.WO_DESCRIPTION_ID = TEMP_REMARKS.REMARKS_ID;
  
  /* === UPDATE THE FINDINGS  */
  UPDATE WO
  SET FINDINGS = TEMP_REMARKS.REMARKS
  FROM WO 
  INNER JOIN TEMP_REMARKS ON WO.FINDINGS_ID = TEMP_REMARKS.REMARKS_ID;
  </code></pre>
  
  <p>The problem at this point is that the update to the <code>WO</code> table is taking over two hours to complete. I've tried using the <code>MERGE</code> statement with no success. I've got other more completed procedures in the db that don't take nearly as long, so I'm convinced that it is not the configuration of the SQL Server itself. </p>
  
  <p>Is there something that should be done when updating <code>nvarchar(max)</code> columns?  </p>
  
  <p>What can be done to improve the performance of this query?  </p>
  
  <p>Here are the table definitions:</p>
  
  <pre><code>CREATE TABLE [dbo].[WO](
      [DOCUMENT_ID] [decimal](18, 0) NOT NULL,
      [WO_DESCRIPTION_ID] [decimal](18, 0) NULL,
      [WO_DESCRIPTION] [nvarchar](max) NULL,
      [FINDINGS_ID] [decimal](18, 0) NULL,
      [FINDINGS] [nvarchar](max) NULL,
  .... bunch of other fields
   CONSTRAINT [PK_WO] PRIMARY KEY CLUSTERED 
  (
      [DOCUMENT_ID] ASC
  )
  </code></pre>
  
  <p>This is the table definition for the <code>TEMP_REMARKS</code>:</p>
  
  <pre><code>CREATE TABLE [dbo].[TEMP_REMARKS](
      [REMARKS_ID] [decimal](18, 0) NOT NULL,
      [REMARKS] [nvarchar](max) NULL
  ) ON [PRIMARY]
  </code></pre>