How to optimize the recursive query?

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.

Plan.pesession (1.9 MB)
avatar image By Nagendran 0 asked Sep 16, 2014 at 07:13 AM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

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

avatar image By Kevin 141 answered Sep 22, 2014 at 06:38 PM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x641
x166
x109
x89
x46

asked: Sep 16, 2014 at 07:13 AM

Seen: 278 times

Last Updated: Sep 22, 2014 at 06:38 PM