ariel_mlk (9/4/2012)

---

Hi people,

I'm tunning a database and i've stumbled by some hierarchy queries, on the good old form of ID, ParentID, to work out the hierarchy the previous developers used a like logic as in

SELECT TB1.* FROM TABLE1 as TB1 ON TABLE1 as TB2 on TB2.Hierarchy + '.' LIKE TB1.Hierarchy + '.%'

where hierarchy field contains the "path" to the parent nodes for example

1.2.3.4.5 would mean, this row is 5, parent is 4, grandparent is 3 and so forth

i've rewritten that in a CTE, and the query plan shouts out at me that it takes something like 25% of the cost in batch when running the rCTE approach and the like approach ( which would lead me to think that the rCTE is by far faster than the original ) but the like clause seems to take way lesser reads, i've read somewhere that the query plan only looks at the cost of a first execution of a rCTE, but as I understand rCTE were meant for jobs like this one, so how can a nasty non-sargable like go by less reads than a rCTE ?

sry if the post is not clear enough,

I haven't posted the query plans in the belief that i'm just misunderstanding something, if it ain't the case, i'll promptly post those