Finding Top level parent of each row of a table [SQL Server 2008] -

i have following 2 tables

table person

id   name    1       2    b    3    c    4    d    5    e 

table relationhierarchy

parentid   childid    2         1    3         2    4         3 

this form tree structure

      d       |       c       |       b       |       

parentid , childid foreign keys of id column of person table

i need write sql can fetch me top level parent i-e root of each person.

following cte can each. converted function , ran each row of person. have got 3k rows in person table , takes 10 secs that. can suggest approach can take less. problem function runs following cte runs 3k times

declare @childid int  set @childid  = 1 --chield search  ;with rcte ( select *, 1 lvl relationhierarchy  childid = @childid  union  select rh.*, lvl+1 lvl dbo.relationhierarchy rh inner join rcte rc on rh.childid = rc.parentid  ) select top 1 id, name rcte r inner join dbo.person p on p.id = r.parentid order lvl desc 

i have updated answer in original question, never-mind, here copy also:

;with rcte (     select  parentid, childid, 1 lvl relationhierarchy       union      select rh.parentid, rc.childid, lvl+1 lvl      dbo.relationhierarchy rh     inner join rcte rc on rh.childid = rc.parentid ) ,cte_rn  (     select *, row_number() on (partition r.childid order r.lvl desc) rn     rcte r  ) select pc.id childid, pc.name childname, r.parentid, pp.name parentname dbo.person pc  left join cte_rn r on pc.id = r.childid ,  rn =1 left join dbo.person pp on pp.id = r.parentid 

sqlfiddle demo

note slight difference in recursive part of cte. childid rewritten each time anchor part. addition row_number() function (and new cte) top level each child @ end.

edit - version2

after finding performance issues first query, here improved version. going top-to-bottom, instead of other way around - eliminating creating of rows in cte, should faster on high number of recursions:

;with rcte (     select  parentid, childid, 1 lvl relationhierarchy r1     not exists (select * relationhierarchy r2 r2.childid = r1.parentid)      union      select rc.parentid, rh.childid, lvl+1 lvl      dbo.relationhierarchy rh     inner join rcte rc on rc.childid = rh.parentid ) select pc.id childid, pc.name childname, r.parentid, pp.name parentname dbo.person pc  left join rcte r on pc.id = r.childid left join dbo.person pp on pp.id = r.parentid  

sqlfiddle demo