需要帮助提高 SQL 删除性能

Question

我有两个关系，客户和地址。首先，我无权修改数据库(例如为客户和地址制作新的组合(。 我需要的是，给定一个州列表，删除具有与该州相关的地址 1 或地址 2 的客户。 我在下面做的事情非常慢，所以任何帮助将不胜感激。

foreach (State state in listOfStates)
{
query = 
"DELETE FROM CUSTOMER
WHERE EXISTS (
SELECT * FROM ADRESS
WHERE CUSTOMER.ADRESS1 = ADRESS.ID
AND STATE = " + state + ")";
cmd.CommandText = query;
cmd.ExecuteNonQuery();
query = 
"DELETE FROM CUSTOMER
WHERE EXISTS (
SELECT * FROM ADRESS
WHERE CUSTOMER.ADRESS2 = ADRESS.ID
AND STATE = " + state + ")";
cmd.CommandText = query;
cmd.ExecuteNonQuery();
}
Customer
+----------+---------+---------+--+
|   Name   | Adress1 | Adress2 |  |
+----------+---------+---------+--+
| John Doe |       1 |       2 |  |
| Victor   |       3 |       4 |  |
| Renat    |       5 |       6 |  |
+----------+---------+---------+--+
Adress
+----+--------+------+------------+
| ID | Street | City |   State    |
+----+--------+------+------------+
|  1 | xxx    | xxx  | California |
|  2 | xxx    | xxx  | Florida    |
|  3 | xxx    | xxx  | California |
|  4 | xxx    | xxx  | Ohio       |
|  5 | xxx    | xxx  | Virginia   |
|  6 | xxx    | xxx  | Colorado   |
+----+--------+------+------------+

Answer 1

我建议你为状态建立一个IN子句。如果您的州列表List<string>，这将是：

string states = "'" + string.Join("','", listOfStates) + "'";

(这将导致类似'Arizona','Kentucky','Tennessee'的东西，即一个逗号分隔的字符串，所有状态都括在单引号中。

如果 listOfStates 不是List<string>，则调整上述内容以满足您的集合类型，例如

string states = "'" + string.Join("','", listOfStates.Select(state => state.Name)) + "'";

然后对所有状态运行单个删除语句：

query = 
"DELETE FROM customer
WHERE EXISTS (
SELECT * FROM adress
WHERE adress.id IN (customer.adress1, customer.adress2)
AND adress.state IN (" + states + "))";
cmd.CommandText = query;
cmd.ExecuteNonQuery();

Answer 2

根据我对 Access 的经验，连接通常比 WHERE EXISTS 子句快得多。

这应该可以通过复合连接来实现。以下是空气代码，但应该有效。

Access 喜欢在非平凡的 JOIN 子句中使用大量括号。

SELECT c.*
FROM CUSTOMER c
INNER JOIN ADRESS a
ON ((c.ADRESS1 = a.ID OR c.ADRESS2 = a.ID)
AND (a.STATE = <state>)
)

如果上述方法有效，只需将"选择"替换为"删除"。

你也可以把它和Thorsten的建议结合起来：

AND (a.STATE IN ('state1', 'state2'))