Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Delete taking time due to foreign keys Expand / Collapse
Author
Message
Posted Wednesday, February 20, 2013 2:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, Visits: 556
Dear All

We have a base table and 10 other tables related with foreign keys to this. One of the foreign key table has miliion rows.
Foreign keys helps to stop delete from Base Table if there is data in any othese 10 tables with same key value that being deleted.

But because of miliion rows it takes long time to delete the row from main tables.


How to speeed up the delete query?
Post #1421989
Posted Wednesday, February 20, 2013 2:35 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 6:14 AM
Points: 647, Visits: 1,312
Check whether you have proper indexes for the child table.

Run a delete statement enabling execution plan and see whether the index is being used.


-- Roshan Joe
*******************************************
There are two types of DBAs. Those who has skills and those who have permissions
Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help
Custom cleanup script for backups
Post #1421993
Posted Wednesday, February 20, 2013 3:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, Visits: 556
Created index on foreign key tables , for key column of the base table. but no luck.

Execution plan shows index seek for these tables.

After disabling the foreign keys on this table delete goes very fast
Post #1422004
Posted Wednesday, February 20, 2013 7:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 7,140, Visits: 12,764
Krishna1 (2/20/2013)
Created index on foreign key tables , for key column of the base table. but no luck.

Not the Base Table, the Child Table needs an index on the columnthat refers to the Base Table. See, everytime a delete occurs the Engine needs to do a SELECT on the child table to ensure it is not orphaning any rows. If the column in the Child Table is not indexed that can be very detrimental to performance.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1422071
Posted Wednesday, February 20, 2013 9:27 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, Visits: 556
I have aded index on child but still it takes long time and in execution plan it shows index seek.
Post #1422390
Posted Thursday, February 21, 2013 12:28 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:31 AM
Points: 2,841, Visits: 3,985
Delete will take time as OPC mentioned above as it has to reference child tables too .

Two approaches are there.

first,
although it is not recommened if you are following strictly the FK concept BUT yes , remove the FK constraints then do the delete from parent + childs tables (CAREFULLY handle the data to avoid orphans records)

Second, take the batch approach for delete operation.


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1422416
Posted Thursday, February 21, 2013 3:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 7,140, Visits: 12,764
Please post the actual execution plan and all involved table definitions including indexes and constraints.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1422485
Posted Thursday, February 21, 2013 6:50 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:14 AM
Points: 4,611, Visits: 4,070
In the original post, it's noted that there is a base table and 10 children. I'd bet there's a missing index on the foreign key of at least one of them. Have you been able to isolate the bottleneck to a single child?


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1422552
Posted Thursday, February 21, 2013 6:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 7,140, Visits: 12,764
Ed Wagner (2/21/2013)
In the original post, it's noted that there is a base table and 10 children. I'd bet there's a missing index on the foreign key of at least one of them. Have you been able to isolate the bottleneck to a single child?

He said it eases up when he drops one specific FK so I was thinking there might be a grandchild causing the issue, i.e. a child of the child table for which the FK was dropped.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1422558
Posted Thursday, February 21, 2013 7:01 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:14 AM
Points: 4,611, Visits: 4,070
Good point. We're back to needing the DDL.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1422562
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse