|
I entered this challenge on beyondrelational. With the sample data of about 300 rows my solution ran in 0 ms so I thought I had something, but alas the load testing showed it took about 8000ms. Looking at the winners, my solution is almost identical to Stefan's solution below except mine has a replace and I do not do the "pseudo recursion" How can mine be 8 seconds slower than this, can anyone shed light on what the pseudo-recursion is doing? i will not be <0 so it will only return the anchor part, how is this processed differently than a generic cte? Even without it, shouldn't the patindex only be executed once for each row during the table scan? I am floored at the discrepancy of 8 seconds. Further it shows mine having 4000 writes but Stefan's only 41, something doesn't seem right for how close our solutions are
(comments are locked)
|
|
It is my understanding that Stefan's "Pseudo recursion" trick makes the engine store the results of the cte query ( a bit like using a temp table without any indexes ) for re-use rather than re-processing the underlying query - however I cannot see how it helps in this query! Ask Stefan to explain is my advise. Definitely a good trick to have in your bag if it makes that big of a difference. I will have to dig into this deeper.
Feb 02 '11 at 04:03 PM
Scot Hauder
I've tried it a few times and have yet to see a benefit on my code - It would be great to understand a bit more about when it helps!
Feb 02 '11 at 04:04 PM
Mister Magoo
I would hope it doesn't materialize the cte for every row referenced in the main select, or we should never use them. I have seen some ugly performance with simple ctes like this so I have been avoiding them unless I need recursion. Instead of the recursion trick I will have to see if just using a derived table has the same effect.
Feb 02 '11 at 04:19 PM
Scot Hauder
Now, the really big WOW! for me is in the winning solution - the ORDER BY. If I implement the same order by on my solution it comes out identical to the winner...I would re-submit but that seems mean spirited...
Feb 02 '11 at 04:48 PM
Mister Magoo
(comments are locked)
|

