DELETE Performance:

DELETE is slow because it processes rows one-by-one:

1. Table scan: Find rows matching WHERE clause

2. Row-by-row processing: Check each row, fire triggers, log deletion

3. Index updates: Update all indexes on table

4. Transaction log: Write every deletion to log

5. Lock management: Acquire and release locks for each row

DELETE Performance Factors:

- Number of rows: More rows = slower (linear relationship)

- Indexes: More indexes = slower (each index must be updated)

- Triggers: DELETE triggers add overhead (can be 10x slower)

- WHERE clause: Indexed WHERE is faster (index seek vs table scan)

- Foreign keys: Cascade deletes add overhead (must delete child rows)

- Transaction log: Large deletes fill transaction log (can run out of space)

DELETE Performance Tips:

1. Index WHERE columns: DELETE FROM orders WHERE order_date < '2023-01-01';

Index order_date for 10x-100x speedup (index seek instead of table scan)

2. Batch large deletes: Delete 1000-5000 rows per transaction

DELETE FROM orders WHERE order_date < '2023-01-01' LIMIT 1000;

Prevents long locks, smaller transaction log, progress monitoring

3. Disable triggers temporarily: If safe, disable triggers during bulk delete

ALTER TABLE orders DISABLE TRIGGER ALL; -- SQL Server

DELETE FROM orders WHERE order_date < '2023-01-01';

ALTER TABLE orders ENABLE TRIGGER ALL;

4. Drop indexes before bulk delete: If deleting > 50% of rows

DROP INDEX idx_order_date ON orders;

DELETE FROM orders WHERE order_date < '2023-01-01';

CREATE INDEX idx_order_date ON orders(order_date);

Faster to rebuild index than update for each deletion

5. Use TRUNCATE if deleting all rows: 100x-1000x faster

Instead of: DELETE FROM temp_orders;

Use: TRUNCATE TABLE temp_orders;

TRUNCATE Performance:

TRUNCATE is fast because it deallocates data pages:

1. Deallocate pages: Release storage space (1 operation)

2. Reset identity: Set sequence back to starting value

3. Minimal logging: Log page deallocations (not individual rows)

4. No triggers: Skip trigger overhead

5. No row processing: No need to check rows

TRUNCATE Performance Factors: