November 4, 2007 at 5:58 pm
Wow... thanks for the kudo, Steve. Thanks.
I read that blog article... Michael J. Hillenbrand is pretty much spot on except maybe for the order of the first two things... sometimes you don't have the luxury of building an effective data model because some 3rd party built it or it's a legacy model that you just can't change (usually, politics or to hard to justify the ROI on something that works no matter how slow)... but you can overcome the problems associated with a less-than-perfect data model if you really know SQL Server, T-SQL (along with tricks like the Tally table, etc), and what it takes to effectively use indexes (and, sometimes, be aware that an index will actually cause some performance problems).
My guess is that most of the problems in code can be traced to poor data models or poor coding from the start.
You're turn to be spot on 😉 . The part Michael left out in his SQL Tuning section is that a lot of people don't even think of tuning/writing good scalable code because it seems to work nasty fast at the time... that time is when you may have a only couple of hundred rows of test data available. Shoot... even a running balance that uses a triangular join will appear to run fast with that little amount of data (although simple scalability tests will show the impending eponential doom of that method). Schedule or not, if you don't plan for performance in the face of scalability, the code will have problems when you can least afford to have them... typically, month end runs and quarterly tax runs. Plan now or pay later. 😛
What if the schedule you have to meet won't allow for such planning? Rule still stands... Plan now or pay later. Normally, such performance testing doesn't take all that long... don't let "good enough" be your greatest enemy six months or a year from now... write great SQL from the start first time, everytime. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
November 4, 2007 at 10:56 pm
Arrogant DBA's, eh?
"What's that you say Mr. Web Developer? Just "put it in"? Sure, I'll stay up to 3am troubleshooting & optimizing your masterpiece while you are all snuggled away in bed. Not to worry. Heck, feel free to sleep-in a couple of hours. You've earned it. Oh, and be sure to take ALL the credit for solving the problems. I don't mind."
Over my dead body pal!
James Stover, McDBA
November 5, 2007 at 1:52 am
Agreed on all counts. I think it centres on efficiency in that if you decide to compensate for something inefficient in your processes instead of making it more efficient, you're always playing catch-up. Silk purses and sow's ears spring to mind, and that's not just in relation to IT let alone just the sphere of databases.
However, one area that has perhaps been missed, that affects (IMHO) SQL Server far more than Oracle, is the fact that SQL Server doesn't need much specialist knowledge to get up and running. This makes it the default choice of backend for many developers of in-house apps. In essence, they can write their app, install SQL Server and create the database they need all in one go, with little understanding of the fundamentals. Far from consulting someone who understands databases, many of them don't even have a DBA available in the first place, and, in fact, the company's SQL Server infrastructure quite often ends up being managed by the development team - not good.
Of course, there does come a point when people realise it'd be sensible to start employing some proper administration skills, but that's usually when the small, unstressed app has grown organically into a massively inefficient house of cards that soaks up any and all resources thrown at it. Oh, and the development team have grown quite proprietorial about their admin rights, so the new DBA has a double whammy to have to deal with.
Basically, there are times when SQL Server is too easy to use for its own good.
Semper in excretia, suus solum profundum variat
November 5, 2007 at 2:25 am
Most applications tend to start very small with very simple business requirements like 'We'd like a list such and so'. Then, as insight in the possibilities grows, so does the application and its complexity.
Also, we as developers or analysts grow, learn and develop our individual personal skills. We gain further insight in how particular situations can be handled differently.
The best anybody can do is anticipate which additional requirements will most likely follow the initial ones. Mostly however, we appreciate the effort but concentrate on the immediate requirements. Also, remember: no matter how much time you spend on planning, no battle plan survives first enemy contact.
I've been confronted with both kinds of progress more than once over the years (and thankful for it) and made it good practice to evaluate both data model and SQL at least two times a year to see if I had learned anything that could be done better.
One thing I have learned over the years is that it's always a good idea to have someone else review your work and ideas, both in development and in production. As they look at it from a different perspecitve, it's almost a guarantee for improvement.
November 5, 2007 at 6:22 am
I still have a 20+ year old poster in my office entitled "5 Rules Of Data Normalization". Granted, depending on whose opinion you consult, there are quite a few more today. The problem, in part, is noted relative to the ease of use in SQL Server. It unfortunately does not demand design expertise to simply get something to work. This is very evident when you compare the types of things you can get away with in SQL Server using diagrams to design a database versus a design tool like Erwin.
The other thing I saw interesting in this thread is the idea of thinking about it, walking away, then asking someone else. I've thrown away more designs than I have ever implemented by following a similar strategy. Odds are pretty good that unless you are Mr. Codd or Mr. Date, your first design is going to have some issues. They may be minor, they may be fundamental problems that only rear their ugly heads after it is too late.
Business pressures are as they may be, but I advocate the Fram approach: you can pay me now, or you can pay me later. If you take the time to seriously analyze the designs first, plan on discarding many, and especially give the ideas time to percolate while you are NOT thinking about them, you may not end up with world-class designs, but you'll more likely end up with a design you can live with during daylight hours rather than at 3 in the morning.
------------
Buy the ticket, take the ride. -- Hunter S. Thompson
November 5, 2007 at 6:40 am
James Stover (11/4/2007)
Arrogant DBA's, eh?"What's that you say Mr. Web Developer? Just "put it in"? Sure, I'll stay up to 3am troubleshooting & optimizing your masterpiece while you are all snuggled away in bed. Not to worry. Heck, feel free to sleep-in a couple of hours. You've earned it. Oh, and be sure to take ALL the credit for solving the problems. I don't mind."
Over my dead body pal!
Heh... Now THAT's what I'm talking about! Been there, done that, and don't want to do it again! 😀
But, they've figured a way around it all... we have a carefully designed scalable data model that comes close to being perfect (the designers really did do a pretty good job!)... we have very strong SQL Code Standards and code reviews and DBA reviews of the reviewed code and a QA department to boot! But, they don't have the same standards for the Java side of the house and the volume of data QA tests with is nothing but a shadow of what's expected... between the natural RBAR of HQL (Hibernate) and all the embedded SQL that doesn't get checked, I'm just waiting for the code and performance to cave in.
I think it's begining to happen because I've been asked a couple of times, lately, to "look at some code for performance problems" and it looks a whole lot like embedded SQL. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
November 5, 2007 at 7:02 am
In better than 90% of the students I see the DBA is not involved in the development cycle at all. Stranger still, most of them don't want to be involved. That leads to developers doing table design, which could be ok, but often is not. I think its the missing Data Architect that is really the missing piece of the equation, someone who really understands modelling, some level of performance, and the tradeoffs involved for developers.
As far as pay now or pay later, you might find this to be worth reading. It really struck me as practical based on my own experiences.
http://blogs.construx.com/blogs/stevemcc/archive/2007/11/01/technical-debt-2.aspx
November 5, 2007 at 7:30 am
I like Ward's analysis of "technical debt", which might help refine Jeff's rule to be Plan now, or pay later (with interest). The road to hell for DBA's and data developers is paved with wizard-based SQL code, and the "we/they" attitude between the DBA(s) and the dev team(s) relying on them.
----------------------------------------------------------------------------------
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?
November 5, 2007 at 7:35 am
... by far the worst 'pay me later' is:
"What are my chances of getting a SQL server instance and a license in the next 30 minutes?"
This just happened last Thursday ! So now we have an application server with MSDE (installed by the software vendor) going live today. Not to mention that we need to purchase another server, set it up and configure it, acquire another SQL license, install SQL Server and then get a project plan together to move the database from MSDE to a full fledged version before the limitations of MSDE set in. Talk about ROI and politics ...
Since DBAs are usually left out in the early formative stages of a project like requirements gathering, software selection and hardware acquisition and setup you get just that.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
November 5, 2007 at 7:39 am
That's a great article, Andy... I've never seen justification of doing it wrong put so eloquently. 😛 The problem with even the Type II Technical Debt is that you have to remember there's a bill to pay. 😉 Yes, by all means, I agree that you have to get the ice to market before it melts... but, if there's no decent refridgerator at the market when you get the ice there, it's eventually gonna melt anyway and the collateral damage the runoff creates may be more than what you bargained for. :hehe:
I agree with the idea that you may want to "cut some features" in order to get the product to market on time. But the collateral damage caused by performance challenged, non-scalable, or sloppy code that's allowed as "part of the plan" that isn't followed up because there's no time for that, either, will eat the face off a company in the form of a bad reputation for crappy/slow/breakable/high interference code.
If a company decides that some form of Technical Debt is ok, they better have a plan to relieve the technical dept or they'll end up in the poor house. :Whistling:
--Jeff Moden
Change is inevitable... Change for the better is not.
November 5, 2007 at 8:03 am
I agree that taking on debt around performance isn't a good idea, and Im not advocating bad design from the start either!
As a consultant its rare that Im called before the problem happens, and often they have been successful for quite some time. Then it tips over, they call someone in that does some degree of magic and makes some recommendations for the future, off they go for a while longer! As a trainer I advocate DBA's being very connected to the developers as a coach & mentor when it comes to data access & design, but often its not up to them. The ones that seem to be most successful are the ones with the charisma to push their way in without seeming like they are pushing their way in.
I find its not often that devs don't listen as much as they are so rarely told anything at the point in the dev cycle where it could be changed. The question for the industry is how do we get someone involved in the data model early on so we don't wind up with a mess later?
November 5, 2007 at 8:27 am
This editorial and the 'debt' blog post referenced in the responses, continue to perpetrate one of the greatest ineffiencies of today's IT world, namely the One True Path fallacy.
All software embodies a model of reality; all models are reductions of reality to emphasize certain aspects and de-emphasize others. Models are thus more limited in application than full reality. Different applications will require different models for best use. Simple models are quicker to build, but more limited in application, but also likely more clearly descriptive of their (limited) application area and easier to maintain for small changes to the application area.
The above is true whether your application area is physics or finance or manufacturing or accounting or whatever! There is no One True Model; rather, it all depends on the problem you are trying to solve today, how you expect the problem to evolve over what time scale, and how accurate and precise you need your model to be to match your problem.
The evolution of the problem over time is particularly difficult: where will the user (company) be in 6mos or a year or 5 years? How difficult will it be to adapt the model to the future evolution of the company? But how well can this evolution be predicted, how much generality do we need to build into the model to accomodate the predicted evolution, and, most importantly, what is the cost of miss-applied generality in the event we are very wrong and the user actually zigs instead of zags?
It is curious in particular to look at the evolution of software development costs from the fondly remembered bad old days of batch and punched cards to today. In particular, it seems to me that even as the costs of evolving systems have dropped tremendously (interactive development, evolving small models to much larger models), IT has focused on an increasingly expensive and slow planning and analysis cycle coupled with large scale development of models that seem to anticipate every possible path of problem evolution -- which all too often excepts the path actually taken.
This is already way too long, but one person's (DBA's) imperfect and inefficient model may well be another person's (developer's, users) most efficient model. And vice-versa. The question isn't model elegance or hardware efficiency, but rather efficiency related to the solving the problem at hand. (Which may well require a substantial degree of elegance, but may well not.)
November 5, 2007 at 8:52 am
I take my doses of reality in short bursts, usually about 1/2 hour after the morning double expresso:).
I think Mr Moulinex (I mean Slicendice - hehe) touches on something, which is that there seems to be a third kind of debt, which is the app that has outlived/outgrown its model. A lot of management-types are notoriously leery of "open-ended" projects, so they seek a true end to any process, and ideally, NO second/third/nth SLDC iteration to continuously improve.
----------------------------------------------------------------------------------
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?
November 5, 2007 at 9:00 am
I actually tend to agree with Sir Slicendice that it's hard to predict where you'll be, to what level you'll have success, what type of scalability is needed, etc. And I agree that getting something done quickly is many times worthwhile. For every system that fails to scale, there are probably dozens that don't need to scale.
However, Jeff has often argued that it doesn't take any more effort to write better SQL from the start. That's true in many cases if you've bothered to learn. Here's where I think developers fail. They don't make enough of an effort to learn or even do what they've learned.
Just like developers might cringe at a DBA hacking together code for their application, the reverse is true.
It pays to spend a little time on design. Even if it's a couple hours talking through it with a DBA to get ideas, it's worth it for a developer. And if you can't implement stored procedures in your code, you're not being efficient. You're being stupid and stubborn. It takes almost no more effort.
The tuning question is a hard one because often it doesn't necessarily hurt the company to have something poorly performing. The money they saved up front might still be worth it to pay a consultant later. Or they may just live with performance problems. Who's to say if the application is quicker that people will work more quickly or they'll get more work done? Very hard to quantify.
That being said. It makes some sense in almost any area, not just IT, to spend a little time thinking about the design and working out issues ahead of time if possible. It may seem like a waste of time, but often the actual building then goes more quickly.
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply