Tuning Time
-
I like the depends idea.
If management has to have it and has to have it now, or the business requires a certain piece, system, or service put in place three weeks ago and no one knew it till now, then there are certain things that could happen, like quick and dirty code that is not top drawer. When tested it proved to work and in the beta management saw what they needed. Now they are ready to deploy or to run in as production in the development environment. We have all been there, right?
However, even at these times it is not appropriate to toss the baby, bathwater, bathroom and the entire farm and write a real piece of trash. There is a minimum level of performance that should be required of any IT work done.
Another issue is as systems change for any reason systems are kludged into things they were never designed or architected to do. This often berings systems into a new realm. The effort to change a race horse into a camel is required by business. There are times we have to live with kludges and throw a lot of hardware at them.
I would rather re-architect, re-develop etc but time money, and other resources are not available and you have to kludge and throw a server at it. Part of the business.
Summary, seek to make it the best, and compromise only when you have to.
Have a good day!
Not all gray hairs are Dinosaurs!
-
Without intending to muddy the waters, there's one area where the 'get more hardware' approach can be argued to be the right one: concept prototyping. If you need to get management buy-in for big bucks to do the job properly, which they won't spend unless they have evidentiary proof that the concept works, extra hardware can be a quick win.
Having said that, I've seen, all too often, the chaos caused by management being shown a prototype and thinking 'well, that works; install it now!' You need a particular breed of rhino-hide project manager at that point to stand up to the board-level idiodicies that can be perpetrated and ensure that the prototype doesn't become the final solution (in more ways than one...)
-
Judging on all comments here, perhaps I am a minority on this one. Coming from a developer turned DBA background, I understood Steve's situation. Code tuning is all depends on the type of projects and deadlines. For low usage and none-critical project, leave it alone as long as it works (unless you have lots of free time.) For a fast paced evolving system (as in Steve's article), new features to the market on a timely manner are more important than performance tuning. I have work on a project where adding couple new features could save users hours of work each day vs. optimizing the performance only add up to save few minutes. My coding philosophy is always to make it work first and then make it work better!
-
To a very large degree I agree with this approach. The question comes in, how do you define "work." It "works" in that it returns the data I want and when we're only dealing with one user and one row it runs in 10 seconds. Of course as soon as we add two users, it deadlocks. Per my definition, this doesn't work, but I've gotten arguments. As soon as we have to run production level data of 1000 rows it takes 56 seconds and times out. Again, you could argue performance, but I'd say it's not working where again, I've had to defend that position.
That's why we really strive for a "good enough" approach. We're not trying to squeeze an extra 50ms off a query that gets called once a day. Instead we have to make sure that the system developed will "work" in production and not the hot-house of the dev server.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Sometimes good enough is good enough...
Since I took over as DBA at this company (they had never had one) I have collected over 110 GB worth of traces on stored procedures, partially to figure out which ones are not used anymore so I do not spend time on them, but mostly to get hard performance values for execution times. My goal is to take the top 25 in total execution time and cut as much time from those as possible. From what I see on this particular system the top 25 stored procedures take over half of the resources on the server.
The rest I am most likely not going to care about. If a report that runs once a day takes 5 minutes to run I don't really care (depends of course also on how and who is running it....)
Hardware is rarely the issue. Sometimes it is, but, at least in my experience, more ram has solved most of the issues where people have wanted whole new servers.
-
I would not assume that poor code scales linearly. With double the users or data, do you need another 2 or 32 servers?
If you want to just add another server to handle twice the load, then it is assumed that the queries have been tuned to scale well. If this is the case, then performance is probably already addressed along with resource considerations (memory, CPU, logical reads, scans, locking, etc.). Once performance is linear, perhaps further optimization is not critical.
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
-
This is utterly disgusting. There is no reason for such poor coding. To justify it by suggesting faster hardware is insane. No wonder most of the developers from that era are unemployable.
-
Talking about a comment or the article?
-
The need for tuning doesn't imply poor coding. It can, and I'd agree with that, but it also could be that you're trying to make things more efficient, which is what I was trying to describe in my story. You can almost always tune your code better. Heck, the Linux kernel gets tuned all the time, in many of the releases.
However in a business you have to make choices. Sometimes it's not worth a developer's time to retune code. It's more efficient to buy more hardware.
Note: This doesn't work as well in databases because we typically don't scale out well, but it other tiers, it can.
-
From a recent experience we faced both issues, old servers and less than optimized coding. As we scaled larger, the issues with providing reliable and timely service escalated to the point that performance was absolutely unacceptable from the clients perspective. Delaying action on both problems led to a point that immediate action was necessary, and optimizing tons of code was more costly in both time and money than throwing hardware at the problem. Knowing that hardware was only a temporary fix, optimizing the code became the next step at getting the most out of the dollars spent on the hardware. In fact, the education and optimizations from rewriting the code has returned the old hardware back into production , extending it's life as an additional production server. Lesson learned.
Do it right/better the first time , or throw more money at it in order to survive and do it again. Simply, if we don't keep up, we will get left behind.
-
... there is never time to do it right ...
... but ...
... there is always time to do it again !!!
That 'credo' is precisely what has made being a SQL DBA extremely erwarding personally and financially !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Oh my... Steve, you sure do know how to press the buttons
You basically brought the guns to bear on two of my favorite hot spots... "managers who know the cost of everything & the value of nothing" and "post-mortem tuning"...I think Rudy Komacsar hit the nail on the head in the post immediately above... you're going to pay for performance enabled code one way or the other and it will always be very much more expensive if you do it post-mortem rather than if you take the time to do it right the first time. And, don't forget... performance problems are like getting a flat-tire... they only happen when you can least afford for them to happen.
One of the premises of throwing hardware at a performance problems is thinking that more robust hardware will actually fix the performance problems... sometimes that works and sometimes it doesn't. I've run into dozens of situations where the customer or my employer has decided to throw big $$$ into more robust hardware and the operating system software and the training to support both as well as making a huge shift from MSSQL Standard Edition to the "Nirvana" of the Enterprise Edition and all the extra RAM/CPU's it can use, thinking that the performance problems would simply vanish. They also spend time exercising performance tricks of the trade like which disks to store the LDF on, splitting TempDB into 1 drive per CPU, partitioning data, etc, etc. Then, they may spend 1 to 6 months moving the apps and all that goes with such a move only to find out they've not fixed a single performance problem because the code is so bad to begin with. And, those that did realize a performance gain were sadly disappointed to find the perfomance went back to making major gurgling sounds when their apps died in agony, yet again, as they slammed into the "tipping point" wall of data that scaled up in just a couple of months.
The main question of your fine editorial is "How much time should be spent on performance tuning code?" A better question would be "should any time be spent"? Usually, no time, whatsoever, has been or will be spent until a performance problem is at hand in a production environment. GUI programmers not well versed in the not-so-black art of effective database programming, self-proclaimed or appointed SQL Developers who only wish to move onto the next job, and the insistent demands by Managers whose only task is to produce something that works within the too short scheduled time, all lead to absolutely no consideration for performance enabled code.
Too often the idea of "good enough" creeps into the equation of what code will be delivered and the real fact of the matter is that "good enough" usually means that if I click some infernal button on some GUI, some result will appear having been retrieved from a database that's too small for any realization of performance problems. Compound that with inadequate testing that must also meet some ridiculous schedule and the fact that a good number of DBA's are simply too lazy/arrogant or, more likely, too busy to actually look at the code going into their database, and the code that actually makes it to production has no chance of performance or scalability.
So, the real answer to the question of "How much time should be spent on performance tuning code?" is... more than what is currently spent which is normally ZERO.
And, it doesn't take a 90k$ SQL Ninja to do it either... If you want to avoid performance problems, stop making the all too common and lame excuses about how "sometimes you just gotta use a cursor", not using set based code because "it takes too long to figure out", or avoiding set based code because some wacko from the shallow end of the gene pool wrote some performance inhibited code that people call "set-based" just because it doesn't have a loop in it or it's all done in a single SELECT. A good friend of mine once said, "If you wanna catch fish, ya gotta go where the fishing is good". In the database world, being a performance "fisherman" is the best and easiest job in the world because because people keep making those same silly and costly justifications for writing and releasing ineffective code. It's on time, alright... but it just missed the garbage truck. 
I truly hope that no one takes to heart what I've said above or folks like Rudy and I may find it more difficult to find those jobs that "has made being a SQL DBA extremely rewarding personally and financially."
Of course, if the "in-house" Developers finally get smart, there's always those wonderful outsourcing companies who have even less to lose because of code with latent performance or scalability problems. Now, there's a real bargain 
So far as the statement "You can almost always tune your code better" goes, the answer is "Of course you can... no one made an attempt to tune it to begin with"!
--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) -
Like I said, fishing is good in the performance world... didn't even have to search for it... was the next item for me on "Active Threads"...
For all of those that think buying more RAM fixes problems, please see the following...
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=146&messageid=392645
The recurring theme on hundreds of similar posts on this good forum is that no one spent any time on so-called "tuning".
--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) -
Ya I can't wait to see how this one ends
. -
Same way it always does, Remi... someone will make a justification something like "Well Jeff, it depends..."
Six months later, they'll be scrambling to fix a major performance issue with some batch of code somewhere --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)
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply