在 SQL 中删除语句非常慢

我有这样的陈述，这是时间:

DELETE FROM [table] WHERE [COL] IN ( '1', '2', '6', '12', '24', '7', '3', '5')

我试着在这种时候做一个:

DELETE FROM [table] WHERE [COL] IN ( '1' )

到目前为止已经22分钟了，而且还在继续。

表中有260,000行，有四列。

有没有人知道为什么这么慢，以及如何加快速度？ 我在[ COL ]上确实有一个非唯一的、非聚集的索引，我在[ COL ]上执行 WHERE 操作。 我使用的是 SQLServer2008R2

更新: 我桌上没有触发器。

178557 次浏览
小开

If the table you are deleting from has BEFORE/AFTER DELETE triggers, something in there could be causing your delay.

Additionally, if you have foreign keys referencing that table, additional UPDATEs or DELETEs may be occurring.

小开

Deleting a lot of rows can be very slow. Try to delete a few at a time, like:

delete top (10) YourTable where col in ('1','2','3','4')
while @@rowcount > 0
begin
delete top (10) YourTable where col in ('1','2','3','4')
end
小开

Preventive Action

Check with the help of SQL Profiler for the root cause of this issue. There may be Triggers causing the delay in Execution. It can be anything. Don't forget to Select the Database Name and Object Name while Starting the Trace to exclude scanning unnecessary queries...

Database Name Filtering

Table/Stored Procedure/Trigger Name Filtering

Corrective Action

As you said your table contains 260,000 records...and IN Predicate contains six values. Now, each record is being search 260,000 times for each value in IN Predicate. Instead it should be the Inner Join like below...

Delete K From YourTable1 K
Inner Join YourTable2 T on T.id = K.id

Insert the IN Predicate values into a Temporary Table or Local Variable

小开
最佳答案

Things that can cause a delete to be slow:

  • deleting a lot of records
  • many indexes
  • missing indexes on foreign keys in child tables. (thank you to @CesarAlvaradoDiaz for mentioning this in the comments)
  • deadlocks and blocking
  • triggers
  • cascade delete (those ten parent records you are deleting could mean millions of child records getting deleted)
  • Transaction log needing to grow
  • Many Foreign keys to check

So your choices are to find out what is blocking and fix it or run the deletes in off hours when they won't be interfering with the normal production load. You can run the delete in batches (useful if you have triggers, cascade delete, or a large number of records). You can drop and recreate the indexes (best if you can do that in off hours too).

小开

Is [COL] really a character field that's holding numbers, or can you get rid of the single-quotes around the values? @Alex is right that IN is slower than =, so if you can do this, you'll be better off:

DELETE FROM [table] WHERE [COL] = '1'

But better still is using numbers rather than strings to find the rows (sql likes numbers):

 DELETE FROM [table] WHERE [COL] = 1

Maybe try:

 DELETE FROM [table] WHERE CAST([COL] AS INT) = 1

In either event, make sure you have an index on column [COL] to speed up the table scan.

小开

Check execution plan of this delete statement. Have a look if index seek is used. Also what is data type of col?

If you are using wrong data type, change update statement (like from '1' to 1 or N'1').

If index scan is used consider using some query hint..

小开

  1. Disable CONSTRAINT

    ALTER TABLE [TableName] NOCHECK CONSTRAINT ALL;

  2. Disable Index

    ALTER INDEX ALL ON [TableName] DISABLE;

  3. Rebuild Index

    ALTER INDEX ALL ON [TableName] REBUILD;

  4. Enable CONSTRAINT

    ALTER TABLE [TableName] CHECK CONSTRAINT ALL;

  5. Delete again

小开

I read this article it was really helpful for troubleshooting any kind of inconveniences

https://support.microsoft.com/en-us/kb/224453

this is a case of waitresource KEY: 16:72057595075231744 (ab74b4daaf17)

-- First SQL Provider to find the SPID (Session ID)


-- Second Identify problem, check Status, Open_tran, Lastwaittype, waittype, and waittime
-- iMPORTANT Waitresource select * from sys.sysprocesses where spid = 57


select * from sys.databases where database_id=16


-- with Waitresource check this to obtain object id
select * from sys.partitions where hobt_id=72057595075231744


select * from sys.objects where object_id=2105058535
小开

It's possible that other tables have FK constraint to your [table]. So the DB needs to check these tables to maintain the referential integrity. Even if you have all needed indexes corresponding these FKs, check their amount.

I had the situation when NHibernate incorrectly created duplicated FKs on the same columns, but with different names (which is allowed by SQL Server). It has drastically slowed down running of the DELETE statement.

小开

If you're deleting all the records in the table rather than a select few it may be much faster to just drop and recreate the table.

小开

open CMD and run this commands

NET STOP MSSQLSERVER
NET START MSSQLSERVER

this will restart the SQL Server instance. try to run again after your delete command

I have this command in a batch script and run it from time to time if I'm encountering problems like this. A normal PC restart will not be the same so restarting the instance is the most effective way if you are encountering some issues with your sql server.

小开

In my case the database statistics had become corrupt. The statement

delete from tablename where col1 = 'v1'

was taking 30 seconds even though there were no matching records but

delete from tablename where col1 = 'rubbish'

ran instantly

running

update statistics tablename

fixed the issue

小开

After inspecting an SSIS Package(due to a SQL Server executing commands really slow), that was set up in a client of ours about 5-4 years before the time of me writing this, I found out that there were the below tasks: 1) insert data from an XML file into a table called [Importbarcdes].

2) merge command on an another target table, using as source the above mentioned table.

3) "delete from [Importbarcodes]", to clear the table of the row that was inserted after the XML file was read by the task of the SSIS Package.

After a quick inspection all statements(SELECT, UPDATE, DELETE etc.) on the table ImportBarcodes that had only 1 row, took about 2 minutes to execute.

Extended Events showed a whole lot PAGEIOLATCH_EX wait notifications.

No indexes were present of the table and no triggers were registered.

Upon close inspection of the properties of the table, in the Storage Tab and under general section, the Data Space field showed more than 6 GIGABYTES of space allocated in pages.

What happened:

The query ran for a good portion of time each day for the last 4 years, inserting and deleting data in the table, leaving unused pagefiles behind with out freeing them up.

So, that was the main reason of the wait events that were captured by the Extended Events Session and the slowly executed commands upon the table.

Running ALTER TABLE ImportBarcodes REBUILD fixed the issue freeing up all the unused space. TRUNCATE TABLE ImportBarcodes did a similar thing, with the only difference of deleting all pagefiles and data.

小开

Older topic but one still relevant. Another issue occurs when an index has become fragmented to the extent of becoming more of a problem than a help. In such a case, the answer would be to rebuild or drop and recreate the index and issuing the delete statement again.

小开

As an extension to Andomar's answer, above, I had a scenario where the first 700,000,000 records (of ~1.2 billion) processed very quickly, with chunks of 25,000 records processing per second (roughly). But, then it starting taking 15 minutes to do a batch of 25,000. I reduced the chunk size down to 5,000 records and it went back to its previous speed. I'm not certain what internal threshold I hit, but the fix was to reduce the number of records, further, to regain the speed.


提交答案