DELETE FROM - Somethings not right

I have a base customer table of 2 million records.  We're doing some
testing and I added 33000 rows incorrectly.  No biggie, we'll just
delete them, right?  Nope....9 hours later, process is still running
using this query.

delete from customer where custid in (select custid from #tmp1)

k...so we'll change it.

delete from customer where custid > 2295885

2 hours later, still going slower than a turtle...literally 1 row per
second.

custid is my primary key so you can't tell me it's not indexed.  It's
pretty bad when I have to resort to doing a restore of my entire
database of 27GB because it's faster than deleting 33000 rows.

WTF?

mind you...I'm running off of a 4 processor machine at 3.6GHZ and 8GB
RAM off the SAN..

nnelson  wrote:
> I have a base customer table of 2 million records.  We're doing some
> testing and I added 33000 rows incorrectly.  No biggie, we'll just
> delete them, right?  Nope....9 hours later, process is still running
> using this query.
>
> delete from customer where custid in (select custid from #tmp1)
>
> k...so we'll change it.
>
> delete from customer where custid > 2295885
>
> 2 hours later, still going slower than a turtle...literally 1 row per
> second.
>
> custid is my primary key so you can't tell me it's not indexed.  It's
> pretty bad when I have to resort to doing a restore of my entire
> database of 27GB because it's faster than deleting 33000 rows.
>
> WTF?

Do you have any unindexed foreign key's referencing the table? Not
indexing foreign keys can seriously hurt DELETE performance.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

nnelson (nnelson@cmscms.com)  writes:
> I have a base customer table of 2 million records.  We're doing some
> testing and I added 33000 rows incorrectly.  No biggie, we'll just
> delete them, right?  Nope....9 hours later, process is still running
> using this query.
>
> delete from customer where custid in (select custid from #tmp1)
>
> k...so we'll change it.
>
> delete from customer where custid > 2295885
>
> 2 hours later, still going slower than a turtle...literally 1 row per
> second.
>
> custid is my primary key so you can't tell me it's not indexed.  It's
> pretty bad when I have to resort to doing a restore of my entire
> database of 27GB because it's faster than deleting 33000 rows.

First thing to check is that you don't have any blocking somewhere.
Start your delete and not the spid in the status bar of the Query Analyzer.
From another window run sp_who, and check the Blk column. If this column
has a non-zero value, the process is blocked by this spid. My thinking
is that when you realised your mistake, you pressed the red button in
QA. If you were in the middle of a transaction, it was not rolled back,
but you must explicitly run ROLLBACK TRANSACTION.

As David said, unindexed FK columns in other tables could also be an issue,
although nine hours is well too long time for that. Then again, if the
FKs are cascdaing, maybe not.

Also check if the table has a DELETE trigger that could have performance
issue for this volume.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

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.

I dont understand what SPID is, but I did see a value of 51 in the
properties of the query.  I'll look into that. I ran the sp_who and the
blk column was 0 for all rows.  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.

I ran the process again after I posted the message last night using
this code: Delete from customer where custid > 2298595.  Again, 9 hours
later still running.  I looked at the properties of the Customer table
and the row count was what I wanted at 2298594.  And like a dummy I
cancelled the query then and it rolled back. LOL.  I found out it's
different from 2000 since 2005 rolls back.

The CustomerActivity record has a clustered index of CustomerActivityId
and Unique NonClustered index of CustId, ActivityDate and ActivityType.
The order table does not have an index on CustId. It has a unique non
clustered index on OrderNumber and OrderDate.  And another index on
OrderId.

SQL 2000 would never take this long, even with the current
setup...maybe 5 minutes.  I just can't understand it.

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Thanks again for your reply.  I never did solve the puzzle here.
Instead I ran three cursors at 10,000 a pop to delete the rows.  It
still took a number of hours to complete.  To me it's still baffling.
I have the same database in SQL 2000 and never had that problem over
there.

Oh well.  I'm able to move forward today anyway.

Wow.

Something is weird here.

I wonder if the delete is doing each row as an individual transaction,
with commit.

How could this be????

Perhaps it is deleting a row, rebuilding the index, committing, moving
on to the next????

A delete on 33k rows should be like 3 seconds.

????????????????

If your database is running  ALLOW_SNAPSHOT_ISOLA
TION ON, you might try
testing with it turned off to see how that affects performance.  Like Erland
said, something is wrong with performance that slow.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"sql server" <nnelson@cmscms.com> wrote in message
news:1141018808.405308.30200@t39g2000cwt.googlegroups.com...
> Thanks again for your reply.  I never did solve the puzzle here.
> Instead I ran three cursors at 10,000 a pop to delete the rows.  It
> still took a number of hours to complete.  To me it's still baffling.
> I have the same database in SQL 2000 and never had that problem over
> there.
>
> Oh well.  I'm able to move forward today anyway.
>