May 27, 2011 at 11:15 pm
David Kuhl (5/27/2011)
battelofhalfwits (1/1/2009)
Wow...Two things
1) Never use Cursors! Looping through a temp table takes less overhead than a Cursor; especially when you are working on a 24/7/365 server where you really need to watch your resources.
I want to echo this. I have encountered situations where there was no way around doing row-by-row inserts (database conversion / merger where a stored proc needed to be run to generate values for each line) but when it was first written with cursors our conversion process was going to take about 3 days to run. After replacing the cursors with temp tables and while loops the process was cut down to a few hours. I've never seen a situation where I could not replace a cursor and benefit.
D.
If you had simply changed your cursors to forward only, read only, static cursors, you would have achieved the same result. 😉
--Jeff Moden
May 27, 2011 at 11:18 pm
Sorry... duplicate post removed.
--Jeff Moden
May 27, 2011 at 11:28 pm
pete.griffiths (5/27/2011)
For people not trained in using set based theory it is understandable to use a rbar approach because this is all that they know. But if you do know it then please use it!Rant over 😉
Speaking of rants, I'd probably reword that to say something to the effect that if you don't know set-based theory, please stay away from my data and servers. 😀
--Jeff Moden
May 29, 2011 at 7:32 am
I haven't read all posts but...
DML is not Data Modification Language. It means Data Manipulation Language and the SELECT statement is also part of it.
Anyway great article. 🙂
I've never seen someone using a cursor to concatenate strings before though.
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
May 31, 2011 at 8:42 am
The SELECT statement may be part of DML, it all depends on the context and who makes the decision. A straight Read-Only SELECT does no manipulation so it would be outside of DML. It is when the SELECT is conjoined with INSERT, INTO or UPDATE that SELECT is considered to be in DML.
May 31, 2011 at 8:51 am
Mhlewis (5/27/2011)
We've had to use cursors in generating notification emails using sp_send_dbmail. We have a list of 5 account execs that get notified when their data is loaded and ready for them to review. I'd love a set based version of sp_send_dbmail.
This is a bit rough... without tables, I just wrote the basic gist here.
But, this will generate a huge dynamic sql statement that will send all the emails at once.
DECLARE @SQL nvarchar(max)
SELECT
@SQL = COALESCE(@SQL + N'; EXEC sp_send_dbmail @Recipients = ''' + @recipientlist + ''',@body = ''Email body'',@subject = ''Subject''',N'; EXEC sp_send_dbmail @Recipients = ''' + @recipientlist + ''',@body = ''Email body'',@subject = ''Subject''')
FROM
joblisttable
EXEC sp_executesql @SQL
May 31, 2011 at 1:55 pm
Mad Myche (5/31/2011)
The SELECT statement may be part of DML, it all depends on the context and who makes the decision. A straight Read-Only SELECT does no manipulation so it would be outside of DML. It is when the SELECT is conjoined with INSERT, INTO or UPDATE that SELECT is considered to be in DML.
I was just pointing that DML isn't related to modification only.
The way I see it manipulation is not same as modification.
I've heard people believing SELECT isn't part of DML because of the "modification" word.
Selecting data is a form of manipulating data unless I'm mistaken on the concept.
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
May 31, 2011 at 2:33 pm
codebyo (5/31/2011)
Mad Myche (5/31/2011)
The SELECT statement may be part of DML, it all depends on the context and who makes the decision. A straight Read-Only SELECT does no manipulation so it would be outside of DML. It is when the SELECT is conjoined with INSERT, INTO or UPDATE that SELECT is considered to be in DML.I was just pointing that DML isn't related to modification only.
The way I see it manipulation is not same as modification.
I've heard people believing SELECT isn't part of DML because of the "modification" word.
Selecting data is a form of manipulating data unless I'm mistaken on the concept.
Best regards,
You are correct. SELECT is part of DML and it even starts its own transaction (assuming implicit transactions) just like INSERT, DELETE, UPDATE... I think the confusion arises from the common misinterpretation of the "M" as modification instead of manipulation as you pointed out, it also might stem in part from the fact that SELECT statements don't fire DML triggers.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
May 31, 2011 at 6:28 pm
Jeff Moden (1/1/2009)
gryphonsclaw (1/1/2009)
You don't have to use a cursor to concatenate a string.DECLARE @myVar varchar(MAX)
SET @myVar = ''
SELECT @myVar = @myVar + mycolumn + ' '
FROM myTable
SELECT @myVar
The author also pointed that out in the article.
Apologies for the newbishness, but this bit of SQL kinda blows my mind because I've been a developer for years, but had never come across code like this. How does this work? Seems like there would have to be an internal cursor at work here. Can someone please point out a link explaining how this style block of code works? It would be greatly appreciated.
I've started reading Itzik Ben Gan's T-SQL Querying book (currently on Ch 4) and hope to learn new appraches and techniques. Ch 1 was indeed eye-opening on the order in which clauses are processed. Sad to say that the db's I'm currently working with at my new job are rife with cursors and udfs. You couldn't possibly design less performant dbs if you tried. Oh, and not one single comment ... anywhere. What did I get myself into?
May 31, 2011 at 8:38 pm
DCPeterson (5/31/2011)
You are correct. SELECT is part of DML and it even starts its own transaction (assuming implicit transactions) just like INSERT, DELETE, UPDATE... I think the confusion arises from the common misinterpretation of the "M" as modification instead of manipulation as you pointed out, it also might stem in part from the fact that SELECT statements don't fire DML triggers.
Thank you for verifying the statement. I became unsure for an instant.
I have so much to learn and so little (precious) time. :w00t:
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
June 5, 2011 at 10:25 am
jmaloney-1117255 (5/31/2011)
Jeff Moden (1/1/2009)
gryphonsclaw (1/1/2009)
You don't have to use a cursor to concatenate a string.DECLARE @myVar varchar(MAX)
SET @myVar = ''
SELECT @myVar = @myVar + mycolumn + ' '
FROM myTable
SELECT @myVar
The author also pointed that out in the article.
Apologies for the newbishness, but this bit of SQL kinda blows my mind because I've been a developer for years, but had never come across code like this. How does this work? Seems like there would have to be an internal cursor at work here. Can someone please point out a link explaining how this style block of code works? It would be greatly appreciated.
I've started reading Itzik Ben Gan's T-SQL Querying book (currently on Ch 4) and hope to learn new appraches and techniques. Ch 1 was indeed eye-opening on the order in which clauses are processed. Sad to say that the db's I'm currently working with at my new job are rife with cursors and udfs. You couldn't possibly design less performant dbs if you tried. Oh, and not one single comment ... anywhere. What did I get myself into?
Apologies for the late response. :blush:
Think of it as a "set based loop"... something that some of us call a "Pseudo Cursor". For every row in the table, it will add "mycolumn" from the table. So, if the table had the first three letters of the English Alphabet in it, here's what would happen.
1. The DECLARE and SET create the variable and turns it from containing a NULL to having an empty string in it.
2. The SELECT reads the "first" row from the table and the content of row/column is added to the variable along with a "space" delimiter (the delimiter isn't important here) and reassigned to the variable kind of like say @counter = @counter + 1.
The variable now contains "A ".
3. The SELECT then reads the "second" row from the table and the same thing happens as in #2 above. The content of the second row is added to the variable and the result is stored back in the variable.
The variable now contatins "A B ".
4. The SELECT then reads the "third" row from the table and the same thing happens as in #2 and #3 above. The content of the second row is added to the variable and the result is stored back in the variable.
The variable now contatins "A B C ".
Unfortunately, the technique is a less-than-optimal method and can get quite slow as the variable gets bigger for reasons similar to why the old fashioned "bubble-sort" get's slower... It has to handle more and more data for each row added in. There's a trick with XML Path that you can do in a setbased fashion to concatenate related row information. Please see the following articles for more information...
http://qa.sqlservercentral.com/articles/Test+Data/61572/
http://qa.sqlservercentral.com/articles/comma+separated+list/71700/
--Jeff Moden
June 7, 2011 at 10:14 am
Jeff, thank you for taking the time to answer. 🙂
I suspected that there would likely be some overhead involved. And thank you for the links, I'll check them out straight away. I also read your Tally Table article - fantastic stuff. I think I found my new religion as an anti-RBAR convert. This forum is outstanding!
June 15, 2011 at 5:50 pm
jmaloney-1117255 (6/7/2011)
Jeff, thank you for taking the time to answer. 🙂I suspected that there would likely be some overhead involved. And thank you for the links, I'll check them out straight away. I also read your Tally Table article - fantastic stuff. I think I found my new religion as an anti-RBAR convert. This forum is outstanding!
Apologies for the late response, again! :blush: I managed to burn the candle at both ends and at the middle. 😛
Thank you very much for the kind words about the Tally Table article. I'm very happy to add you to the "Anti-RBAR Movement". 🙂
--Jeff Moden
Viewing 13 posts - 76 through 87 (of 87 total)
You must be logged in to reply to this topic. Login to reply