CODEWITHSUNDEEP
c#sqldatabasems-accesssql-delete
DFryziak
DFryziak

Reputation: 55

Need help improving SQL DELETE performance

I have two relations, Customer and Adress. First of all, I don't have permission to modify the DB (like making a new raltion for customers and adresses). What I need is, given a list of States, delete customers who either have Adress1 or Adress2 related to that state. What I'm doing below is very slow, so any help would be appreciated.

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   |
+----+--------+------+------------+

Upvotes: 2

Views: 124

Answers (2)

Andre
Andre

Reputation: 27644

In my experience with Access, Joins are usually much faster than WHERE EXISTS clauses.

This should be doable with a composite JOIN. The following is air code but should work.

Access likes lots of parentheses in non-trivial JOIN clauses.

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

If the above works, simply replace SELECT by DELETE.

You can also combine this with Thorsten's suggestion:

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

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 95053

I suggest you build an IN clause for the states. If your listOfStates is List<string>, this will be:

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

(This would result in something like 'Arizona','Kentucky','Tennessee', i.e. a comma-separated string with all states enclosed in single quotes.)

If listOfStates is not List<string>, then adjust the above to meet your collection type, e.g.

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

Then run a single delete statement for all states:

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();

Upvotes: 2

Related Questions