提高表变量删除的性能
我已经看到了 t-sql 中普通表的删除性能调整。
但是表变量的删除是否需要进行性能调整？
编辑
这是一个例子： 绘图变得更厚，因为 UserExclusionsEvaluate 实际上是一个 CTE，但我将首先尝试围绕表变量对其进行优化（如果可能）。 CTE 本身运行速度非常快。只是删除速度慢。
DELETE FROM @UsersCriteria
FROM @UsersCriteria UsersCriteria
WHERE UserId IN (SELECT UserID FROM UserExclusionsEvaluate WHERE PushRuleExclusionMet = 1)
在它当前的化身中，@UsersCriteria 是：
DECLARE @UsersCriteria TABLE
(
UserId int primary key,
PushRuleUserCriteriaType int
)
我已经尝试将@UsersCriteria 作为非主要的，并尝试了集群非集群。
问题也可能出在 IN 上。我还尝试过在子查询上使用 JOIN 。
编辑：
好消息！ 经过大量使用 SQL 后，包括将 suquery 移动到链式 CTE、尝试表提示等。
从表变量到临时表的简单更改极大地提高了性能。
这真的很有趣，因为删除本身运行良好，子查询（在 CTE 上）本身运行良好。但将两者混合起来速度非常慢。
我猜测在子查询中使用 CTE 时优化器无法启动？也许与删除混合时。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
并不真地。
除非您在 DECLARE 中定义了可能工作的 PK：没有表变量的统计信息，并且假定表只有 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
好吧，你能做的事情是有限的。但是，如果表变量中有大量数据集，并且需要更好的性能，则应该使用临时表。
您还可以批量删除（例如一次删除 1000 个）。
否则，请向我们展示您的删除语句，我们将看看是否有任何可以改进的地方。
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.
否。
表变量是不可索引的且是瞬态的。他们没有统计数据。
它们的目的不是存储大量数据。
如果您有一个表变量，该变量足够大，以至于在您从中删除时会出现性能问题，那么您正在以意想不到的方式使用它们。将这些数据放入
#Temp表或真实表中，以便您拥有更多控制权。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
#Temptable or a real table so you have more control.