Improve performance of deletes on a table variable

Question

I have seen performance tweaks for delete on normal tables in t-sql.

But are there performance tweaks on deletes on table variables to be done?

---

EDIT

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.

DELETE FROM @UsersCriteria
FROM @UsersCriteria UsersCriteria
WHERE UserId IN (SELECT UserID FROM UserExclusionsEvaluate WHERE PushRuleExclusionMet = 1)

In it's current incarnation, @UsersCriteria is:

DECLARE @UsersCriteria TABLE
(
    UserId int primary key,
    PushRuleUserCriteriaType int
)

I've tried @UsersCriteria as non primary and experimented with clustered non-clustered.

It's probable also the problem is with the IN. I've also tried a JOIN on a subquery.

---

EDIT:

GOOD NEWS! After lots of playing with the SQL, including moving the suquery into a chained CTE, attempting table hints etc etc etc.

A simple change from a table variable to a temp table dramatically improved the performance.

Which is really interesting, as deletes ran fine byself, the subquery (on the CTE) ran fine byitself. But mixing the two ran mega slow.

I'm guessing that the optimiser can't kick in when using a CTE in a subquery? Maybe when mixed with the delete.

Answer 1

Not really.

Unless you defined a PK in the DECLARE which may work: there are no statistics for table variables and the table is assumed to have 1 row only