Problem solve Get help with specific problems with your technologies, process and projects.

DELETE takes much longer than SELECT

I'm tring to delete many rows from a table in SQL Server, and it takes me ~30 minutes. If I select the same rows (that I want to delete), it takes ~5 seconds(!) and almost the same to insert. The only index I have is a primary key. Any idea why only delete takes so long? This is what I'm trying to do:
 DELETE PRF FROM VLD_ProfileProducts PRF INNER JOIN (SELECT DISTINCT BuyerID, GroupID FROM #AgrCat) BG ON PRF.BuyerID = BG.BuyerID INNER JOIN CAT_DAILY_ProductGroupsProducts PGP ON PGP.ProductGroupID = BG.GroupID AND PGP.ProductID = PRF.ProductID WHERE PRF.WeekDay = 7 AND PRF.DAXType = 1

Selecting the data only places a shared lock on the table, which unless there are modifications to those rows, won't compete with any other lock. An insert is going to grab an exclusive lock on the rows being inserted, and since they aren't yet visible to the table, aren't going to have competing locks either, as well as generally being a straight append to the table and the indexes on the table assuming you are inserting to the bottom of the table. When you run the delete, it has to acquire an exclusive lock on each row being deleted along with the corresponding portions of the index tree. That lock can compete with other locks on the table and produce blocking on your delete which will slow the entire process down.

If you want to do through the gory details of locking, blocking, internal table structures, and exactly how SQL Server processes a transaction, I would recommend picking up a copy of Kalen Delaney's Inside SQL Server 2000, which will give you more than enough information to explain what you are seeing.


For More Information

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.