title: UPDATE statement under a transaction running indefinitely
body: <p>I am facing issue with ever-running query in one stored procedure (using SQL Server 2005). </p>
  
  <p>In this stored procedure there are many UPDATE statements and few INSERT statements running under one Transaction.  </p>
  
  <p>When debugging the stored procedure, I found that there is one specific UPDATE query which is running indefinitely as shown below.   </p>
  
  <pre><code>BEGIN TRAN  
  ...   
  ...-- some INSERT &amp; UPDATE statements   
  
   UPDATE PIH
          SET PIH.RemittanceHeaderID = RD.RemittanceHeaderID
          FROM TempPayroll TP 
              INNER JOIN RemittanceDetails RD  ON TP.UniqueID = RD.PLInvoiceDetailID 
              INNER JOIN PLInvoiceHeader PIH  ON RD.PLInvoiceUniqueID = PIH.InvoiceUniqueID AND PIH.CompanyCode = TP.CompanyCode
          WHERE TP.PLSelfBill = 1 
              AND TP.UsePLPaymentTerms = 1
              AND RD.PLInvoiceDetailID IS NOT NULL
              AND TP.RecordType LIKE 'INVOICE%'
              AND RD.RecordType LIKE 'INVOICE%'  
    ... --some UPDATE statements
  COMMIT TRAN
  </code></pre>
  
  <p>However, when I remove the <code>CompanyCode</code> condition the stored procedure is working fast with no issues. Also if I execute just this statement alone under a transaction separately it is running fine.  </p>
  
  <p>There are around 240 matching records for this query. Each table contains just 30k - 50k records.  </p>
  
  <p>How do I debug the query and how to fix it?  </p>
  
  <p>Why just that JOIN condition (<code>CompanyCode</code>) is creating this issue?  </p>
  
  <p>Thanks in advance. </p>