February 20, 2013 at 2:15 am
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?
February 20, 2013 at 2:35 am
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.
February 20, 2013 at 3:17 am
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
February 20, 2013 at 7:13 am
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
February 20, 2013 at 9:27 pm
I have aded index on child but still it takes long time and in execution plan it shows index seek.
February 21, 2013 at 12:28 am
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;-)
February 21, 2013 at 3:53 am
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
February 21, 2013 at 6:50 am
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?
February 21, 2013 at 6:57 am
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
February 21, 2013 at 7:01 am
Good point. We're back to needing the DDL.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply