title: Why a SQL Server join is taking too long to execute?
body: <p>I am populating a temporary table with data, below is the definition of my temp table.</p>
  
  <pre><code>DECLARE @PurgeFilesList TABLE
  (
      JobFileID BIGINT,
      ClientID INT,
      StatusID INT,
      IsPurgeSuccessfully BIT,
      ReceivedDate DATETIME,
      FilePath VARCHAR(2000),
      StatementPath VARCHAR(2000)
  )
  </code></pre>
  
  <p>Insert logic to populate temp table, after this I am making an additional join with a table named <code>Account</code>:</p>
  
  <pre><code>SELECT 
      JobFileID,
      PFL.ClientID,
      StatusID,
      IsPurgeSuccessfully,
      ReceivedDate,
      CASE 
         WHEN FilePath IS NULL THEN StatementPath 
         ELSE FilePath 
      END 'FilePath'
  FROM 
      @PurgeFilesList PFL
  INNER JOIN 
      Account(NOLOCK) A ON ISNULL(PFL.ClientID, 0) = ISNULL(A.ClientID, 0) 
                        AND A.HoldStatementPurge = 0
  </code></pre>
  
  <p>But, this join is taking too much time. Although total number of rows in Account table is less than 5000.</p>
  
  <p><code>Account</code> table schema:</p>
  
  <pre><code>Column_name         Type    Computed    Length
  -----------------------------------------------
  AccountID           bigint      no        8
  AccountNumber       varchar     no        32
  PrimaryCustomerName varchar     no        100
  LastName            varchar     no        100
  ClientName          varchar     no        32
  BankID              varchar     no        32
  UpdatedDate         datetime    no        8
  IsPurged            bit         no        1
  PurgeDate           datetime    no        8
  ClientID            int         no        4
  HoldStatementPurge  bit         no        1
  </code></pre>
  
  <p>Kindly let me know, if any other info is required.</p>
  
  <p>Execution Plan:</p>
  
  <p><a href="https://i.stack.imgur.com/M9rWi.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/M9rWi.png" alt="enter image description here"></a></p>