Posted by Erland Sommarskog on 02/26/06 19:38

nnelson (nnelson@cmscms.com) writes:
> Thank you both for your replies. I should note that the table was
> populated from a C# application and I loaded the 300,000 records in 3
> minutes which I was very impressed with. There are other tables that
> got data added to it, like KnownBy and CustomerActivity. Now that I
> think of it, I deleted the child rows from those two tables before I
> tried to delete the customer rows and those went fairly quick. So,
> something else must be wrong.

That information does not say much.

> I dont understand what SPID is, but I did see a value of 51 in the
> properties of the query.

SPID = "Server process ID". 51 is a very typical SPID, in fact the lowest
spid a user process can have in SQL 2000 and later.

> I'll look into that. I ran the sp_who and the blk column was 0 for all
> rows.

OK, no blocking.

> There are a lot of tables that are children of the customer table, but I
> would have to think that they are all indexed. Once I find the indexes
> I'll let you know (pretty unfamiliar with 2005 from 2000). FYI - there
> isn't any data in any of the other tables for these customers I want to
> delete, but there are about 4 million orders for the other 2.3 million
> customers.

And the orders table does not have an index on CustomerID.

It is irrelevant here that none of the Customers you want to delete
do not have any orders. As you have set up a foreign-key constraint,
SQL Server needs to check that you are not deleting any customers for
which there are orders.

Deleting 33000 rows from a table that is referenced by a non-indexed
column with 4 foud million rows, is not going to run fast. Then again,
it should not take nine hours, not even if SQL Server settled for a
really poor plan to perform the FK check.

Anyway, a customer ID column in an Orders table is something I would
expect to be indexed.




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]