Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Stairway to Database Design Level 8: Cursors Expand / Collapse
Author
Message
Posted Tuesday, August 9, 2011 4:16 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 12:29 PM
Points: 1,945, Visits: 3,121
Comments posted to this topic are about the item Stairway to Database Design Level 8: Cursors

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1156651
Posted Wednesday, November 2, 2011 3:40 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 1:17 PM
Points: 866, Visits: 600
How about - using while loops and variables to avoid cursor overhead but still handle row based processing?

If you can tell me how to do database mail for producing emails to a set of customers (based on data in SQL server) without using row by row processing (either cursors or while loops) that would be very helpful.
Post #1199152
Posted Wednesday, November 2, 2011 4:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 10, 2014 8:39 AM
Points: 23, Visits: 39
Nothing wrong with the article itself but I think it needs to be acknowledged that there are some things that are most easily done with cursors. As long as the performance is OK, whats the problem? Its about delivering robust functionality to users in the minimum time possible.

I try and do everything in the simplest and most robust way possible. By simplest, that means understandable by people who may be less familiar with T-SQL than me and without huge statements and unnecessary code written simply to conform to a somewhat arbitrary design guideline.

If I think the best way is set-based I'll do it, and the same for row-based.

Free thinking is important as is the confidence to step outside recieved opinion when that gets better results quicker.

Dont mean to be too negative as this has been a valuable series of articles.
Post #1199169
Posted Wednesday, November 2, 2011 4:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 13, 2014 1:58 AM
Points: 7, Visits: 109
Once you get your head around using set based SQL you'll hardly use cursors.

It's nice to have this option but I would use a while loop/variables if I really need to loop row by row (itch, itch , scratch)

Nice article nevertheless.

Post #1199179
Posted Wednesday, November 2, 2011 5:48 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:33 AM
Points: 832, Visits: 1,593
I started in current job about 18 months ago bringing with me a nascent aversion for cursors and while loops. I was instructed in the first week to write something with a cursor; something that really just needed a join to a calendar table. The rationale was that a cursor would only take "a few" more seconds to run and was a lot easier to read. I secretly disobeyed this instruction and generally have done ever since.There has almost always been a faster set-based solution.

Having said that, we do now have the luxury of working with versions of SQL Server that are newer than SQL 2000, and I think some of the improvements to later versions of SQL have made it far easier to avoid cursors and loops. It's therefore a bit rich for me to sneer down my patrician nose at the code that I've inherited. Much of it was written ten or a dozen years ago. It seems to me the SQL community's philosophy and collective knowledge in relation to cursors and their alternatives has changed a lot in that time.

What I can't get my head around though, is the belief that cursors are easier to read. Maybe I write too much SQL and not enough procedural code (that is to say, roughly none) but I find set-based TSQL waaaaay easier to read than cursor-based TSQL. As for "only a few more seconds", sorry, but I've had to stand beside busy company directors while they've waited 45 seconds for a report that should have only taken 5 seconds to run. It's excruciating. Do that a few times and you'll find a way to avoid that cursor!






One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important.
Bertrand Russell
Post #1199219
Posted Wednesday, November 2, 2011 6:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 14, 2011 2:19 PM
Points: 1, Visits: 13
Hi all,
I am all for new knowledge. I have come to SQL from a programmer background and still find it hard NOT to think sequentially through a set. Recently, I had to find the 25th, 50th (median) and 75th percentiles (or 1st, 2nd, 3rd quartiles) for a box and whisker plot. After much digging I found a post from a guy using a cursor to find the median and tweaked it to give me the other two stats.

Very happy to hear of a more nifty way to do this. My code works nicely, and the performance is OK, but as I said, always happy to learn.

--Holding table for quartiles rows
CREATE TABLE #ExamStats (sub_code VARCHAR(4), Q0 decimal(5,2), Q1 decimal(5,2), Q2 decimal(5,2), Q3 decimal(5,2), Q4 decimal(5,2), mean decimal(5,2), NumRows int)
CREATE TABLE #ExamResults (
ID int IDENTITY(1,1)
, result decimal(5,2)
)

DECLARE @subjcode varchar(4), @q2 decimal(5,2)
, @mid decimal(5,2), @odd int, @mid1 decimal(5,2), @mid2 decimal(5,2)
, @NumRows int

DECLARE @q1max int, @q1 decimal(5,2), @q3 decimal(5,2), @q3min int
DECLARE @q0 decimal(5,2), @q4 decimal(5,2), @mean decimal(5,2)

DECLARE oCursor CURSOR FOR
SELECT distinct sub_code
FROM studresult
WHERE (cmpy_code = '01') AND (res_year = '2011') AND (res_period = '3') and (obj_code like 'SE%%') and ISNUMERIC(stud_result)= 1 --AND (sub_code = '8041')
ORDER BY sub_code asc

--Open said cursor and start stepping thru it
OPEN oCursor
WHILE 0=0 BEGIN
FETCH NEXT FROM oCursor INTO @subjcode
IF @@Fetch_Status<>0 BREAK
TRUNCATE TABLE #ExamResults

--Insert the data for a given age
INSERT INTO #ExamResults (result)
SELECT stud_result
FROM studresult
WHERE (cmpy_code = '01') AND (res_year = '2011') AND (res_period = '3') and (obj_code like 'SE%%') and ISNUMERIC(stud_result)= 1 AND (sub_code = @subjcode)
ORDER BY stud_result asc

--Now actually compute the median
SET @NumRows = @@ROWCOUNT
SET @odd = @NumRows % 2

--1 if odd number of recs, 0 if even
SET @mid = @NumRows / 2

--Number of the "middle" record
IF @NumRows = 1 BEGIN --Special trick so this works if only one record
SET @odd = 1
SET @mid = 1
END -- Which row(s) hold the median value?

