Improve performance of deletes on a table variable

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 k开发者_Go百科ick in when using a CTE in a subquery? Maybe when mixed with the delete.

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

Well there is a limited amount you can do. However, if you have a large data set in the table variable, you should be using a temp table instead if you need better performance.

You could also do the deletes in batches (say 1000 at a time).

Otherwise, show us your delete statment and we'll see if we see anything that can be imporved.

NO.

Table variables are unindexable and transient. They have no statistics.

They are not intended to store very large amounts of data.

If you have a table variable that is big enough to give you performance problems when you delete from it, you're using them in an unintended way. Put that data into a #Temp table or a real table so you have more control.