January 20, 2010 at 5:03 pm
My Delete query which is below is taking too long to delete 20,494 records from a table. Basically I have tableA which has a one-to-many relationship with tableb. Tableb has a foreign key column which is the primary key of tableA. I wrote a delete query like so below which ran really slow:
DELETE FROM tableb WHERE ProductId IN ( SELECT ProductId FROM tablea WHERE CreateDate > '10/1/2009' )
But this query above ran for continously for 2 hours with no results! So then I re-wrote by first moving all ProductId data from tableA to a temp table:
DELETE FROM tableb WHERE ProductId IN ( SELECT ProductId FROM #tempTable )
And still not help.
Would it be possible if any of you can tell me how I can do this delete using a cursor or while loop? Im not familiar with both and I would really appreciate it.
Thanks, S
--
:hehe:
January 20, 2010 at 6:46 pm
Can you check the actual execution plan? Maybe limit the subselect to top 10 results first. It's probably cascading the delete to other tables etc..
Also find out how long to get a primary key from tableb, then delete based on that since it is likely indexed.
Check for any triggers too.
January 20, 2010 at 7:42 pm
There is some other transaction which is holding locks on the object your query wants.
Look into sp_lock and find whether any other transaction accessing the same table and holds an exclusive lock.
Likely reasons are always some open transaction which has nt commited or some DDL operation
The code below can be handy.
select req_spid,
CASE req_mode WHEN
1 then 'Sch-S'
WHEN 2 then ' Sch-M'
WHEN 3 then ' S'
WHEN 4 then ' U'
WHEN 5then ' X'
WHEN 6 then ' IS'
WHEN 7 then ' IU'
WHEN 8 then ' IX'
WHEN 9 then ' SIU'
WHEN 10 then ' SIX'
WHEN 11 then ' UIX'
WHEN 12 then ' BU'
WHEN 13 then ' RangeS_S '
WHEN 14 then ' RangeS_U'
WHEN 15 then ' RangeI_N'
WHEN 16 then ' RangeI_S'
WHEN 17 then ' RangeI_U'
WHEN 18 then ' RangeI_X'
WHEN 19 then ' RangeX_S'
WHEN 20 then ' RangeX_U'
WHEN 21 then ' RangeX_X' END as Lock_type ,
CASE rsc_type WHEN
2 then 'Database'
WHEN 3 then ' File'
WHEN 4 then ' Index'
WHEN 5 then ' Table'
WHEN 6 then ' Page'
WHEN 7 then ' Key'
WHEN 8 then ' Extent'
WHEN 9 then ' RID (Row ID)'
WHEN 10 then 'Application' END as Resource_type,
case req_status when 1 then 'Granted'
WHEN 2 THEN 'Converting' When 3 then 'Waiting' END as Request_Status,
rsc_text as [Row/Page ID]
from master..syslockinfo
Even if you use a cursor or while loop u can still run into the same problem, if another connection holds a exclusive lock on the row you are trying to delete.
Regards,
Raj
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply