Is there any way to get rid of recursive in terms of performance. My query took almost 3 to 4 mins for huge recursion. Kindly guide me in this view.
By Nagendran 0 asked Sep 16, 2014 at 07:13 AM
Your main problem is the way you have coded the hierarchy in the SQL code. If you're able to afford it, I encourage you to build Joe Celco's book SQL For Smarties, which includes a lot of the best patters around navigating a hierarchy of data within a relational database.
There is a shorter exposition on this topic at http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html. But I worry that link is not endorsed by the author.
In any event, you need to refactor your code to use a more effective self-join and avoid both the CTE and the MAXRECURSION option.
Hope that helps,
By Kevin 141 answered Sep 22, 2014 at 06:38 PM