Deleting records from a table takes a long time

I have a table A that has about 35000 rows. Table B has about 2 million rows
.
Three columns colX,colY and colZ in table B have referential integrity
constraints with the primary key of table A. i.e. fk_1 for colX referencing
pk of table A, fk_2 for colY referencing pk of table B,fk_3 for colZ
referencing pk of table C ( There are other fks also on tableB and indexes)
When I delete rows from table A the delete takes a very long time sometimes
about 40 minutes for about 10 rows( if I allow the delete sql to run) Is
there a way I can speed up the delete from table A? Or any other things I
should look into?Are your statistics up to date? See UPDATE STATISTICS in BOL.
"Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:6EDF8E2A-8AEE-439B-9ACB-A36CE3F7956B@.microsoft.com...
>I have a table A that has about 35000 rows. Table B has about 2 million
>rows.
> Three columns colX,colY and colZ in table B have referential integrity
> constraints with the primary key of table A. i.e. fk_1 for colX
> referencing
> pk of table A, fk_2 for colY referencing pk of table B,fk_3 for colZ
> referencing pk of table C ( There are other fks also on tableB and
> indexes)
> When I delete rows from table A the delete takes a very long time
> sometimes
> about 40 minutes for about 10 rows( if I allow the delete sql to run) Is
> there a way I can speed up the delete from table A? Or any other things I
> should look into?|||Frank1213 wrote:
> I have a table A that has about 35000 rows. Table B has about 2
> million rows. Three columns colX,colY and colZ in table B have
> referential integrity constraints with the primary key of table A.
> i.e. fk_1 for colX referencing pk of table A, fk_2 for colY
> referencing pk of table B,fk_3 for colZ referencing pk of table C (
> There are other fks also on tableB and indexes) When I delete rows
> from table A the delete takes a very long time sometimes about 40
> minutes for about 10 rows( if I allow the delete sql to run) Is there
> a way I can speed up the delete from table A? Or any other things I
> should look into?
If table A has FK values in table B, then you can't delete from table A
unless you have set up cascading deletes. Have you? The other way to
delete is to manually remove the table B rows that match the PK in table
A, then delete from table A.
It's impossible to really guess where the holdup is in your testing. I
assume you have an index on the FK col1X in tableB, right?
David Gugick
Imceda Software
www.imceda.com|||Do you have an index on the foreign key in table B that references table A?
If not then when you delete from A it will probably be doing table scans of
table B to perform the referential integrity action (validate, cascade
delete/update).
"Frank1213" wrote:

> I have a table A that has about 35000 rows. Table B has about 2 million ro
ws.
> Three columns colX,colY and colZ in table B have referential integrity
> constraints with the primary key of table A. i.e. fk_1 for colX referenci
ng
> pk of table A, fk_2 for colY referencing pk of table B,fk_3 for colZ
> referencing pk of table C ( There are other fks also on tableB and indexes
)
> When I delete rows from table A the delete takes a very long time sometime
s
> about 40 minutes for about 10 rows( if I allow the delete sql to run) Is
> there a way I can speed up the delete from table A? Or any other things I
> should look into?|||Run the delete with showplan and statistics io to see where your slowdown is
at. Have you done this yet? Post the results, the original query, and the
ddl. Maybe we can help you out with a more educated guess. :)
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O10aVwQKFHA.3552@.TK2MSFTNGP12.phx.gbl...
> Frank1213 wrote:
> If table A has FK values in table B, then you can't delete from table A
> unless you have set up cascading deletes. Have you? The other way to
> delete is to manually remove the table B rows that match the PK in table
> A, then delete from table A.
> It's impossible to really guess where the holdup is in your testing. I
> assume you have an index on the FK col1X in tableB, right?
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Like Derrick, I would guess that putting an index on the foreign key in the
child table would help. Can you define "sometimes"? Does it sometimes run
in 40 ms? How busy is the system at the time? Do you have adequate
hardware?
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:6EDF8E2A-8AEE-439B-9ACB-A36CE3F7956B@.microsoft.com...
>I have a table A that has about 35000 rows. Table B has about 2 million
>rows.
> Three columns colX,colY and colZ in table B have referential integrity
> constraints with the primary key of table A. i.e. fk_1 for colX
> referencing
> pk of table A, fk_2 for colY referencing pk of table B,fk_3 for colZ
> referencing pk of table C ( There are other fks also on tableB and
> indexes)
> When I delete rows from table A the delete takes a very long time
> sometimes
> about 40 minutes for about 10 rows( if I allow the delete sql to run) Is
> there a way I can speed up the delete from table A? Or any other things I
> should look into?