Prev: SP parameter optional
Next: DBTYP.NET Studio 2010 - Database Comparison Suite Released
From: --CELKO-- on 10 Mar 2010 17:10
Infinite recursion is usually an error. Did you mean to have cycles?
You can add constraints in the nested sets model to prevent then, but
you have to use triggers in the adjacency list model.
From: tshad on 10 Mar 2010 17:19

"alen teplitsky" <alen.teplitsky.dba(a)gmail.com> wrote in message
news:eaf8160f-36fc-49b2-9486-5f3dedf85633(a)q16g2000yqq.googlegroups.com...
On Mar 10, 1:55 pm, Plamen Ratchev <Pla...(a)SQLStudio.com> wrote:
> You can add path for the IDs and based on that define expression to detect
> cycles. Here is how it may look (the cycle
> column expression is used to identify cycles and stop from exploring any
> paths with cycles):
>
> WITH MultipleAnchorRowsCTE
> AS
> (
> SELECT ParentID, ID, 0 AS level, 'N' AS cycle,
> CAST('.' + CAST(id AS VARCHAR(8)) + '.' AS VARCHAR(MAX)) AS cte_path
> FROM @MultipleAnchors
> WHERE ParentID IS NULL
> UNION ALL
> SELECT children.ParentID, children.ID, level + 1,
> CASE WHEN macte.cte_path LIKE '%.' + CAST(children.id AS VARCHAR(8)) +
> '.%'
> THEN 'Y'
> ELSE 'N'
> END,
> CAST(macte.cte_path + CAST(children.id AS VARCHAR(8)) + '.' AS
> VARCHAR(MAX))
> FROM @MultipleAnchors AS children
> JOIN MultipleAnchorRowsCTE AS macte
> ON macte.ID = children.ParentID
> AND macte.cycle = 'N'
> )
> SELECT ParentID, ID, Level
> FROM MultipleAnchorRowsCTE;
>
> --
> Plamen Ratchevhttp://www.SQLStudio.com

> i think i did something like this a year or so ago. made it faster
> than the sp we're using in production for the last 10 years. you may
> have to remind me to find it

What I found was that the procedure I was working on had been done with
recursive procedures. The way they handled the infinite problem was to use
a temporary table to store the IDs as they were found them and would use a
boolean to go through each set of lists, when done they would delete the
records and start again with the new list.

This took about 10 seconds with a 2,400 row table

After doing the CTE, it took 1-2 seconds. When I added in Plamens technique
the performance was the same (1 -2 seconds).

Tom


First  |  Prev  | 
Pages: 1 2
Prev: SP parameter optional
Next: DBTYP.NET Studio 2010 - Database Comparison Suite Released