Process blocking itself .. help !

Question

Process blocking itself .. help !

Simon_L

SSCarpal Tunnel

Points: 4669
More actions
November 18, 2008 at 11:14 am

#75367

Hi
Ive got piece of sql that selects a list fo client ids from a table using a cursor and then deletes data from a a series of tables based on that id.. see cut down version below.. anyways it takes an age to run and on running an sp_who2 I see its blocking itself.
Is there anyway i.e query plan or a trace that would show up exaclty where its blocking itself ?
And is there anyway round it ?
thanks si
_{declare @id varchar (10)}
declare id_cur cursor for
select id
from redundantdata
open id_cur
fetch next from id_cur into @id
while @@fetch_status = 0
begin
delete USER_INSTALL where id = @id
delete USER_JOB where id = @id
delete USER_log where id = @id
fetch next from id_cur into @id
end
close id_cur
deallocate id_cur
go

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

Lots of code will have temporary blocks that make it look like it's blocking itself. But, thats NOT the reason it takes an "age" to run... the cursor has the privelege of causing that little problem all by itself.

Lets say you have 1000 rows of redundant data in each of the 3 tables... which do you think will take longer... 3 deletes of a 1000 rows each, or 3000 deletes of 1 row each?

Write back and let us know how many rows you expect to delete so we can show you how to do this without the slothfulness of a cursor that does 3 deletes per loop.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Simon_L SSCarpal Tunnel Points: 4669 More actions · Answer 2

Jeff Moden (11/18/2008)
Lets say you have 1000 rows of redundant data in each of the 3 tables... which do you think will take longer... 3 deletes of a 1000 rows each, or 3000 deletes of 1 row each?.

perfect sense

Jeff Moden (11/18/2008)
Lots of code will have temporary blocks that make it look like it's blocking itself.

in fact using EM instead of sp_who2 is shows they are latchs(thanks to mohammed for pointing me in this direction)

the code circles through 180 odd client ids and deletes from 30 tables,,, usually 1 to 3 rows per delete but six digits for one table per delete

takes 30 minutes to complete

thanks simon

Garadin One Orange Chip Points: 29613 More actions · Answer 3

Instead of:

declare @id varchar (10)

declare id_cur cursor for

select id

from redundantdata

open id_cur

fetch next from id_cur into @id

while @@fetch_status = 0

begin

delete USER_INSTALL where id = @id

delete USER_JOB where id = @id

delete USER_log where id = @id

fetch next from id_cur into @id

end

close id_cur

deallocate id_cur

go

Try:

DELETE FROM USER_INSTALL WHERE ID IN (SELECT DISTINCT ID FROM RedundantData)

DELETE FROM USER_JOB WHERE ID IN (SELECT DISTINCT ID FROM RedundantData)

DELETE FROM USER_LOG WHERE ID IN (SELECT DISTINCT ID FROM RedundantData)

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Simon_L SSCarpal Tunnel Points: 4669 More actions · Answer 4

Garadin (11/19/2008)
Instead of:
declare @id varchar (10)
declare id_cur cursor for
select id
from redundantdata
open id_cur
fetch next from id_cur into @id
while @@fetch_status = 0
begin
delete USER_INSTALL where id = @id
delete USER_JOB where id = @id
delete USER_log where id = @id
fetch next from id_cur into @id
end
close id_cur
deallocate id_cur
go
Try:
DELETE FROM USER_INSTALL WHERE ID IN (SELECT DISTINCT ID FROM RedundantData)
DELETE FROM USER_JOB WHERE ID IN (SELECT DISTINCT ID FROM RedundantData)
DELETE FROM USER_LOG WHERE ID IN (SELECT DISTINCT ID FROM RedundantData)

bizarrely enought the approach Im just trying ... now youd think the results couldnt be different but they are.. so just going through it ...

thank you

simon

Garadin One Orange Chip Points: 29613 More actions · Answer 5

If RedundantData is constantly being written to, you could possibly have some issues with extra rows being deleted in the second or third delete statements that weren't deleted out of the first. Easily avoided by selecting the rows into a temp table before you delete and referencing that instead.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]