Slowly Executing procedure

[krotha]

Hi

alter procedure dbo.delete_nod
@table_name nvarchar(255),
@pk_fld_id nvarchar(255),
@pk_value numeric,
@project_id numeric
AS

declare @delete_tree_node bit
Begin
select @delete_tree_node = isnull(delete_tree_node,0)
from projects where project_id = @project_id

End

print @table_name

if (@table_name ='State' and @pk_fld_id = 'State_Id' and @delete_tree_node = 1)
Begin

Begin Transaction
Begin
delete from mytable where id in(select id from table1 where rid in(select rid from
table2 where sid in(select sid from table3 where tid = @pk_value )))

End
if @@error <> 0
Begin

goto Error_Routine
End

-- I have multiple delete statements here
Commit Transaction
End

Return
Error_Routine:
Rollback Transaction

GO
delete_tree_node 'Class','Id',6177,20
when I run this procedure with above parameters it takes long time to run. I am checking the condition if the parameter is state then only go to deletes block. but here I am passing class it takes so long time to print the variable. if I commented out the deletions block, it executes in 0 seconds. Why it is taking so much time to execute when I don't pass State.

Any help would be appreciated

 

[ESquared]

Have you just tried your delete query as a select query to see how long it runs?

The IN() operator is expanded to a series of ORs by the query engine. It's possible that changing to JOINs would help.

delete from mytable where id in(select id from table1 where rid in(select rid from
table2 where sid in(select sid from table3 where tid = @pk_value )))

DELETE T0
   FROM T3
      INNER JOIN T2 ON T3.sid = T2.sid AND tid = @pk_value
      INNER JOIN T1 ON T2.rid = T1.rid
      INNER JOIN MyTable T0 ON T1.rid = T0.id

(try this one as a select first, too)

-------------------------------------
There are 10 kinds of people in the world: those who know binary, and those who don't.

 

[krotha]

Thanks ESquared for your response. I tried using joins as you suggested but still it takes same time. first of all it should not go to delete section since I am passing different parameter. Am I doing something wrong in the if statement?

I tried the select too, it takes no time to execute.

 

[ESquared]

Do you have triggers running on deletion?

-------------------------------------
There are 10 kinds of people in the world: those who know binary, and those who don't.

 

[krotha]

No I don't have any triggers on those tables.