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
- Dozens more answers to tough SQL Server questions from Michael Hotek are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.