CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Mark Robinson
Mark Robinson

Reputation: 13278

What is the fastest way to delete all customers from SQL Server based on that customer not meeting a single criteria

Let me explain by way of example. Consider the following table:

Customer Id | Food
------------+---------
1           | Pizza       
1           | Burger   
1           | Hot Dog  
2           | Milkshake       
2           | Burger   
3           | Pizza

I want to delete all the records for customers who have NEVER ordered Pizza. So, I should be left with this (customer #2 deleted):

Customer Id | Food
------------+---------
1           | Pizza       
1           | Burger   
1           | Hot Dog  
3           | Pizza

I know I can do a NOT IN but the performance is horrible.

What is the most performant way to write this query to achieve this against 100,000+ records in SQL Server?

Upvotes: 7

Views: 200

Answers (4)

Sonam
Sonam

Reputation: 3466

  • If the table size is huge, try deleting in batches. or
  • Move the records which needs not to be deleted in a temporary table.Truncate the main table and then move the rest of the records from the temporary table to the main table.

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460238

A simple NOT EXISTS should be efficient with proper indexes.

DELETE c1 FROM Customers c1
WHERE NOT EXISTS
(
    SELECT 1 FROM Customers  c2
    WHERE c1.[Customer Id] = c2.[Customer Id]
    AND c2.[Food] = 'Pizza'
);

Demo

Create an index on [Customer Id] and a non-clustered index on Food.

Upvotes: 9

valex
valex

Reputation: 24144

try

delete t from t 
left join (select distinct [Customer Id] from t where Food='Pizza') t2
on t.[Customer Id]=t2.[Customer Id]
where t2.[Customer Id] is null

SQLFiddle demo

Upvotes: 0

John Woo
John Woo

Reputation: 263843

how about NOT EXISTS

DELETE  a
FROM    table1 a
WHERE   NOT EXISTS
        (
            SELECT  1
            FROM    table1 b
            WHERE   a.customerID = b.customerID AND
                    b.Food = 'Pizza'
        )

Upvotes: 4

Related Questions