删除不在查询中的位置 [英] Delete where not in query

问题描述

我有一个查找表（ ## lookup ）。我知道这是坏的设计，因为我复制数据，但它加速了我的查询极大。我有一个填充此表的查询

I have a lookup table (##lookup). I know it's bad design because I'm duplicating data, but it speeds up my queries tremendously. I have a query that populates this table

insert into ##lookup select distinct col1,col2,... from table1...join...etc...

我想模拟这种行为：

delete from ##lookup
insert into ##lookup select distinct col1,col2,... from table1...join...etc...

这将清楚地更新表。但这是很多插入和删除。

This would clearly update the table correctly. But this is a lot of inserting and deleting. It messes with my indexes and locks up the table for selecting from.

这个表也可以通过以下方式更新：

This table could also be updated by something like:

delete from ##lookup where not in (select distinct col1,col2,... from table1...join...etc...)
insert into ##lookup (select distinct col1,col2,... from table1...join...etc...) except if it is already in the table

第二种方法可能需要更长时间，但我可以说没有锁，我可以从表中选择。

The second way may take longer, but I can say "with no lock" and I will be able to select from the table.

有关如何以第二种方式编写查询的任何想法？

Any ideas on how to write the query the second way?

推荐答案

DELETE LU
FROM ##lookup LU
LEFT OUTER JOIN Table1 T1 ON T1.my_pk = LU.my_pk
WHERE T1.my_pk IS NULL

INSERT INTO ##lookup (my_pk, col1, col2...)
SELECT T1.my_pk, T1.col1, T1.col2...
FROM Table1 T1
LEFT OUTER JOIN ##lookup LU ON LU.my_pk = T1.my_pk
WHERE LU.my_pk IS NULL

也可以使用WHERE NOT EXISTS而不是上面的LEFT JOIN来查找不存在的行。

You could also use WHERE NOT EXISTS instead of the LEFT JOINs above to look for non-existence of rows.

您可能还想查看MERGE语句，如果你在SQL 2008.否则，你不保持表同步 - 你只保持PK同步。如果一个表中的一个列发生变化，而另一个表中的列不发生变化，则不会在上面反映出来。

You might also want to look into the MERGE statement if you're on SQL 2008. Otherwise, you aren't keeping the tables in sync - you're only keeping the PKs in sync. If one of the column changes in one table but not the other that won't be reflected above.

无论哪种方式，听起来您可能想要考虑优化查询。虽然复制数据可能看起来像一个很好的解决你的性能问题，你可以看到它可以带来很多的头痛（这只是一个）。你最好找出糟糕的表现和修复的根本原因，而不是穿上这个丑陋的带子。

Either way, it sounds like you might want to consider optimizing queries. While duplicating the data may seem like a nice fix for your performance issues, as you can see it can carry a lot of headaches with it (and this is just one). You're better off finding the underlying cause of the poor performance and fixing that rather than putting on this ugly bandaid.

这篇关于删除不在查询中的位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！