Revisit What You Know
-
OK, votes are in for "Rant of the decade". Sorry, "Coherent, cogent rant of the decade". Loved it. Thanks Jeff
-
I'd like to think Im not advocating in favor of cursors, or even a leaning towards them. I agree that we should try to write clean scalable code up front rather than retro fitting. But Im advocating that all of us keep in mind the needs of the business as we decide how much time (and money) to spend on something. It's not easy to know what's right, but definitely there are some good guidelines we can follow.
I don't think my question presents an impossible scenario at all, rather one that would ultimately fit someone that could handle and appreciate a caretaker role. Don't know that I'd want that particular job myself, but the question is designed to see if they can think. Do they realize they need log backups? Will they cluster, mirror, log ship, replicate, or ? Will they hit me up to spend a bunch of money to convert to RAID 10 plus more drives for logs to comply with best practices? Will they rewrite code that works to eliminate the cursors or will they recognize that the business imperative is to kill the mainframe?
This isn't a great analogy, but maybe it will help. Imagine you have a small but annoying leak at home so you call a plumber. Plumber shows up, looks at the leak, says that the problem is you have the wrong kind of pipe. You'll need to repipe the entire house or you'll just have more problems (he then stops to instant message some friends, web browse, and look up how to repipe in BOL), and finally quotes you $5k. Next plumber comes by, dirty truck, not bathed in a week, says he can patch it for $50. Which one you going to do? Do you really need new pipe everywhere? Even if you do, can you afford to do it now? Thats the nature of businesses and they rely on people like us to give them options and explain the value, then the business gets to pick - even if we don't agree with their choice.
I think in hindsight maybe my examples could have been better, but I dont know how to point out places where stepping away from a best practice would be good for the business without picking hot button topics!
-
Cool... someone actually does read these
Thanks for the nice feedback, SDM.--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff, I can't and won't try to say that you are wrong on any particular point because I do agree with you that performance is critical.
The one thing that seems to be missing from your stated view is the realization that tradeoffs and compromises are a fact of life. There's nothing that you do that doesn't cost something, and at some point you are bound to run into the law of diminishing returns. What is also true is that the exact point at which a project reaches the diminishing returns is likely to change over time, so that decisions that were made years or even months ago that seemed logical to all involved, will in the changed environment appear to be not so logical. It is relatively easy to portray those decisions as mistakes (or malpractice) when in fact they might not have been either at the time.
/*****************
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
*****************/
-
To continue the analogy...
You have a small leak in a pipe that you noticed and you call the plumber. He shows up in a well used but very clean truck. As he opens the truck, you notice that he has some fairly well used but neatly stacked books on building codes and water quality/safety. He looks at your pipe leak and says that he can fix the leak for $50 but, because the pipe is old, will likely spring another leak, and it's made of poisonous lead... the pipe should be replaced for $60. The plumber, being well experienced, knows that turning the water off would be a huge inconvenience so he lays in a parallel pipe so that when the connection is actually made, he doesn't have to turn the water off for very long. Because it was just a straight piece of pipe, the plumber gets done early and, in the process, looks at other nearby pipes. He then shows you where the original plumber not only used lead pipe, but also created cold solder joints everywhere because (s)he did a rush job and they're all starting to leak and drip water in hidden spots. You didn't notice because the water they leaked wasn't visible, yet. Plumber suggests that you replace all the pipes in your house, that it'll cost about $5-7k because he hasn't looked at all the pipes and you say it's too much. The plumber asks you if you know how to turn off the water in case a bigger leak happens (to which you answer "yes"), hands you his card, and leaves.
A month later, one of the cold solder joints completely fails blowing and elbow joint right off the pipe while you're sleeping. The basement floods to some good depth destroying the washer, electric dryer, the content of several boxes of books you had stored on the floor, and your favorite golf shoes. You're wife is pissed because it flooded her shoe closet. You call the plumber again. Plumber only charges $70 to replace that bad pipe and the now faulty elbow. You also call a cleaning crew which charges you $1500 dollars to clean up the mess and $500 each for the new washer and dryer you had to buy. You start a new credit card so your wife can replace the shoes she lost because your insurance doesn't cover flooding no matter the source of the flood. You still don't replace all the pipes.
A month later, the hidden water accumulating in the living room ceiling finally takes it's toll. The ceiling comes down, breaking several pieces of furniture & the entertainment system and soaking the rest with the most God awful smelling moldy water you can imagine. You call the plumber ($70 to replace 1 pipe, again), the cleaning crew ($2000 this time), rent a dumpster ($200), replace all the furniture and the entertainment system ($4500), get a new ceiling put in ($1500). Even though the cleaning crew did a good job, you still notice the smell of mold for a couple of months afterwards. You still don't replace the pipes.
So, as I was trying to explain about code... what's the true cost of writing performance inhibited code as a tradeoff to meet schedule?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hell, I know that DC... I'm not the kind of person who will go for a 10 second run when the current run is only taking 30 seconds on a million rows. I'm also not the kind of person that will try to cut the time in half on a 250 millisecond return to the GUI.
My whole point is that most people don't even consider what will happen when scalability occurs. They just write code that gets the job done for now. They don't even know what a "cold solder joint" is
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff,
you bring up great points and I mostly agree.
But the problem for me is that you're talking risk. Most of us have no idea, well not zero, but very, very close to zero, idea how scalable our app needs to be. I've seen Access systems scale to hundreds of users and huge 32 way Sun boxes built with great code that served dozens.
It costs to build great scalable code. It costs time, effort, and it's hard to find great people to do it. I agree that we can code better, and that more people should be trained to pick the low-hanging fruit.
However just as much as I like your analogy, it's not necessarily accurate. You might not have a major disaster. You might have 5 more leaks and spent $300. It's a risk you take and you have to guesstimate (few of us calculate) if it's worth it.
Here's one that applies to me (somewhat).
I like woodworking. Everyone talks about dust control, health issues, etc. and there's a great site by Bill Pentz that talks about the economics of dust control (http://billpentz.com/woodworking/cyclone/Index.cfm)
Anyway, don't read it if you don't hack with wood, but he talks about the $1-2k it really costs for a good system. And he says you can start with a shop vac for $50, then buy a small collector for $400, then maybe replace that with a larger one for $600, then you need a cyclone, etc.
But many people don't ever buy a cyclone. And many of them don't see horrible health effects. Many people are effective with the $400 single stage collector. They take the risk and it pays off. Maybe they just work in the shop once a month. Maybe they do more hand tool work than power tool work, etc. The point is that maybe their economics make sense.
I think for many companies, the economics of @#$@@$# performing code work. The apps do enough for enough people that it's not worth more time to get it working better.
Everyone wants tuned code, but often they don't want to give you time to tune code. Or they want to get new code in so fast that it doesn't get tuned either.
Some companies do think it's worth it. The problem is that I'm not sure they're the majority. If they're not, then you're tilting at windmills
-
Another analogy... ask the people that have suffered "brown lung" disease or sudden near death or Anaphylactic shock due to contact with the oils from certain woods what they should have done. Yeah, yeah... rare exceptions. That's why the analogy isn't appropriate for what I'm trying to get at... you don't have to make such a large investment in good SQL code as you think and folks ask for "optimization and tuning" a heck of a lot more than how to cure brown lung disease.
And, ask yourself this... is there ever a day where the active stalls in your barn arn't taken care of? Are there any domestic animals that aren't fed every day (other than pet snakes)? Would you ever all someone to smoke in your barn?
It costs next to nothing to write good, fast, scalable code. And, even if you know the app will never scale up, there's still no reason not to write good, fast code which usually turns out to be scalable, as well. The key is simply avoiding unneccesary RBAR and the RBAR that everyone does think is necessary, probably isn't. That's it. No tuning, no special optimizations, and no fancy hardware. And, IMHO, with rare execptions, it actually takes less time and fewer lines of code to avoid the RBAR. You don't need to be a genious or a code Ninja to do it, either.
Heh... yeah...
everybody who hasn't experienced a sudden meltdown or hasn't run out of CPU time on their system thinks I'm tilting at windmills. Guess that's actually good for folks like me... we keep counting the windmills because each of them means some pretty hefty bucks in our coffers when the windmill that (apparently) only a few of us can see, breaks. And, the ones who still think I'm tilting at windmills after such an incident, are going to buy a pot wad of hardware from our buddies.You and I know there's no justification for smoking in the barn... intentionally writing performance challenged code is like trying to be real careful about how you smoke in the barn.
And, when you get visitors, you tell them "No smoking in the barn" even if the floor has no loose hay. No different with developers... train them not to smoke in the barn!But, you're right... it's all about risk management... folks can take the risk, if they want
I'll sell them new animals and my buddies will build them a new barn --Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Actually there are days the stalls aren't taken care of. There's a tolerance for delays and there are possibly days where food is left out because no one can get back. Our code handles similar tolerances.
Jeff I think it might cost next to nothing for YOU to write scalable code because you're experienced, you give good solutions and I've seen some great T-SQL code from you here. But for many others, possibly most others, it costs something, perhaps even a lot. Buying extra hardware sometimes is worth it.
Whether you're tilting at windmills or not, it's a matter of frame of reference. I agree that we should train people better, but I think we have very few trainers out there.
-
Ok... let's say that's correct for a minute... what does something like the following cost in the short term? Nothing... schedule met... everyone was happy...
What's it costing them now? Somebody was smoking in the barn when they wrote this code... and I'm not sure it was just tobacco
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=387778
And, I'll just bet that throwing hardware at such a problem isn't going to fix the problem, either! They didn't keep the barn clean and now the manure is coming out the windows
I absolutely agree with your other comment... not many "good" trainers out there. Let's get a couple of the resident "Ninjas" together and start an SQL BootCamp (more of an "SQL Special Forces" or "SQL Top Gun" training camp) of sorts... for money!
We can even create our own certificate path and get it blessed by the folks in Redmond making it something that employers will seek. Current MS Certifications may get your foot in the door... such certifications as what I'm talking about can make the recipient a highly paid individual.And, thank you for the nice compliments... means a lot coming from good folks like you
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Can't tell you what the cost is. It's a decision for the people in that situation. Lots of people accept shoddy work in construction, engineering, medicine, etc. for a short term fix. Up to the individuals involved to decide if it's worth it.
And you are welcome. You've earned the complements.
-
Hey Andy.
Just a personal recount and opinion.
To often people learn one way to achieve a result and then use it to death no matter what the cost. I had the opportunity of learning set based operations from an old friend who applied only this logic to his reasoning for set based operations. Simply put, Do it once ! It wasn't until, years later, did I come to learn the truth of that one statement. I had been sent oveseas to rescue a failing project. It was taking 30 + hours to import data into our SQL database. This was not a slouch of a server either, Quad Xeon SQL2000 Gigs and Gobs of RAM and more hard drives to make any DBA drool. The Lead developer was sent on-site and managed to only get it down to 16 hours. the cause ? 1.6 Million records being forced one at a time through a series of cursor based opeartions, why ? because they had always done it that way. One author I still use as a resource stated that, 'If the cursor is your solution, then there is a better way'. After 3 set based inserts and 1 set based update and 13 minutes later.....
Yes it took longer than the client was originally quoted, but the end result was a solution the client happily paid for. CodeOn
-
Too many have been lured from the true path via the Dark certification promise of quick rewards rather than the long road of design permanance and eloquent performance.
BTW I don't like chopsticks either...
CodeOn
-
Mal D!
Glad to see your posts... thought I was the only wirehead with the idea of "Yes, perhaps its time to re-evaluate", but in the direction of maybe it's worth spending a little extra time.
Thanks for jumping in. 
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I'll try to make this my last post, since I've already admitted that I didnt do as good as job as I'd wished making my point - but I will try again in the future!
Step away from the cursor point - we can agree to disagree there, though we don't disagree as much as you might think. I believe in set based as well, but I've lived in a world where a time or two I was resource constrained enough that I lived with a couple of them to get the job done.
I've really combined two different concerns not very clearly. One, is that as you mature in the profession you need to go back and look at the gaps in your game and your biases. If you don't have them you're a better man than I am! The other is that we need to be pragmatic about where and when we invest time. It's great if we're in a position to always use the "right" hardware, to kill every cursor, etc, but sometimes we have competing demands on our time and it's our job to make sure we're aligned with the needs of the business. For businesses large enough to afford the overhead of a dedicated DBA I think they should absolutely be the gate through which all changes pass and they should be kicking back bad tables, procs, etc, or helping to rework them when needed. What I hope all of you that are reading and posting will do is talk to me about the first part right now and we'll discuss the 2nd point in a different context one day!
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply