title: SQL IN operator in update query causes a lot of time
body: <p>Below is a update query which is to update a table with about 40000 records:</p>
  
  <pre><code>UPDATE tableName
  SET colA = val, colB = val
  WHERE ID IN (select RecordIDs from tableB where needUpdate = 'Y')
  </code></pre>
  
  <p>When the above query is executed, I found out that the below query taken ~ 15 seconds</p>
  
  <pre><code>SELECT RecordIDs
  FROM tableB
  WHERE needUpdate = 'Y'
  </code></pre>
  
  <p>But when I take away the where clause (i.e. <code>update tableName set colA = val, colB = val</code>) The query runs smoothly.</p>
  
  <p>Why this happens? are there any ways to shorten the time of execution?</p>
  
  <hr>
  
  <p>Edited:</p>
  
  <p>Below is the structure of both tables:</p>
  
  <pre><code>tableName:
      ID int,
      VehicleBrandID int,
      VehicleLicenseExpiryDate nvarchar(25),
      LicensePlateNo nvarchar(MAX),
      ContactPerson nvarchar(MAX),
      ContactPersonID nvarchar(MAX),
      ContactPersonPhoneNumber nvarchar(MAX), 
      ContactPersonAddress nvarchar(MAX),
      CreatedDate nvarchar(MAX),
      CreatedBy nvarchar(MAX)
      PRIMARY KEY (ID)
  
  
  
    tableB:
      RowNumber int
      RecordIDs int
      NeedUpdate char(1)
      PRIMARY KEY (RowNumber)
  </code></pre>
  
  <hr>
  
  <p>Edited</p>
  
  <p>Below screenshot is the execution plan for the update query</p>
  
  <p><img src="https://i.stack.imgur.com/3GTnb.png" alt="enter image description here"></p>