6

My DB has about 90 tables. Most of the tables have an UpdatedBy and AddedBy column that have foreign keys pointing back to the user table. With even moderate amounts of data spread throughout the database, this makes for a very slow delete process when purging old user records.

Our delete query first updates all of the Updated/Added references to the current user and then deletes the user record. The execution plan shows table scans for each related table due to the foreign key. See this post as an example of what we are facing.

The pointers back to added and updated are not really useful other than for analysis during application problems. They are rarely used, and aren't a primary source of good information when they are. I'm wondering if I should remove the foreign key constraints all together or if I should possibly move the auditing of updates to a separate table that keeps a record of table name, column, value, and user id, or if there is some other generally accepted approach that the DBA community gravitates toward for this sort of situation.

  • 1
    The delete (and, presumably, the initial update) is slow because there are no supporting indexes. Have you considered soft-deleting the users instead? – Jon Seigel Jun 3 '14 at 18:26
  • How long is it taking? Are you dropping users one at a time or in batches? Do you need the whole delete wrapped in a transaction, or can you perform the updates in one step (or a series of small steps) and then delete the users once they no longer have any referenced rows? – Jon of All Trades Jun 3 '14 at 18:27
  • We aren't using transactions. Deletions can take from 45-75 seconds per user which is a problem with 140k users to delete. We've tested in batches of 10, 50, 100, 1,000, and 10,000. We can run the whole delete in about 4 minutes if we disable constraints and then re-enable. We've had soft deletes in place, but there is Personal Health Information that we want to remove from production. – Josh Jun 3 '14 at 18:39
  • I wonder if it would ease things a bit if you first ran an update to set these auditing columns to NULL, then ran the DELETE. Yes, this is two write operations for each row, but may turn out to be less expensive than any checks involved with the FKs when the values are present... particularly if you delete the rows in batches. It is moving swiftly toward the end of a long day, and I'm not sure what I'm suggesting actually makes sense, but it seems obvious that the constraints are having an effect on the duration of the transaction, and if the referencing value is NULL it may help. – Aaron Bertrand Jun 4 '14 at 0:58
  • Could be worth a try, but there would still be many, many records with valid pointers back to the User table. I would only be able to null out the records that I'm trying to delete. Also, I don't think this would give SQL server a reason to skip a table scan. – Josh Jun 4 '14 at 17:19
10

Make sure that there are indexes on the constrained columns, since the dbms will do a lookup using those columns in the referencing tables.

You can try using this script, it generates an index creation script for any non-indexed column being referenced by a cascading constraints.

SELECT 'CREATE NONCLUSTERED INDEX IX_'+OBJECT_NAME(fk.parent_object_id)+'_'+c.name+' ON '+OBJECT_NAME(fk.parent_object_id)+'('+c.name+') WITH (ONLINE=ON)' 
        --, OBJECT_NAME(fk.referenced_object_id) AS referenced_tale,  cc.name
FROM 
    sys.foreign_keys fk
    INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
    INNER JOIN sys.columns c ON fkc.parent_object_id = c.object_id AND fkc.parent_column_id = c.column_id
    INNER JOIN sys.columns cc ON fkc.referenced_object_id = cc.object_id AND fkc.referenced_column_id = cc.column_id
WHERE delete_referential_action_desc IN ('CASCADE', 'SET_NULL')
    AND NOT EXISTS
        (
        SELECT  1
        FROM
            SYS.index_columns ic 
            INNER JOIN sys.indexes i ON i.object_id = ic.object_id AND ic.index_id = i.index_id
        WHERE 
            1 = 1
            AND type_DESC IN ('CLUSTERED','NONCLUSTERED')
            AND ic.OBJECT_ID = c.object_id
            AND ic.column_id = c.column_id
            AND ic.is_included_column = 0
        )
  • Forgive my 'greenness', but is there a point where having too many indexes on a table would actually hurt performance? All the linked tables have other indexes, keys, and constraints defined outside of the scope of the pointer back to the User table. Should I be cautious about introducing 2 more indexes on the 90 or so tables? – Josh Jun 4 '14 at 17:22
  • 1
    There's really no catch-all answer for this, you have to tune your index needs based on the characteristics of your system. You might want to reference this answer here for details: dba.stackexchange.com/questions/5525/… – druzin Jun 5 '14 at 1:56
  • 1
    I think you also need AND ic.key_ordinal = 1 since the column might be in some index somewhere, but if it's not the first column it cannot be looked up quickly. – Ed Avis Aug 14 '15 at 17:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.