Deleting from a small table referenced by a large table

Go To StackoverFlow.com

1

Imagine a database that tracks payments between customers.

Say I've got a Customer table:

Customer 
----------
CustomerID
Name

and a Transaction table

Transaction
------------
BuyerID
SellerID
Amount

Where BuyerID and SellerID are both Foreign Key references to the CustomerID column of the Customer table.

In my current (analogous) situation, the Transaction table is large (500 Million Rows), even though the Customer table is small (2000 rows). Deleting a row from the Customer table, however, takes a very long time, because the database has to scan the Transaction table to see whether the Customer has any referencing Transactions (In fact, it has to do it twice - one to check for BuyerID and one for SellerID). The Transaction table is not Indexed on either BuyerID or SellerID (The real table is indexed on a combination of Buyer, Seller, and a few other columns)

I know I can drop all the foreign key constraints, delete the rows, and then re-add the constraints. Will that be any faster than just doing the DELETE FROM with the foreign keys enabled? Are there any other ways to speed up the delete operation that I'm missing.

2012-04-03 20:17
by Ryan
You state your transaction table is large, then state it is small. I can't decide which is true - Mark Schultheiss 2012-04-03 20:21
Are your deletes cascaded - NullUserException 2012-04-03 20:23
Is your transaction table indexed with BuyerID or SellerID columns - hkutluay 2012-04-03 20:24
Deletes aren't cascaded, fixed the Transaction/Customer Typo (thanks Mark), added info about indexe - Ryan 2012-04-03 20:28


4

You should index BuyerID and SellerID in your Transaction table...

For a further explanation as to why you should be indexing your foreign keys, read Kimberly Tripp's excellent article on the subject.

2012-04-03 20:31
by Michael Fredrickson


1

go ahead and add the extra two indexes. leave the constraints alone.

2012-04-03 20:29
by Randy


1

Generally if I have sales transactions for a customer, I don't want to ever delete them or the customer. This is exactly why you havea foreign key constraint, so you don't delete the customer. That is just messing with your financial reporting (Why did sales for 2011 suddenly go down 20%, oh we deleted some records from the database, not a good converasation to have.). What you want is to make a customer inactive not to delete them usually in this case.

Given that Ryan says this is a database in development and not in production where otehrs are likely to be entering data to it at the same time, I would drop the FKs, put the customer ids I intend to delete into a work table and then do the deletes to the parent and all child tables. This way you can delete from the 500 million row table in batches if you need to. Then when you are done put the FKs back on.

2012-04-03 20:40
by HLGEM
I think that you're absolutely correct for a production system, but this database is currently early in development, and the Customers in question were added erroneously, so we do want them deleted in this cas - Ryan 2012-04-03 20:45
I just wanted to point this out because people sometimes forget that FKs are there to prevent you from deleting - HLGEM 2012-04-03 20:47


0

If I am working with 500 millions rows, first thing I will do is - remove foreign key reference and have centralize place like stored procedure to control delete functionality.

2012-04-03 20:27
by Firoz Ansari
Are you suggesting that this will make the OP's deletions faster - Tim Lehner 2012-04-03 20:43