Изменения ускоряющие запрос (без изменения алгоритма):
1. Заменяем временную таблицу в базе данных на таблицу в памяти, да и поддержка целостности не нужна:

    declare @ChildNodeList table ( ID_NODE int, LEV int )

2. Изменяем запрос в цикле перенеся ограничения выборки из директивы WHERE в директиву FROM, т.к. вложенные запросы в WHERE выполняются для каждого кортежа в таблице существенно тормозя:

INSERT @ChildNodeList
SELECT DISTINCT N.ID_NODE, @lev 
  FROM NODE N
  inner join @ChildNodeList CNL on CNL.ID_NODE = N.ID_PARENT
  left outer join @ChildNodeList CNL2 on CNL2.ID_NODE = N.ID_NODE
WHERE CNL2.ID_NODE is null

Вот и все. На первый взгляд.
Желаю удачи!