January 13, 2008 at 7:39 am
Can someone tell me if there is any benefit performance wise to using CTES for recursive queries
over previous methods like stacks (eg the one shown in BOL).
I am currently going through a system thats just moved to 2005 trying to rewrite any code I think
could do with a tweak. The system currently uses TVF's that build
the hierarchal data up in a while loop stack. These functions are referenced and used in many places throughout the system.
Is there any point in rewriting either the functions so that they use CTES to return the data OR in the calling code replacing calls to the functions all together with the CTEs. I've read that basically they are just a nice syntaxical way of writing recursive queries but under the covers they would be doing the same thing. At the moment the code is all in one place in the function and I don't want to even bother thinking about changing anything if theres no benefit to the system performance wise.
Thanks for any advice in advance.
January 14, 2008 at 5:14 am
The most immediate benefit you'd see from using the CTE vs. a function is that the CTE will take advantage of indexes within the system as part of it's operation. Depending on how you're using your functions, if the tables generated are used to look up more data or join to themselves or other tables, then, because they don't generate statistics, they could be very slow when compared to CTE's. Testing is going to be the only way to be sure. As a general rule, except when dealing with small scale data, I stay away from table valued functions.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 15, 2008 at 8:37 am
I had the same thought when I upgraded to SQL Server 2005.
I have a TVF to return just the "key" data in my hierarchical tree structure. It uses the stack code as published by Microsoft.
I experimented using a CTE and found that I was getting much better performance from my TVF. In addition, the lack of a sorting capability for the intermediate nodes (which my TVF does) killed any use of a CTE. Oh how I really could use Oracle's CONNECT BY... in SQL Server!
See more details in my earlier post:
http://www.sqlservercentral.com/Forums/Topic366130-8-1.aspx#bm366402
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply