SQL Server 2005：删除优化
如果在 ProductFileInfo 表中找不到引用，这是从 DeletedProducts 表中删除的最有效方法吗？
示例：
DELETE FROM DeletedProducts
WHERE ProductId NOT IN SELECT DISTINCT ProductID FROM ProductFileInfo
或者 NOT EXIST 是执行此操作的更好方法。
注意：ProductFileInfo 中有超过 2000 万条记录。
我正在使用 SQL Server 2005 Standard。
谢谢
Is this the most efficient way to delete from DeletedProducts table where there are not references found in the ProductFileInfo table?
Sample:
DELETE FROM DeletedProducts
WHERE ProductId NOT IN SELECT DISTINCT ProductID FROM ProductFileInfo
Or is NOT EXIST a better way to perform this.
Note: ProductFileInfo has over 20 Million records in it.
SQL Server 2005 Standard is what I am using.
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
NOT IN 和 NOT EXISTS 可能会产生相同的计划。您要删除多少行？如果数量很多，我会批量处理 5K 或 10K，这样您就不会用一个大事务填充您的 LOG，然后如果它因某种原因失败，则需要进行一次大回滚，
例如
为了让 GO N 工作你需要 SSMS 和 service pack 2 (IIRC)，但当然你也可以编写一个 while 循环..
while @@rowcount > 0 ......NOT IN and NOT EXISTS will probably produce the same plan. How many rows are you going to delete? If it is a lot I would do batches of 5K or 10K this way you won't fill your LOG with one big transaction and then if it fails for whatever reason it needs to do a big rollback
for example
in order for GO N to work you need SSMS and service pack 2 (IIRC) but of course you can also write a while loop..
while @@rowcount > 0.......尝试多种解决方案并测试它们的性能，YMMV。
还可以尝试外连接
try multiple solutions and test their performance, YMMV.
Also try an outer join
好吧，我通常会写一些类似的内容
，可能，您不想锁定查找表，因此您可以指定
with(readpast)提示或with(nolock)< /代码>。Well, I typically write something along the lines of
Probably, you don't want to lock the look-up table, so you can specify
with(readpast)hint orwith(nolock).这是一个很好的资源，完全可以满足您的要求。
http://www.sql-server-performance.com/tips/t_sql_where_p3。 ASPX
This is a great resource for exactly what your asking.
http://www.sql-server-performance.com/tips/t_sql_where_p3.aspx