IF @odd = 1 BEGIN
--Odd number of rows - median is the middle one
SET @q2 = (SELECT result FROM #ExamResults WHERE ID = @mid)

--To Reset Midpoints
SET @q1max = @mid - 1
SET @q3min = @mid + 1
END ELSE BEGIN
--Even number of rows - median is the average of the two middle ones
SET @mid1 = (SELECT result FROM #ExamResults WHERE ID = @mid)
SET @mid2 = (SELECT result FROM #ExamResults WHERE ID = @mid + 1)
SET @q2 = (@mid1 + @mid2) / 2

--To Reset Midpoints
SET @q1max = @mid
SET @q3min = @mid + 1
END

--Q1
SET @odd = @q1max % 2
SET @mid = @q1max / 2
if @q1max = 1 BEGIN
SET @odd = 1
SET @mid = 1
END
IF @odd = 1 BEGIN
SET @q1 = (SELECT result FROM #ExamResults WHERE ID = @mid)
END ELSE BEGIN
SET @mid1 = (SELECT result FROM #ExamResults WHERE ID = @mid)
SET @mid2 = (SELECT result FROM #ExamResults WHERE ID = @mid + 1)
SET @q1 = (@mid1 + @mid2) / 2
END
--Q3
SET @odd = (@NumRows-@q3min) % 2
SET @mid = ((@NumRows-@q3min) / 2) + @q3min
if (@NumRows-@q3min) = 1 BEGIN
SET @odd = 1
SET @mid = 1
END
IF @odd = 1 BEGIN
SET @q3 = (SELECT result FROM #ExamResults WHERE ID = @mid)
END ELSE BEGIN
SET @mid1 = (SELECT result FROM #ExamResults WHERE ID = @mid)
SET @mid2 = (SELECT result FROM #ExamResults WHERE ID = @mid + 1)
SET @q3 = (@mid1 + @mid2) / 2
END

SET @q0 = (SELECT MIN(result) FROM #ExamResults)
SET @q4 = (SELECT MAX(result) FROM #ExamResults)
SET @mean = (SELECT AVG(result) FROM #ExamResults)

INSERT into #ExamStats values(@subjcode, @q0, @q1, @q2, @q3, @q4, @mean, @NumRows)
END
CLOSE oCursor
DEALLOCATE oCursor

SELECT student.stud_code, student.surname, student.given_name, studresult.res_year, studresult.res_period,
student.year_grp, student.house, student.form_cls, student.pctut_grp, subtab.sub_long,
studresult.sub_code, studresult.stud_result, #ExamStats.Q0, #ExamStats.Q1, #ExamStats.Q2, #ExamStats.Q3, #ExamStats.Q4, #ExamStats.mean, #ExamStats.NumRows
FROM student INNER JOIN
studresult ON student.stud_code = studresult.stud_code AND student.cmpy_code = studresult.cmpy_code INNER JOIN
#ExamStats ON studresult.sub_code = #ExamStats.sub_code INNER JOIN
subtab on subtab.cmpy_code = student.cmpy_code and subtab.sub_code = studresult.sub_code
WHERE studresult.res_year = '2011' and studresult.res_period = 3 and (obj_code like 'SE%%') and student.cmpy_code = '01'
ORDER BY studresult.res_year, studresult.res_period, studresult.sub_code, student.stud_code

DROP TABLE #ExamStats
DROP TABLE #ExamResults

Post #1199249
Posted Wednesday, November 2, 2011 6:51 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 4:23 PM
Points: 415, Visits: 2,333
"This is called an impedance mismatch, a borrowed term from electronics that refers to parallel versus serial data transmission."

Mr. Celko shouldn't discuss topics he doesn't know about because he ends up looking pretty silly when he does.

Post #1199258
Posted Wednesday, November 2, 2011 6:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 10, 2014 8:39 AM
Points: 23, Visits: 39
I agree that using a cursor to avoid joining 2 tables is obviously wrong, and if a report takes 45 seconds because a cursor is used I too not be looking to use a cursor for that report.

But these are rather extreme examples. Cursors have their place as do set based operations.
Post #1199259
Posted Wednesday, November 2, 2011 7:36 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 6:20 AM
Points: 271, Visits: 415
patrickmcginnis59 (11/2/2011)
"This is called an impedance mismatch, a borrowed term from electronics that refers to parallel versus serial data transmission."

Mr. Celko shouldn't discuss topics he doesn't know about because he ends up looking pretty silly when he does.



This is kind of off-topic, but I raged a little inside when the only examples of procedural languages he came up with were COBOL, BASIC, and FORTRAN. As if functional programming consists of nothing but useless old dinosaurs whose names are written in scary giant letters.

Lisp, anyone?
Post #1199281
Posted Wednesday, November 2, 2011 8:13 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 4:23 PM
Points: 415, Visits: 2,333
aurato (11/2/2011)
patrickmcginnis59 (11/2/2011)
"This is called an impedance mismatch, a borrowed term from electronics that refers to parallel versus serial data transmission."

Mr. Celko shouldn't discuss topics he doesn't know about because he ends up looking pretty silly when he does.



This is kind of off-topic, but I raged a little inside when the only examples of procedural languages he came up with were COBOL, BASIC, and FORTRAN. As if functional programming consists of nothing but useless old dinosaurs whose names are written in scary giant letters.

Lisp, anyone?


SQL and RDBMS technologies solve a specific class of problems well and most database professionals realise this without needing to believe for instance that procedural languages are based on a "sequential file model," and Mr. Celko just looks a bit silly writing stuff like that. But I doubt he's going to change because of any criticism appearing on a web forum, so I'm not going to sweat it.
Post #1199302
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse