how to make this procedure work faster

[Kobrowsky]

hi!
this procedure takes app. 7 minutes to calculate all the levels when there are 4000 members under a sponsor alltogether in all the branches. how to make it work faster?

if exists (select name from tempdb..sysobjects where name = '##PregledTock')
drop table ##PregledTock

select NAZIV as Member , FIELDSA as Sponzor, FIELDNA as BT, FIELDNB as TT INTO
##PregledTock from SUBJEKT
where FIELDSA = '3861900019'

alter table ##PregledTock
add ID int
go
update ##PregledTock
set ID = 1

begin
declare
@id int,
@Member char(30),
@sponzor char(30),
@BT int,
@TT int

set @id = 1
while exists (SELECT Member FROM ##PregledTock where ID = @id)

begin
set @id = @id+1
declare crKurzor cursor local
for
select NAZIV, FIELDSA, FIELDNA, FIELDNB from SUBJEKT
where FIELDSA in (SELECT Member FROM ##PregledTock where ID = @id-1)

open crKurzor
fetch next from crKurzor into @Member, @sponzor, @bt, @tt
while @@fetch_status = 0
begin
insert into ##PregledTock values (@Member, @sponzor, @bt, @tt, @id)
fetch next from crKurzor into @Member, @sponzor, @bt, @tt
end

close crKurzor
deallocate crKurzor
end

end

update SUBJEKT
set FIELDNE = FIELDNA+(select sum(BT) from ##PregledTock), FIELDNF = FIELDNB + (select sum(TT) from ##PregledTock)
where NAZIV = '3861900019'

 

[nikhilparchure]

1)CurSors are a major mess and take heavy resources while could be happening in your case,please find articles for ways in which Cursors could be avoided.

2)Why is the need for a global temporary table???

 

[SQLSister]

Am I grasping corrctly that you are dealing with hierarchial data here? THere are ways to avoid having to curse through the dat if so, there are some links to articles on how to set up you hierarchial structure so that it is easier to query in old threads. Search fo hierachial data in the tek-tips search and you should get some ideas.