Global filter

[otsizh]

We have a global filrer defined on the stored prosedure and used in as cascading prompt. the procedure is as fallow:

CREATE PROCEDURE [dbo].[usp_focus_rpt_get_businessstrategylist]
   (@IDs   varchar(500),
    @traversal_code   varchar(1))
AS
--
set nocount on
--
----------------------------------------------------------------------------------------------------------------------
--   if @IDs is blank or '-1' then all business_strategy_ids are returned
--   else
--      if @traversal_code = 'S' then only selected IDs are returned
--      if @traversal_code = 'D' then selected IDs and children are returned
--      if @traversal_code = 'U' then selected IDs and parents are returned
--      if @traversal_code = 'T' then selected IDs and children and parents are returned
----------------------------------------------------------------------------------------------------------------------
if (len(coalesce(@IDs, '')) = 0) or (rtrim(@IDs) = '-1')
begin
   select business_strategy_id from d_project_business_strategy
   return
end
--
declare   @rowsadded   int
create table #t
   (business_strategy_id   int,
    processed   tinyint default 0)
--
-- insert selected nodes
insert into #t (business_strategy_id)
select    business_strategy_id from d_project_business_strategy
where   business_strategy_id in
      (select element from dbo.udf_focus_parse_string(@IDs, ','))
--
-- insert children
if (@traversal_code = 'D') or (@traversal_code = 'T')
begin
   set @rowsadded = 1
   while @rowsadded > 0
   begin
      -- mark all records whose children are going to be found in this iteration, with processed = 1
      update #t set processed = 1 where processed = 0
      -- insert children for bu marked 1
      insert into #t (business_strategy_id, processed)
      select business_strategy_id, 0 processed from d_project_business_strategy
      where   parent_business_strategy_id in   (select business_strategy_id from #t where processed = 1) and
         parent_business_strategy_id <> business_strategy_id
      --
      set @rowsadded = @@rowcount
      -- mark all records whose children have been found in this iteration
      update #t set processed = 2 where processed = 1
   end
end
--
-- insert parents
if (@traversal_code = 'U') or (@traversal_code = 'T')
begin
   update #t set processed = 0 where business_strategy_id in (select element from dbo.udf_focus_parse_string(@IDs, ','))
   set @rowsadded = 1
   --
   while @rowsadded >0
   begin
      -- mark all records whose parent are going to be found in this iteration, with processed = 1
      update #t set processed = 1 where processed = 0
      -- insert parent
      insert into #t (business_strategy_id, processed)
      select parent_business_strategy_id, 0 processed from d_project_business_strategy
      where   business_strategy_id in   (select business_strategy_id from #t where processed = 1) and
         parent_business_strategy_id <> business_strategy_id
      --
      set @rowsadded = @@rowcount
      -- mark all records whose parent have been found in this iteration
      update #t set processed = 2 where processed = 1   
   end
end
--
select distinct business_strategy_id from #t
--
GO
when this filter is used in the report the performance is significant poor. Could anybody recomend how to rewrite this procedure in order to get better performance?

thanks, olha