SQL - 提高 NOT EXISTS 查询性能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/554509/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL - improve NOT EXISTS query performance
提问by Melursus
Is there a way I can improve this kind of SQL query performance:
有没有办法可以提高这种 SQL 查询性能：
INSERT
INTO ...
WHERE NOT EXISTS(Validation...)
The problem is when I have many data in my table (like million of rows), the execution of the WHERE NOT EXISTSclause if very slow. I have to do this verification because I can't insert duplicated data.
问题是当我的表中有很多数据（比如百万行）时，WHERE NOT EXISTS子句的执行速度很慢。我必须做这个验证，因为我不能插入重复的数据。
I use SQLServer 2005
我使用 SQLServer 2005
thx
谢谢
采纳答案by cjk
Make sure you are searching on indexed columns, with no manipulation of the data within those columns (like substring etc.)
确保您正在搜索索引列，而不操作这些列中的数据（如子字符串等）
回答by Blorgbeard is out
Off the top of my head, you could try something like:
在我的头顶上，您可以尝试以下操作：
TRUNCATE temptable
INSERT INTO temptable ...
INSERT INTO temptable ...
...
INSERT INTO realtable
SELECT temptable.* FROM temptable
LEFT JOIN realtable on realtable.key = temptable.key
WHERE realtable.key is null
回答by Otávio Décio
Try to replace the NOT EXISTS with a left outer join, it sometimes performs better in large data sets.
尝试用左外连接替换 NOT EXISTS，它有时在大型数据集中表现更好。
回答by dwc
Pay attention to the other answer regarding indexing. NOT EXISTS is typically quite fast if you have good indexes.
注意关于索引的其他答案。如果您有良好的索引，NOT EXISTS 通常非常快。
But I havehad performance issues with statements like you describe. One method I've used to get around that is to use a temp table for the candidate values, perform a DELETE FROM ... WHERE EXISTS (...), and then blindly INSERT the remainder. Inside a transaction, of course, to avoid race conditions. Splitting up the queries sometimes allows the optimizer to do its job without getting confused.
但是我在使用您描述的语句时遇到了性能问题。我用来解决这个问题的一种方法是对候选值使用临时表，执行 DELETE FROM ... WHERE EXISTS (...)，然后盲目地插入其余部分。在事务内部，当然要避免竞争条件。拆分查询有时允许优化器完成其工作而不会感到困惑。
回答by hova
If you can at all reduce your problem space, then you'll gain heaps of performance. Are you absolutely sure that every one of those rows in that table needs to be checked?
如果您可以减少问题空间，那么您将获得大量性能。您绝对确定该表中的每一行都需要检查吗？
The other thing you might want to try is a DELETE InsertTable FROM InsertTable INNER JOIN ExistingTable ON <Validation criteria>before your insert. However, your mileage may vary
您可能想尝试的另一件事是DELETE InsertTable FROM InsertTable INNER JOIN ExistingTable ON <Validation criteria>在插入之前。但是，您的里程可能会有所不同
回答by SqlACID
insert into customers
select *
from newcustomers
where customerid not in (select customerid
from customers)
..may be more efficient. As others have said, make sure you've got indexes on any lookup fields.
..可能更有效率。正如其他人所说，确保您在任何查找字段上都有索引。
回答by b_levitt
Outer Apply tends to work for me...
外申请往往对我有用......
instead of:
代替：
from t1
where not exists (select 1 from t2 where t1.something=t2.something)
I'll use:
我会用：
from t1
outer apply (
select top 1 1 as found from t2 where t1.something=t2.something
) t2f
where t2f.found is null
