Thanks, it's so much easier when you have sample data to play with.

As Kevin pointed out, this isn't the most efficient type of hierarchy to return results from, and if you have the resources you should consider the alternatives. If not, then a couple of new indexes should give you a decent performance lift:

-- Change column parent_comp_id to be not nullable

ALTER TABLE comp ALTER COLUMN parent_comp_id VARCHAR(24) NOT NULL

-- Two new indexes:

CREATE UNIQUE NONCLUSTERED INDEX [ix_HierarchyA] ON [dbo].[comp]

(parent_comp_id ASC, comp_id ASC)

INCLUDE

(comp_code, [name], company_code)

CREATE UNIQUE NONCLUSTERED INDEX [ix_HierarchyB] ON [dbo].[comp]

(comp_id ASC, parent_comp_id ASC)

INCLUDE

(comp_code, [name], company_code)

The subquery for returning the level5 details looks like trial and error coding and is difficult to make sense of:

SELECT TOP 1

L.comp_id,

L.comp_code,

L.name

FROM dbo.comp L

INNER JOIN dbo.comp tc

ON tc.comp_id = L.parent_comp_id

AND tc.company_code = L0.company_code -- outer reference

INNER JOIN dbo.comp_chain bc

ON bc.comp_id = L.comp_id

AND bc.chain_comp_id = L0.comp_id -- outer reference

WHERE tc.parent_comp_id IS NULL OR tc.parent_comp_id = '0'

ORDER BY L.comp_id

Can you explain what it's meant to do?