How to optimize the recursive query?

Nagendran 2014-09-16 07:13:02

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.

Kevin 2014-09-22 18:38:59
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,

-Kevin