Looping

Question

Looping

Viewing 15 posts - 31 through 45 (of 49 total)

You must be logged in to reply to this topic. Login to reply

Andrew Watson-478275 SSCarpal Tunnel Points: 4652 More actions · Answer 1

SQLRNNR (3/16/2012)
happycat59 (3/15/2012)
Whilst it might take a while, eventually you will get an error when you run out of disk space. So, I kinda think that the correct answer is most likely that you will get an error at line 8 (the INSERT statement).
That may be true, but given the max size limitations of SQL Server and if you could have that much disk space - you would be waiting for a very very very long time.

Not necessarily. The limit for table size is "how much disk have you got". If your DB is already filling the disk, you could hit the ceiling pretty quickly.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Andrew Watson-478275 (3/16/2012)
SQLRNNR (3/16/2012)
happycat59 (3/15/2012)
Whilst it might take a while, eventually you will get an error when you run out of disk space. So, I kinda think that the correct answer is most likely that you will get an error at line 8 (the INSERT statement).
That may be true, but given the max size limitations of SQL Server and if you could have that much disk space - you would be waiting for a very very very long time.
Not necessarily. The limit for table size is "how much disk have you got". If your DB is already filling the disk, you could hit the ceiling pretty quickly.

That's why I qualified it with "if you could have that much disk space"

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 3

patrickmcginnis59 (3/16/2012)
Sean Lange (3/16/2012)
Nice easy question. Unfortunately the choices are worded such that none of them are really correct. The biggest issue is this is testing syntax on a construct that we strive so hard around here to convince people not to use. If this was a forum post everyone would be asking "why do you need a loop for this? This is a set based operation.".
I've always wondered why T-SQL is so bad at loops. Is there an inherent limitation on the quality of program translation inside T-SQL that forces such bad performance? I always just considerd that it could be another shortcoming with Microsoft, but I also have wondered if there are circumstances that require poor performance with language translation inside SQL server.

Probably because T-SQL is optimized for set-based processing and not RBAR processing, and you don't have as many loops with set manipulation I would think. It's all about what the purposes are for.

patrickmcginnis59 SSCertifiable Points: 6483 More actions · Answer 4

jeff.mason (3/16/2012)
patrickmcginnis59 (3/16/2012)
Sean Lange (3/16/2012)
Nice easy question. Unfortunately the choices are worded such that none of them are really correct. The biggest issue is this is testing syntax on a construct that we strive so hard around here to convince people not to use. If this was a forum post everyone would be asking "why do you need a loop for this? This is a set based operation.".
I've always wondered why T-SQL is so bad at loops. Is there an inherent limitation on the quality of program translation inside T-SQL that forces such bad performance? I always just considerd that it could be another shortcoming with Microsoft, but I also have wondered if there are circumstances that require poor performance with language translation inside SQL server.
Probably because T-SQL is optimized for set-based processing and not RBAR processing, and you don't have as many loops with set manipulation I would think. It's all about what the purposes are for.

Well it doesn't seem like its not optimized, it seems like something is actually missing. I paced a loop running on a server against a dot net program running on a PC, and it was orders of magnitude worse on the server without even taking into consideration the vast hardware advantages the server had. This is not a new discovery of mine, but I was reminded of it by a web article that talked about set based processing as a new programming paradigm when an anonymous poster just pointed out that there was nothing mysterious about set oriented programming, it was just that T-SQL was so bad as a programming language and he was right.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

patrickmcginnis59 (3/16/2012)
jeff.mason (3/16/2012)
patrickmcginnis59 (3/16/2012)
Sean Lange (3/16/2012)
Nice easy question. Unfortunately the choices are worded such that none of them are really correct. The biggest issue is this is testing syntax on a construct that we strive so hard around here to convince people not to use. If this was a forum post everyone would be asking "why do you need a loop for this? This is a set based operation.".
I've always wondered why T-SQL is so bad at loops. Is there an inherent limitation on the quality of program translation inside T-SQL that forces such bad performance? I always just considerd that it could be another shortcoming with Microsoft, but I also have wondered if there are circumstances that require poor performance with language translation inside SQL server.
Probably because T-SQL is optimized for set-based processing and not RBAR processing, and you don't have as many loops with set manipulation I would think. It's all about what the purposes are for.
Well it doesn't seem like its not optimized, it seems like something is actually missing. I paced a loop running on a server against a dot net program running on a PC, and it was orders of magnitude worse on the server without even taking into consideration the vast hardware advantages the server had. This is not a new discovery of mine, but I was reminded of it by a web article that talked about set based processing as a new programming paradigm when an anonymous poster just pointed out that there was nothing mysterious about set oriented programming, it was just that T-SQL was so bad as a programming language and he was right.

I would disagree with the sentiment that T-SQL is a bad programming language because it isn't a programming language. T-SQL is a data retrieval and manipulation language and it is fantastic at doing that. It is not intended to be a programming language. Looping constructs and RBAR in general are an "extension" of the language to accommodate the programming mindset. It is the way many people think of data that is the problem. When you can stop thinking about what you want to do to a row and instead think about what you want to do to a column you have broken the barrier from RBAR to set based thinking.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

patrickmcginnis59 SSCertifiable Points: 6483 More actions · Answer 6

Sean Lange (3/16/2012)
patrickmcginnis59 (3/16/2012)
jeff.mason (3/16/2012)
patrickmcginnis59 (3/16/2012)
Sean Lange (3/16/2012)
Nice easy question. Unfortunately the choices are worded such that none of them are really correct. The biggest issue is this is testing syntax on a construct that we strive so hard around here to convince people not to use. If this was a forum post everyone would be asking "why do you need a loop for this? This is a set based operation.".
I've always wondered why T-SQL is so bad at loops. Is there an inherent limitation on the quality of program translation inside T-SQL that forces such bad performance? I always just considerd that it could be another shortcoming with Microsoft, but I also have wondered if there are circumstances that require poor performance with language translation inside SQL server.
Probably because T-SQL is optimized for set-based processing and not RBAR processing, and you don't have as many loops with set manipulation I would think. It's all about what the purposes are for.
Well it doesn't seem like its not optimized, it seems like something is actually missing. I paced a loop running on a server against a dot net program running on a PC, and it was orders of magnitude worse on the server without even taking into consideration the vast hardware advantages the server had. This is not a new discovery of mine, but I was reminded of it by a web article that talked about set based processing as a new programming paradigm when an anonymous poster just pointed out that there was nothing mysterious about set oriented programming, it was just that T-SQL was so bad as a programming language and he was right.
I would disagree with the sentiment that T-SQL is a bad programming language because it isn't a programming language. T-SQL is a data retrieval and manipulation language and it is fantastic at doing that. It is not intended to be a programming language. Looping constructs and RBAR in general are an "extension" of the language to accommodate the programming mindset. It is the way many people think of data that is the problem. When you can stop thinking about what you want to do to a row and instead think about what you want to do to a column you have broken the barrier from RBAR to set based thinking.

Well I understand that part and certainly understand T-SQL's limitations because I've personally tested how slow loops are. But if T-SQL was faster it would certainly be nice.

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

patrickmcginnis59 (3/16/2012)
Sean Lange (3/16/2012)
patrickmcginnis59 (3/16/2012)
jeff.mason (3/16/2012)
patrickmcginnis59 (3/16/2012)
Sean Lange (3/16/2012)
Nice easy question. Unfortunately the choices are worded such that none of them are really correct. The biggest issue is this is testing syntax on a construct that we strive so hard around here to convince people not to use. If this was a forum post everyone would be asking "why do you need a loop for this? This is a set based operation.".
I've always wondered why T-SQL is so bad at loops. Is there an inherent limitation on the quality of program translation inside T-SQL that forces such bad performance? I always just considerd that it could be another shortcoming with Microsoft, but I also have wondered if there are circumstances that require poor performance with language translation inside SQL server.
Probably because T-SQL is optimized for set-based processing and not RBAR processing, and you don't have as many loops with set manipulation I would think. It's all about what the purposes are for.
Well it doesn't seem like its not optimized, it seems like something is actually missing. I paced a loop running on a server against a dot net program running on a PC, and it was orders of magnitude worse on the server without even taking into consideration the vast hardware advantages the server had. This is not a new discovery of mine, but I was reminded of it by a web article that talked about set based processing as a new programming paradigm when an anonymous poster just pointed out that there was nothing mysterious about set oriented programming, it was just that T-SQL was so bad as a programming language and he was right.
I would disagree with the sentiment that T-SQL is a bad programming language because it isn't a programming language. T-SQL is a data retrieval and manipulation language and it is fantastic at doing that. It is not intended to be a programming language. Looping constructs and RBAR in general are an "extension" of the language to accommodate the programming mindset. It is the way many people think of data that is the problem. When you can stop thinking about what you want to do to a row and instead think about what you want to do to a column you have broken the barrier from RBAR to set based thinking.
Well I understand that part and certainly understand T-SQL's limitations because I've personally tested how slow loops are. But if T-SQL was faster it would certainly be nice.

That is kind of like wishing for a SMART car than can compete at the Nascar level. 😀 It just isn't built for doing that kind of thing. It does however do what it was designed to do quite well.

That aside it would be a lot easier if looping were faster. Of course a lot of us threadizens would be out of a hobby because we wouldn't have to answer as many requests for making RBAR solutions faster.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

zymos SSCommitted Points: 1960 More actions · Answer 8

Great question and thanks covering the topic. Very common mistake when one is learning Loops.

DBA_Dom SSCrazy Points: 2902 More actions · Answer 9

Thanks for the back to basics counter question. Especially on New iPad/New Apple TV release day!

tilew-948340 Hall of Fame Points: 3431 More actions · Answer 10

I found the answer rapidly as I was smart enough 😀 to find the error

Because I know that it would not take me more than few seconds to realise that there was something wrong and stop the process before it goes to disk limitation, I don't think that "disk space limitation" is a good reason for the answer on "error at line 7" unless you are very dumdum :laugh: or your company has veryvery small budget to buy a decent disk :hehe:

Thanks for the question and a nice week end to everyone!

fhdfger Valued Member Points: 73 More actions · Answer 11

n2012 comes, in order to thank everyone, characteristic, novel style, varieties, low price and good quality, and the low sale price. Thank everyone

==== ( http://www.fullmalls.com ) =====

$33 True Religion jeans, Ed Hardy jeans,LV,Coogi jeans,Affliction jeans

$30 Air Jordan shoes,Shox shoes,Gucci,LV shoes

50%Discount winter fashion :Sandle,t-shirt,caps,jerseys,handbag and brand watches!!!

$15 Ed Hardy ,LV ,Gucci Bikini

$15 Polo, Ed Hardy, Gucci, LV, Lacoste T-shirts

$25 Coach,Gucci,LV,Prada,Juicy,Chanel handbag,

$10 Gucci,Ed Hardy sunglasses

$9 New Era caps.

give you the unexpected harvest

==== ( http://www.fullmalls.com ) =====

fhdfger Valued Member Points: 73 More actions · Answer 12

n2012 comes, in order to thank everyone, characteristic, novel style, varieties, low price and good quality, and the low sale price. Thank everyone

==== ( http://www.fullmalls.com ) =====

$33 True Religion jeans, Ed Hardy jeans,LV,Coogi jeans,Affliction jeans

$30 Air Jordan shoes,Shox shoes,Gucci,LV shoes

50%Discount winter fashion :Sandle,t-shirt,caps,jerseys,handbag and brand watches!!!

$15 Ed Hardy ,LV ,Gucci Bikini

$15 Polo, Ed Hardy, Gucci, LV, Lacoste T-shirts

$25 Coach,Gucci,LV,Prada,Juicy,Chanel handbag,

$10 Gucci,Ed Hardy sunglasses

$9 New Era caps.

give you the unexpected harvest

==== ( http://www.fullmalls.com ) =====

fhdfger Valued Member Points: 73 More actions · Answer 13

n2012 comes, in order to thank everyone, characteristic, novel style, varieties, low price and good quality, and the low sale price. Thank everyone

==== ( http://www.fullmalls.com ) =====

$33 True Religion jeans, Ed Hardy jeans,LV,Coogi jeans,Affliction jeans

$30 Air Jordan shoes,Shox shoes,Gucci,LV shoes

50%Discount winter fashion :Sandle,t-shirt,caps,jerseys,handbag and brand watches!!!

$15 Ed Hardy ,LV ,Gucci Bikini

$15 Polo, Ed Hardy, Gucci, LV, Lacoste T-shirts

$25 Coach,Gucci,LV,Prada,Juicy,Chanel handbag,

$10 Gucci,Ed Hardy sunglasses

$9 New Era caps.

give you the unexpected harvest

==== ( http://www.fullmalls.com ) =====

Sean Lange SSC Guru Points: 286590 More actions · Answer 14

Spam reported on posts above.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Toreador SSChampion Points: 11366 More actions · Answer 15

Andrew Watson-478275 (3/16/2012)
Not necessarily. The limit for table size is "how much disk have you got". If your DB is already filling the disk, you could hit the ceiling pretty quickly.

Out of interest I tried it on my PC (35Gb feee disk space). It took 7 minutes to fail.