Loading ...
Sorry, an error occurred while loading the content.
 

treequery help

Expand Messages
  • vallurinaveen
    hi friends i wrote one stored procedure. But how to improve performance of this stored procedure. i paste the code here. how to alter this stored procedure
    Message 1 of 2 , Nov 1, 2004
      hi friends
      i wrote one stored procedure.
      But how to improve performance of this stored procedure.
      i paste the code here. how to alter this stored procedure working as
      a faster.Bcz this stored proc taken much time when records will be
      increase.
      pls help me urgently

      create PROCEDURE spAM_GetDownline

      @membid varchar(30),
      @pagesize int,
      @pageno int

      AS

      BEGIN

      SET QUOTED_IDENTIFIER OFF
      SET NOCOUNT ON

      declare @recordslimit int
      declare @recordstart int
      declare @recordcount int

      set @recordslimit = @pagesize * @pageno
      set @recordstart = @recordslimit - @pagesize
      set @recordcount = 0

      create table #tempmemtree(memberid varchar(30) primary
      key,lev int)
      create table #tempmemlist(memberids varchar(255) primary
      key)

      insert into #tempmemlist values(@membid )

      declare @tempid varchar(30)
      declare @level int
      set @level =0

      declare @flag int
      set @flag = 0

      declare @cnt int

      while (@flag = 0)
      begin
      declare mycursor cursor static for

      select memberid from master where introducer in
      (select memberids from #tempmemlist)

      OPEN mycursor

      delete from #tempmemlist

      set @level =@level + 1
      set @cnt = 0

      FETCH NEXT FROM mycursor INTO @tempid

      while (@@FETCH_STATUS = 0 )

      BEGIN
      set @recordcount= @recordcount +1

      if @recordcount > @recordstart
      insert into #tempmemtree
      values(@tempid,@level )

      if @recordcount = @recordslimit
      begin
      set @flag=1
      goto label1
      end

      insert into #tempmemlist values(@tempid)

      set @cnt = @cnt + 1

      FETCH NEXT FROM mycursor INTO @tempid
      END
      label1:

      CLOSE MyCursor
      DEALLOCATE MyCursor
      if (@cnt = 0)
      set @flag = 1
      end

      --Return the Results
      select lev,t.memberid, name,introducer,intuby,
      appl_date,paiddata,position,rece_date,rece_no from
      #tempmemtree t, master m
      where t.memberid = m.memberid
      order by lev
      END

      thanks
      naveen
    • hansve2000
      As this must be SQL Server, read Books Online article titled Expanding Hierarchies . Hans. ... as
      Message 2 of 2 , Nov 1, 2004
        As this must be SQL Server, read Books Online article
        titled "Expanding Hierarchies".

        Hans.

        --- In SQLQueriesNoCode@yahoogroups.com, "vallurinaveen"
        <vallurinaveen@y...> wrote:
        >
        >
        > hi friends
        > i wrote one stored procedure.
        > But how to improve performance of this stored procedure.
        > i paste the code here. how to alter this stored procedure working
        as
        > a faster.Bcz this stored proc taken much time when records will be
        > increase.
        > pls help me urgently
        >
        > create PROCEDURE spAM_GetDownline
        >
        > @membid varchar(30),
        > @pagesize int,
        > @pageno int
        >
        > AS
        >
        > BEGIN
        >
        > SET QUOTED_IDENTIFIER OFF
        > SET NOCOUNT ON
        >
        > declare @recordslimit int
        > declare @recordstart int
        > declare @recordcount int
        >
        > set @recordslimit = @pagesize * @pageno
        > set @recordstart = @recordslimit - @pagesize
        > set @recordcount = 0
        >
        > create table #tempmemtree(memberid varchar(30) primary
        > key,lev int)
        > create table #tempmemlist(memberids varchar(255) primary
        > key)
        >
        > insert into #tempmemlist values(@membid )
        >
        > declare @tempid varchar(30)
        > declare @level int
        > set @level =0
        >
        > declare @flag int
        > set @flag = 0
        >
        > declare @cnt int
        >
        > while (@flag = 0)
        > begin
        > declare mycursor cursor static for
        >
        > select memberid from master where introducer in
        > (select memberids from #tempmemlist)
        >
        > OPEN mycursor
        >
        > delete from #tempmemlist
        >
        > set @level =@level + 1
        > set @cnt = 0
        >
        > FETCH NEXT FROM mycursor INTO @tempid
        >
        > while (@@FETCH_STATUS = 0 )
        >
        > BEGIN
        > set @recordcount= @recordcount +1
        >
        > if @recordcount > @recordstart
        > insert into #tempmemtree
        > values(@tempid,@level )
        >
        > if @recordcount = @recordslimit
        > begin
        > set @flag=1
        > goto label1
        > end
        >
        > insert into #tempmemlist values(@tempid)
        >
        > set @cnt = @cnt + 1
        >
        > FETCH NEXT FROM mycursor INTO @tempid
        > END
        > label1:
        >
        > CLOSE MyCursor
        > DEALLOCATE MyCursor
        > if (@cnt = 0)
        > set @flag = 1
        > end
        >
        > --Return the Results
        > select lev,t.memberid, name,introducer,intuby,
        > appl_date,paiddata,position,rece_date,rece_no from
        > #tempmemtree t, master m
        > where t.memberid = m.memberid
        > order by lev
        > END
        >
        > thanks
        > naveen
      Your message has been successfully submitted and would be delivered to recipients shortly.