Re: DELETE FROM - Somethings not right

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
--

.

Relevant Pages

  • Re: Passing a Parameter to a Form when Opening
    ... The best thing is first to look at the capabilities of the SQL server ... > CustID, Surname, Forename and address of all people with that surname. ... > The correct customer is then selected from the datagrid and I want to open ...
    (microsoft.public.dotnet.general)
  • Re: Access the right program for the job?
    ... include any other fields that describe a customer only; ... as the primary key, and get rid of the PartID field. ... CustID (foreign key from tblCustomers) ...
    (comp.databases.ms-access)
  • Re: Add Records
    ... One containing each customer, just once. ... The other table would have (apart from its own Primary Key) a number field ... Because the subform and mainform are linked, when you are on a customer's ... CustID (this is a number field. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Add Records
    ... One containing each customer, just once. ... The other table would have (apart from its own Primary Key) a number field ... Because the subform and mainform are linked, when you are on a customer's ... CustID (this is a number field. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Problem adding "<16" into DCOUNT function to reflect a cap of 16???
    ... tblCUSTOMER INFO and tblEVENTS with a one to many relationship ... in the frmEVENTS, a user uses a combo box with custID to pull up the CustID ... user can schedule the customer for a testing date. ...
    (microsoft.public.access.forms)