February 3, 2015 at 7:06 pm
Useful script, but would you ever see a trigger with a CURSOR in it?
No one in their right mind would ever do such a thing, right?
Of course we have seen some crazy stuff in dbs created and modified by others.
February 3, 2015 at 7:59 pm
Ha, ha.. You're right, but if it's possible, don't assume it's not there. There is indeed some VERY crazy stuff out there. 🙂
February 4, 2015 at 1:49 am
SQL Server CTEs are not bad recursion, because they are not recursion at all.
Admitted, they are written as recursion, but they are a loop.
To get this clear, consider the following:
step n+1 does not access all the previously generated rows, but only the
rows generated at step n
in real recursion step n+1 builds upon the WHOLE RESULT of step n,
this is rows generated in steps 1 thru n.
So CTEs by nature are not recursion, but a loop.
February 4, 2015 at 5:04 am
In the case of the Fibonacci series calculation (or factorials -- can be done same way), you are right about them winding up being a loop "behind the scenes". Not sure same can be said for queries that pull from database. It's a moot point, anyway, unless one really wants to find out -- look at the execution plan, because it's fast as heck.
February 4, 2015 at 7:38 am
To h.tobish:
I very much appreciate your informative comment about the CTE "recursion" not really being an actual recursion.
February 4, 2015 at 10:16 am
I disagree that is the difference between recursion and loops or iteration.
"step n+1 does not access all the previously generated rows, but only the
rows generated at step n.
in real recursion step n+1 builds upon the WHOLE RESULT of step n,
this is rows generated in steps 1 thru n."
In CTE, this kind of query is called recursion; however, you're right in that it is executed as a kind of loop. The term recursion in math and programming was created referring to a function calling itself, and is very good at navigating a tree from root (or from any specific node) down to branches (think upside down "tree"), such as the way "folders" are organized on a PC. So, in the case of the fibonacci series, which is only "querying" pure numbers, not data from a database, one might say that is a loop, and probably behind the scenes the optimizer recognizes this and implements it as a loop. But the second SELECT in the UNION ALL query is referencing the COMMON TABLE EXPRESSION that it is part of (which is actually a table built in memory), so actually it IS recursion.
It doesn't really matter to us developers whether it is a loop or true recursion, because we only have to deal with the results and performance. It really only matters to the coders behind the SQL engine and to mathematicians and academics who want to discuss or teach theory.
February 8, 2015 at 11:53 am
Hi, there are differences between engine sql server 2005 and 2008 to execute a stored procedure with a cursor, because I note execution times much faster with the 2005 version despite having followed his advice.
Thank You
February 8, 2015 at 1:59 pm
s.greco (2/8/2015)
Hi, there are differences between engine sql server 2005 and 2008 to execute a stored procedure with a cursor, because I note execution times much faster with the 2005 version despite having followed his advice.Thank You
Perhaps it's the SQL Server version (mine was 2008 R2) or perhaps it's just a case where you've found the exception to the rule. As with everything else with SQL Server, it's best to test in your environment with your apps and make a decision based upon empirical testing.
By the way, which advice did you follow? Local and fast forward or rewriting into set base query?
February 8, 2015 at 7:22 pm
It would be good to see what queries you are comparing and what the physical infrastructure of the two servers.
Also, it would be good to know whether things like TempDB and Log files being located an a different set of hard drives on each server or not, cpu differences, memory differences, etc. Even if the two SQL Server instances are on the same physical server, the above factors as well as others can make huge difference.
November 14, 2016 at 8:43 am
Thanks for the good article.
Viewing 10 posts - 31 through 39 (of 39 total)
You must be logged in to reply to this topic. Login to reply