title: Improve performance of deletes on a table variable
body: <p>I have seen performance tweaks for delete on normal tables in t-sql.</p>
  
  <p>But are there performance tweaks on deletes on table variables to be done?</p>
  
  <hr>
  
  <p>EDIT</p>
  
  <p>Here's an example:
  The plot gets thicker, as UserExclusionsEvaluate  is actually a CTE, but I'm going to try and optimise it around the table variable first (if possible). The CTE itself runs very quickly. Just the delete that is slow.</p>
  
  <pre><code>DELETE FROM @UsersCriteria
  FROM @UsersCriteria UsersCriteria
  WHERE UserId IN (SELECT UserID FROM UserExclusionsEvaluate WHERE PushRuleExclusionMet = 1)
  </code></pre>
  
  <p>In it's current incarnation, @UsersCriteria is:</p>
  
  <pre><code>DECLARE @UsersCriteria TABLE
  (
      UserId int primary key,
      PushRuleUserCriteriaType int
  )
  </code></pre>
  
  <p>I've tried @UsersCriteria as non primary and experimented with clustered non-clustered.</p>
  
  <p>It's probable also the problem is with the IN. I've also tried a JOIN on a subquery.</p>
  
  <hr>
  
  <p>EDIT:</p>
  
  <p><strong>GOOD NEWS!</strong>
  After lots of playing with the SQL, including moving the suquery into a chained CTE, attempting table hints etc etc etc. </p>
  
  <p>A simple change from a table variable to a temp table dramatically improved the performance.</p>
  
  <p>Which is really interesting, as deletes ran fine byself, the subquery (on the CTE) ran fine byitself. But mixing the two ran mega slow.</p>
  
  <p>I'm guessing that the optimiser can't kick in when using a CTE in a subquery? Maybe when mixed with the delete.</p>