InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 23:20:56 GMT20http://www.sqlservercentral.com/Forums/Topic1421989-1292-1.aspxGood point. We're back to needing the DDL.Thu, 21 Feb 2013 07:01:18 GMTEd Wagnerhttp://www.sqlservercentral.com/Forums/Topic1421989-1292-1.aspx[quote][b]Ed Wagner (2/21/2013)[/b][hr]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?[/quote]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.Thu, 21 Feb 2013 06:57:17 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1421989-1292-1.aspxIn 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?Thu, 21 Feb 2013 06:50:42 GMTEd Wagnerhttp://www.sqlservercentral.com/Forums/Topic1421989-1292-1.aspxPlease post the actual execution plan and all involved table definitions including indexes and constraints.Thu, 21 Feb 2013 03:53:55 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1421989-1292-1.aspxDelete 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.Thu, 21 Feb 2013 00:28:03 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1421989-1292-1.aspxI have aded index on child but still it takes long time and in execution plan it shows index seek.Wed, 20 Feb 2013 21:27:33 GMTKrishna1http://www.sqlservercentral.com/Forums/Topic1421989-1292-1.aspx[quote][b]Krishna1 (2/20/2013)[/b][hr]Created index on foreign key tables , for key column of the base table. but no luck.[/quote]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.Wed, 20 Feb 2013 07:13:06 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1421989-1292-1.aspxCreated 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 fastWed, 20 Feb 2013 03:17:35 GMTKrishna1http://www.sqlservercentral.com/Forums/Topic1421989-1292-1.aspxCheck whether you have proper indexes for the child table.Run a delete statement enabling execution plan and see whether the index is being used.Wed, 20 Feb 2013 02:35:09 GMTjoeroshanhttp://www.sqlservercentral.com/Forums/Topic1421989-1292-1.aspxDear AllWe 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?Wed, 20 Feb 2013 02:15:56 GMTKrishna1