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...litera lly 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? 9 3581
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...litera lly 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/m...S,SQL.90).aspx
--
nnelson (nn*****@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...litera lly 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, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/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 CustomerActivit y. 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 CustomerActivit y record has a clustered index of CustomerActivit yId
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 (nn*****@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 CustomerActivit y. 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, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/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_ ISOLATION 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" <nn*****@cmscms .com> wrote in message
news:11******** *************@t 39g2000cwt.goog legroups.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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Ian McBride |
last post by:
(was: delete() confusion)
I have a class with multiple base classes. One of these base classes
(base1) has its own new/delete operators and nothing else. Another base
class (base 2) has a virtual destructor. The class with the virtual
destructor has AddRef() and Release() methods, and Release() ultimately does
a "delete this". Can that "delete this" statement somehow find the right
delete method? If it does, it involves a downcast...
|
by: Dave |
last post by:
Hello all,
In the code below, I see the following output:
base::operator new(size_t, int)
base::base()
base::~base()
base::operator delete(void *)
In the case of an exception being thrown during construction, a form of
|
by: Vic |
last post by:
Hi all,
When I test the Delete multi table function in MySQL,
DELETE table_name ...]
FROM table-references
I accidentally delete all data in one table. All data in that table
are gone when I try to select them out in Control Center. But when I
go into the /mysql/data/mydatabase/, I see a MYD, MYI, frm for that
table. And it seems that data is still inside the MYD, althought it's
|
by: Uwe Range |
last post by:
Hi to all,
I am displaying a list of records in a subform which is embedded in a
popup main form (in order to ensure that users close the form when
leaving it).
It seems to be impossible to delete a record in this subform.
When I switched modal off and tried to delete a record from the list,
I deleted a record on another form (below the popup form).
|
by: bob |
last post by:
Let's say you use the delete operator as follows:
Rocket *rocket = new Rocket;
void *voidptr = (void *) rocket;
delete voidptr;
Does the memory get deleted right? Do delete operations on void
pointers generally free the memory right?
| |
by: polocar |
last post by:
Hi,
I'm writing a program in Visual C# 2005 Professional Edition.
This program connects to a SQL Server 2005 database called
"Generations" (in which there is only one table, called
"Generations"), and it allows the user to add, edit and delete the
various records of the table.
"Generations" table has the following fields:
"IDPerson", NamePerson", "AgePerson" and "IDParent".
A record contains the information about a person (his name, his...
|
by: amiga500 |
last post by:
Hello,
I have one basic simple question. When I have multiple records in the
datagrid as follows:
Code Product 1 Product 2 Product 3
11111 A B C
22222 D E F
33333 G H I
44444 J K L
|
by: jpatchak |
last post by:
The following select query gives me the set of records I want to delete:
SELECT tblCaseNumbers.*
FROM (tblCaseNumberDump INNER JOIN ON tblCaseNumberDump.SSN = .) INNER JOIN tblCaseNumbers ON . = tblCaseNumbers.ProspectKey
WHERE ((=IIf(Right(,1)='D','D',IIf(Right(,1)='A','PLP', IIf(Right(,1)='B','SLP','M')))));
When I try to make it a delete query however:
DELETE tblCaseNumbers.*
FROM (tblCaseNumberDump INNER JOIN ON...
|
by: Slickuser |
last post by:
From my PHP page:
Grab all data from the database.
Go through a loop to generate the HTML.
Client side:
From the Color drop menu list, if a user change the value. It will
grab that value & update to the database based on the hidden ID.
DELETE ALL will delete everything the databse.
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |