1

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.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Alex KeySmith
  • 16,657
  • 11
  • 74
  • 152
  • Do you have PushRuleExclusionMet indexed (assuming UserID is clustered) – gbn Aug 22 '11 at 18:36
  • Hi @gbn thanks for looking into this for me. PushRuleExclusionMet is in the CTE? Can a column in a CTE be indexed? – Alex KeySmith Aug 22 '11 at 18:38
  • What does your execution plan look like? – HLGEM Aug 22 '11 at 19:06
  • Hi @HLGEM sorry to tell you the truth, I'm a bit lost in the execution plan (I've been working on the single SQL statement for 6 days!, it's epic!) I cannot seem to find where the DELETE is executed in the plan, the diagram is epic! – Alex KeySmith Aug 22 '11 at 19:11
  • Wish I could give everyone the green answer tick, everyones help did the trick +1 for you all. – Alex KeySmith Aug 22 '11 at 19:21

3 Answers3

4

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

gbn
  • 422,506
  • 82
  • 585
  • 676
3

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.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • Would a batch delete really help on a heap though? – JNK Aug 22 '11 at 18:11
  • Don't know, never tried it possibly not. Unless he is currently deleting one record at a time in a cursor. Then a batch of all the records would certainly be faster. – HLGEM Aug 22 '11 at 18:13
  • I would still try replacing both the CTE and table variable with temp tables. Temp tables can be indexed (that could help the subselect) and tend to run faster for large data sets. From Books Online:"Indexes cannot be created explicitly on table variables, and no statistics are kept on table variables. In some cases, performance may improve by using temporary tables instead, which support indexes and statistics." – HLGEM Aug 22 '11 at 19:09
  • Hi @HLGEM good news looks like a temp table did the trick. Thanks for your help. – Alex KeySmith Aug 22 '11 at 19:19
1

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.

JNK
  • 63,321
  • 15
  • 122
  • 138