Why Is This Stored Procedure Slower Than Dynamic SQL?

We have a procedure that does some complicated table joins to populate a temporary table (declared in the proc) and then extraneous rows are removed from the table and the remaining rows are returned.

On the production server the procedure takes 22 seconds, but the identical query in a dynamic session window of SQL Server Management Studio takes 2 seconds. That doesn't make any sense.

We've profiled the procedure and discovered that the small loop at the end of the query runs through twice with each iteration taking 10 seconds. Again - the same section (but dynamic) profiles in at 0 seconds. We further narrowed it down to the compound delete statement from the @tblResults table.

Here's the looped section of code:


-- now delete the entries with no content

declare @deleted int

set @deleted = 1

declare @tmp table ([Id] int)

while @deleted <> 0

begin


delete from @tmp

-- get all entries with no child nodes

insert into @tmp ([Id])


select distinct r.[id] from @tblResults r

where r.[id] not in

(

select [Id] from @tblResults

where [Id] in (select distinct ParentId from @tblResults)

)


-- delete entries with no documents and region match
-- (this is where the slowdown occurs)

delete from @tblResults

where [id] in

(


select t.[id] from @tmp t where t.[id] not in


(select distinct dm.metatagid from documentmetatag dm

where dm.documentid in


(select distinct dr.documentid from documentregion dr, @region r

where (dr.regionid = r.regionId or @regionIds is null)

)

)

)


select @deleted = @@rowcount

end




We then return all rows from the @tblResults table.

We are totally stumped on why the procedure would take so much longer than running the same query in a SQL Manager window. Is there some issue with temporary tables as variables in a procedure? Any ideas anyone?

Thanks,
John