查询数据的所有上级要怎么查才能快呢？有FullId 和ParentId

Question

select a.* from MaterialMaterialCategoryView a with(nolock) where 1=1 and LevelNo<=3 and exists (select * from MaterialALICGMaterialGoods b inner join MaterialMaterialCategory c on b.MaterialCategoryId=c.MaterialCategoryId and c.FullId like a.FullId+'%') order by a.MaterialCategoryId

目的就是有一组数据，现在需要关联外表查询外表的所有上级 有FullId 和ParentId

7秒才查询完毕，查出来的数据也才四五百条