Thanks for the reply Chris,

I have already posted some sample data in my earlier post. Due to some legal restrictions, I can't post more data.

I have executed the code and results are as below:

1. As you Optimized

(89090 row(s) affected)

Table 'comp'. Scan count 199405, logical reads 907812, physical reads 83, read-ahead reads 873, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'comp_chain'. Scan count 89090, logical reads 268582, physical reads 445, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

2. After changing a line in your code

--WHERE top_comp.parent_comp_id IS NULL OR top_comp.parent_comp_id = '0'

where isnull(top_comp.parent_comp_id, '0') ='0'

(89090 row(s) affected)

Table 'comp'. Scan count 1, logical reads 604512, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'comp_chain'. Scan count 89090, logical reads 268582, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

Original IO statistics

Table 'comp'. Scan count 1, logical reads 637983

New IO statistics

Table 'comp'. Scan count 1, logical reads 604512