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