February 3, 2015 at 10:54 am
What version of MSSQL were you using during the analysis? MSSQL 2014 is a lot more efficient with cursors, but want to know if that is incorrect from your experience. Thanks in advance.
February 3, 2015 at 11:26 am
Did you look at using a SQL Server CTE with its ability to recurse?
That actually does a very fast loop in memory and is close to a set based operation.
SQL Server has a way of optimizing that behind the scenes in ways we cannot write by hand.
I'm not providing an example, but it occurs to me that this might be a good way to approach this.
Look up recursion in CTEs. It involved joining a two queries in the CTE where the second query joins to the CTE. Counter-intuitive, but works.
February 3, 2015 at 11:27 am
Chris,
thanks for a good observation about a cursor use...
I think even in the case you described, you can resolve an issue with a set based query.
I would be thankful if you can provide an example where you think a cursor is only the only one choice.
thanks
February 3, 2015 at 11:31 am
Recurse ?
I am weary of recursion. I view it as meant to be used for special cases. I have seen a demo concept of calculating factorials with recursion being abused and used well outside its intended purpose - an easy to follow example but in reality atrociously inefficient as compared to a simple loop.
The first time I tried recursion in T-SQL (SQL Server 2000) there was a hard limit of 32 levels and CTE did not exist.
February 3, 2015 at 11:46 am
Think STUFF is not a very clean way to convert XML dataype to varchar, so,
why not use cast( ... as varchar(max)), or, consistent with xml datatype, xml_thing.value ?
February 3, 2015 at 12:14 pm
SQL Server has come a long way since SQL Server 2000.
I think CTE didn't even come along until 2005.
You are dismissing it out of hand without researching or trying it. Believe me, it can be very, very fast.
February 3, 2015 at 12:22 pm
I think you missed my point.
What I am dismissing is applying what amounts to a sledgehammer when the issue was to drive a 1 inch nail in a 2 X 4.
However efficient SQL Server might have become, it remains a stupid idea to use recursion to calculate factorials instead of a plain loop.
My point is that the Recursive Factorial example is often shown without any caveat that this is for illustrative purpose only because of the overhead.
And yeah, I have moved up to SQL Server 2012, still learning about the extras that came with it.
Did not ostracize recursion altogether, I never encountered again the need for it since the last time I used it.
February 3, 2015 at 12:41 pm
Ah, yes, but things have evolved light years since SQL Server 2000.
CTE's recursion is NOT a sledgehammer.
With SQL Server 2008 or later (and CTE existed in 2005, but not sure about the recursion), try:
declare @target int
set @target=10;
WITH N AS
(SELECT 1 AS i,
1 AS f
UNION ALL
SELECT i+1,
f*(i+1)
FROM N
WHERE i < @target
)
SELECT f FROM N
WHERE i=@target
February 3, 2015 at 12:45 pm
Things have evolved light years since SQL Server 2000.
CTE's recursion is an ELEGANT, SIMPLE, FAST solution, NOT a sledgehammer.
With SQL Server 2008 or later (and CTE existed in 2005, but not sure about the recursion), try:
declare @target int
set @target=10;
WITH N AS
(SELECT 1 AS i,
1 AS f
UNION ALL
SELECT i+1,
f*(i+1)
FROM N
WHERE i < @target
)
SELECT f FROM N
WHERE i=@target;
Note: have to have a semicolon before the WITH statement, so one might have to be added if no previous statement ending in ";".
Also, this doesn't work with @target = zero, but that could be blocked in a stored proc by code that checks input params. I do not think there is a limit except by the numeric data type used.
February 3, 2015 at 12:53 pm
Thanks for the short and sweet example on how to do the recursion with CTE's.
That is useful. Now this will guide me in understanding how to do this.
BUT it is more complex than a simple while loop for such a simple case. Someone with less experience than you (you came up with this example in what - a matter of minutes ?) picking up the recursive code will have to work a lot harder until the method is understood.
Just saying, in trivial cases, recursion just might not be the most appropriate solution. And there are indeed complex cases that will make recursion shine.
Regards
February 3, 2015 at 12:59 pm
I just used google. Anyone can do that.
February 3, 2015 at 2:01 pm
Be careful about concatenating strings in a SELECT statement:
DECLARE @X varchar(2000) = ''
DECLARE @Y varchar(2000) = ''
DECLARE @T TABLE (I varchar(10), O varchar(10))
SET NOCOUNT ON
INSERT @T SELECT 1,'A'
INSERT @T SELECT 2,'B'
INSERT @T SELECT 3,'C'
INSERT @T SELECT 4,'D'
INSERT @T SELECT 5,'E'
INSERT @T SELECT 6,'F'
INSERT @T SELECT 7,'G'
INSERT @T SELECT 8,'H'
INSERT @T SELECT 9,'I'
INSERT @T SELECT 10,'J'
SET NOCOUNT OFF
SELECT @X = @X + O
FROM @T
ORDER BY I
SELECT @Y = @Y + O
FROM @T
ORDER BY CONVERT(int,I)
PRINT @X
PRINT @Y
February 3, 2015 at 2:14 pm
I don't really see why you would ever need to use a cursor in this situation. If I'm correct in reading your initial description there is a simple hierarchical relationship between the dept (owner) and the assigned (child) tasks.
In order to return the owner id (say) and all child instruction data serialized into a comma-delimited string, just use
[font="Courier New"]SELECT owner.id,stuff(
(SELECT ',' + child.instructions
FROM child
WHERE child.ownerid = owner.id
FOR xml path (''), TYPE).value('.','varchar(max)'),1,2,'') AS AllInstructions
FROM owner
ORDER BY etc, etc[/font]
At first glance this is a tad cryptic. Basically it just packs all child nodes into a single string of type varchar(max). Adjust the Varchar dimension according to your own needs.
You need the ",1,2" construction on the value property of the dynamically built node to slice off the leading comma delimiter. Of course you could just use a blank as delimiter and the push the result through a left trim (or something...choice is yours).
Cue flood of complaints about inefficient memory usage of the XML libraries...
Can't be worse than using a cursor can it?
Discuss
Richard
February 3, 2015 at 4:09 pm
Of course you could use ISNULL to convert NULL to '' (empty string).
February 3, 2015 at 6:27 pm
Good post, I'm often getting the team to steer clear of evil cursors.
I have a similar script to identify all objects (in a given db) and any job steps that have the word "CURSOR" in their definition. Hopefully someone will find this useful.
/*
Script identifies objects (in selected database) and jobs containing
"CURSOR"
Optimise database performance by eliminating CURSORS. If that is not an
option consider using LOCAL AND/OR FORWARD_ONLY arguments in cursor.
Remember to get baseline stats in Production and then make
changes in test. Compare results before rolling out to Production.
Steps:
1. Select database.
2. Run
*/
/*CHECK JOB STEPS*/
SELECT '---JOBS CONTAINING "CURSOR"---' AS [Information]
SELECT
[STEP].job_id,
[JOB].name,
[STEP].step_name
FROM
msdb.dbo.sysjobsteps [STEP]
INNER JOIN msdb.dbo.sysjobs [JOB] ON [STEP].job_id = [JOB].job_id
WHERE
[STEP].command LIKE '%CURSOR%'
/*CHECK OBJECTS INCLUDING DDL TRIGGERS (NOT STORED IN sys.objects (HENCE UNION WITH SECOND QUERY)*/
SELECT '---OBJECTS CONTAINING "CURSOR" IN [' + DB_NAME() + ']---' AS [Information]
SELECT object_id, OBJECT_NAME(object_id) AS [object_name], type_desc FROM sys.objects WHERE OBJECT_DEFINITION(object_id) LIKE '%CURSOR%' UNION
SELECT object_id, OBJECT_NAME(object_id) AS [object_name], type_desc FROM sys.triggers WHERE OBJECT_DEFINITION(object_id) LIKE '%CURSOR%'
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply