May 1, 2008 at 9:00 pm
I very much appreciate the feedback... thanks.
I understand where you're coming from... been there myself... been burned by that very same thing. Now, I assume that all code, no matter how silly or random in usage it may originally seem, is destined for production because I've seen non-production quality code get used against production and what its effect is. The only time I ever write even a simple "step through the databases" bit of code with a While loop, is in SQL Server 2000 and only because of the limits imposed by VARCHAR(8000). In 2k5, no While loops even for those types of things.
I suppose it's a matter of practice for me (and a lot of other folks I know)... I've practiced not using While loops for so long that I just about don't ever need them anymore (with the exceptions I've already stated). Falling back on a While loop just doesn't seem practical, anymore, and it actually takes me longer to think of how to do it in a loop that it does set based.
Even it it takes me twice as long to write something set based (not usually) than it would to write a While loop, I'll still take the time to do it set based simply because I don't want to develop any bad habits or get anyone else into a bad habit.
I guess the best example I can give is the age old running total problem... it takes scant seconds to type up a nice bit of correlated sub-query code (heh, not even a While loop) that uses a triangular join to do a "quickie" job on a couple of thousand rows. Even if it turns out to be true that the target table never does grow, there's always the desparate person who says "Hey, there's some code in this other database for that... let's use it!"... and they do... and, it's on a million rows... and, it takes a week to run. It'll take me 15 minutes or more to do it right... but I take the 15 minutes...
There may be a "business reason" for getting the code done as quickly as possible... there's also a business reason when they call you on the carpet because some code with your name on it took a week to run or it crippled a server for a half hour even though it wasn't you that put the code in that other database. I've even had my boss do that... give him a tool in the form of some code... some other developer comes to him and says they need something like that... the boss gives the developer the code and off they go to the nearest terabyte database to install code... it better not be performance limited code. :hehe:
My recommendation that you always write high performance, highly scalable, easy to read and troubleshoot code, even for a dumb ol' interview question like this one, comes from a long line of experiences of what happens if you don't. And, I gotta tell you... what new employer isn't going to appreciate the fact that your concerned about quality and maintainability of code unless all they want from you is to be a head-down code monkey?
There's no excuse for writing performance limited or scalability limited code even for quickee code jobs that are guaranteed to be small...
Like I said, thanks for the feedback... I hope one of those smaller jobs never turn around and bite you like I've seen it bite some folks (me included).
--Jeff Moden
Change is inevitable... Change for the better is not.
May 2, 2008 at 6:31 am
dfalso (5/1/2008)
Jeff, I agree with the point you're trying to make, assuming that this code is destined for a production use. I would absolutely look to a set-based solution, not just because it's faster, but because it's a set-based problem. However, I was just trying to draw attention to the context, because what if it isn't meant for some massive installed system? Maybe it's just a random utility solving a need that your sysadmins have to generate 100ish rows for whatever reason. Maybe that doesn't happen much in your world(s), and if not, then I apologize for the misunderstanding. For me, oftentimes besides coding for purpose I also have to consider things like readability. If this is some stored procedure for my boss (who can probably figure out a while loop, but a CTE would throw him) to generate his own list of 100 rows (but maybe wants to change it to 200 occasionally), this is the way I'll go.Do I think writing for performance and scalability should be sacrificed? Not at all. I was just trying to illustrate that the scalability may be in a different direction than you all are assuming (I want this to "scale" to my boss' need to generate a handful of rows). So my WHILE loop "scales" much better than a CTE-type solution.
Note: I wish I could find a good business reason for the original problem, to better illustrate a scenario, but I can't, so that probably muddies the waters a bit.
Context is extremely important. The fact of the matter is, this is a test of formatting more than a test of SQL skills and therefor isn't the kind of query that ought to be run in SQL in the first place. However, I still think it qualifies as a decent little test of logic. I'm with Jeff on this. Get in the habit, as much as possible, of writing good code up front. Developing the skills and good habits of writing a query that could scale to 1 million rows even if it's only ever going to handle 1 hundred doesn't usually cost any more time. Not only that, with some exceptions, I've found the good set based approach to be the easier to read and maintain code.
That said, this is still working very well as a rorshack test. Cool!
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 2, 2008 at 8:08 am
Jeff, I appreciate both your and Grant's replies. I also appreciate your concerns about something that's written for a small situation now, but for whatever reason is used in a different way later. I agree with both of you that in general, it's best to design for worst-case usage.
I was just trying to explain how, in certain situations, it's better to keep other things in mind besides "the best way" (in this case, performance-minded optimization). For instance, I sometimes have to make a table that looks like a report (i.e. first cell of first row has the title, etc.). Is this the best way to go? No, I'd much rather use Reporting Services. But there may be other factors (users really need this in Excel 03, someone needs to use it in a different application, users' comfort level, etc) that come into play, and I need to consider all of them to solve the problem, not just code a result set.
Also, because of the small-business (me) vs. large-business (you all, I think) situation, I have to worry about things like readibility. If I know that the stored proc is limited in a certain way (let's say, looping over a database list), I will write for readibility and in a situation like the topic here, use a WHILE loop. Do I run the risk of someone employing it for the wrong use? Yes, absolutely. But the flip side is if I get run over by a bus today at lunch, my boss can figure out what the proc is doing and tweak it if necessary [hopefully neither scenario comes true :w00t:]. So I need to keep that in the back of my mind as well. Again, for product use, I'll code the best way. But for a non-production utility like this one, it's something to consider.
Sorry, this turned out to be a long-winded "yes, we both agree" statement. Just wanted to explain where I was coming from...
May 2, 2008 at 8:30 am
Grant Fritchey (5/1/2008)
Weeks, dude, weeks. I just keep telling myself that bringing the sword to work won't really help the problem... I think. Maybe it will...
Time to go back and some time in the dojo, practicing with the real blade and not the practice blades (so as to refocus and remember the respect of the art). Nothing like playing with something dangerous to refocus your priorities.
Come on now - drowning breath exercise for 20 minutes - it will help refocus you:)...
Bad code leads to fear, which leads to anger, and that leads to the dark side.
This too shall pass...;)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 2, 2008 at 8:43 am
How did you know what I had planned for the week-end... 😀
I have to ask though, what's drowning breath exercise? While we do incorporate a lot of traditional stuff, including sweet, sweet katana, we're basically a street-oriented kenpo school.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 2, 2008 at 8:47 am
dfalso (5/2/2008)
Jeff, I appreciate both your and Grant's replies. I also appreciate your concerns about something that's written for a small situation now, but for whatever reason is used in a different way later. I agree with both of you that in general, it's best to design for worst-case usage.I was just trying to explain how, in certain situations, it's better to keep other things in mind besides "the best way" (in this case, performance-minded optimization). For instance, I sometimes have to make a table that looks like a report (i.e. first cell of first row has the title, etc.). Is this the best way to go? No, I'd much rather use Reporting Services. But there may be other factors (users really need this in Excel 03, someone needs to use it in a different application, users' comfort level, etc) that come into play, and I need to consider all of them to solve the problem, not just code a result set.
Also, because of the small-business (me) vs. large-business (you all, I think) situation, I have to worry about things like readibility. If I know that the stored proc is limited in a certain way (let's say, looping over a database list), I will write for readibility and in a situation like the topic here, use a WHILE loop. Do I run the risk of someone employing it for the wrong use? Yes, absolutely. But the flip side is if I get run over by a bus today at lunch, my boss can figure out what the proc is doing and tweak it if necessary [hopefully neither scenario comes true :w00t:]. So I need to keep that in the back of my mind as well. Again, for product use, I'll code the best way. But for a non-production utility like this one, it's something to consider.
Sorry, this turned out to be a long-winded "yes, we both agree" statement. Just wanted to explain where I was coming from...
Thanks, my turn to agree with you. Please don't think that my efforts to always try to do it the right way means that I won't be satisfied with "good enough." I'm thrilled when I get that high. I just aim at perfect, scalable, maintainable, clear code. I didn't mean I always got there. There comes a time when you stop. Yes, you could spend another day or two and squeeze 25ms out of the 50ms query, but you know, it's only called three or four times a minute and 50ms is good enough for now. Move on to the next high nail and start hammering it down...
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 2, 2008 at 8:48 am
I have to agree with Jeff's point, specifically because the context is an interview.
If I were interviewing a potential DBA, and asked a silly question like this, the whole point would be to see if he came back with a loop or a set. There would be no other reason, from my point of view, for asking this exact question.
Basically, I'd be looking for something on the order of: "I think in sets, and came up with a set-based solution, because that's what I'm in the habit of" as the "right" answer, vs "I wrote a loop because that might be good enough this time".
Given a choice of two DBAs (and by that I mean people who would be writing T-SQL code for whatever use, in this case), I'd definitely take the one whose first instinct was "set-based, scalable, performant", vs the one whose first instinct was a loop or cursor, and who wanted to explain to me that, in some cases, a loop or cursor might be good enough.
The reason, of course, is that when real code comes up, the first guy is going to look at it set-based, and the second one is going to try a cursor or loop. Simple as that.
The purpose of technical tests in an interview is simply to find out (a) does the person know what his resume says he knows, and (b) how does he attack coding issues: thought process, self-enforced standards, documentation, etc. Using this question is simply a way to get those data.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 2, 2008 at 9:00 am
Michael Valentine Jones (5/1/2008)
This was my favorite solution from the thread Peso linked to.
declare @ int set @=0while @<100begin set @=@+1print left(@,sign(@%3*@%5)*3)+
left('Bizz',4-sign(@%3)*4)+left('Buzz',4-sign(@%5)*4)end
Short and easy to understand 🙂
Here is a link to the other thread for those who just can't get enough of this.
Beautifully, and Elegantly Simple. Pretty cool too.
I looked at the article and the discussion along the lines of How Many Ways to Skin a Cat, and this is a good one.
May 2, 2008 at 9:07 am
Grant Fritchey (5/2/2008)
How did you know what I had planned for the week-end... 😀I have to ask though, what's drowning breath exercise? While we do incorporate a lot of traditional stuff, including sweet, sweet katana, we're basically a street-oriented kenpo school.
Drowning breath (or GiGong) is a deep-breathing exercise. Simple: inhale through nose, exhale through mouth. The catch is that you are looking to slow down your breathing as low as you can stand it (and technically - pushing it a little), so you're looking to trade the typical 15 breaths per minute with something more along the lines of one solid inhalation (stretched over 30-45 secs) and one really long exhale (also 45 secs +). really slow in BOTH directions. Takes a bit of focus, and is actually a very intense exercise.
Let's just say this causes a very interesting sensation - rather mind-focusing. Thus the name
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 2, 2008 at 9:21 am
GSquared (5/2/2008)
I have to agree with Jeff's point, specifically because the context is an interview.If I were interviewing a potential DBA, and asked a silly question like this, the whole point would be to see if he came back with a loop or a set. There would be no other reason, from my point of view, for asking this exact question.
Basically, I'd be looking for something on the order of: "I think in sets, and came up with a set-based solution, because that's what I'm in the habit of" as the "right" answer, vs "I wrote a loop because that might be good enough this time".
Given a choice of two DBAs (and by that I mean people who would be writing T-SQL code for whatever use, in this case), I'd definitely take the one whose first instinct was "set-based, scalable, performant", vs the one whose first instinct was a loop or cursor, and who wanted to explain to me that, in some cases, a loop or cursor might be good enough.
The reason, of course, is that when real code comes up, the first guy is going to look at it set-based, and the second one is going to try a cursor or loop. Simple as that.
The purpose of technical tests in an interview is simply to find out (a) does the person know what his resume says he knows, and (b) how does he attack coding issues: thought process, self-enforced standards, documentation, etc. Using this question is simply a way to get those data.
{I am sooo sorry to keep beating this dead horse, btw}
I think ultimately this is about how you use SQL Server. In the work I do, we use SQL for alot more than just making an OLTP system work (in fact, in my current job, we don't have an OLTP system). So I come more from a "solve the problem" approach. So for me, I ask coding questions to see how a candidate thinks about a problem ("not" a code problem). Yes, absolutely, I agree with everyone that looping over a table is a horrible way to solve a set-based problem, but that doesn't inherently mean that cursors or loops are evil; you just have to know where (not) to use them. In fact, I use cursors almost every day, just not in the way you'd expect.
So for me, a candidate that automatically assumes that a) the code they're writing is destined for a production system, and b) that performance is the be-all, end-all consideration, is not a good fit for the company, because there's more to consider than that.
[In a general statement]
One learning experience that stands out for me: at my last job, I wanted to write some code to solve a particularly tricky problem. I came up with a very slick, but mathematically complicated way to solve the problem. My boss rejected it. Why? Because no one on the team understood the math behind it (I was a physics major in college). So he (smartly) insisted that I write an algorithm that, while not as effiicient, was understandable by the rest of the team. His point (as mine above): what happens when I'm gone and someone else has to maintain it (which, since I left, is an important one)? Yes, one could argue that the other team should grow in skills, but that ignores the reality of the situation. In the context of the problem, mine was not a "scalable' solution since no one else could modify it.
May 2, 2008 at 9:28 am
I'll give it a shot. If I end up in the hospital, after I get out, we're going to have a chat. 😛
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 2, 2008 at 9:45 am
Grant Fritchey (5/2/2008)
I'll give it a shot. If I end up in the hospital, after I get out, we're going to have a chat. 😛
Just remember - if you pass out - you've gone too far:) Start with baby steps and work up from there....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 2, 2008 at 7:07 pm
DFalso, if you get hit by a bus tomorrow and your boss can't figure out your code, then you're proving exactly what I said... your code is not production quality in that it's not properly documented. If you're willing to write code with While loops because you think it brings understanding, then you're not documenting your code well enough, especially for your boss. If your willing to hire someone who answers something like the FizzBuzz problem with a While loop, I know a thousand people who will be happy to work for you. I want the other ones... the ones that know how a database was meant to work.
You said that you use While loops every day... what do you use While loops for?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 2, 2008 at 7:14 pm
Matt Miller (5/2/2008)
Grant Fritchey (5/2/2008)
How did you know what I had planned for the week-end... 😀I have to ask though, what's drowning breath exercise? While we do incorporate a lot of traditional stuff, including sweet, sweet katana, we're basically a street-oriented kenpo school.
Drowning breath (or GiGong) is a deep-breathing exercise. Simple: inhale through nose, exhale through mouth. The catch is that you are looking to slow down your breathing as low as you can stand it (and technically - pushing it a little), so you're looking to trade the typical 15 breaths per minute with something more along the lines of one solid inhalation (stretched over 30-45 secs) and one really long exhale (also 45 secs +). really slow in BOTH directions. Takes a bit of focus, and is actually a very intense exercise.
Let's just say this causes a very interesting sensation - rather mind-focusing. Thus the name
Gosh... 15 breath's per minute is typical? I'd pass out from hyper-ventilation if I took that many breaths in a minute.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 5, 2008 at 7:11 am
Don't you mean "BizzBuzz breaths per minute"?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 211 through 225 (of 309 total)
You must be logged in to reply to this topic. Login to reply