I was just talking with a vendor on the phone, and we got to talking about new versions of DBs. Somehow, we really got on a tear and I wanted to share it with you.
And this is really one of the biggest areas where I think the DB industry really needs improvement. The issue is how to get devs to start thinking in modern terms. There are actually two issues surrounding this issue, maybe more, but let me get started and we'll see what shakes out.
First of all, there's this campaign of best practices that's been strictly adhered to for years without question. I actually blogged about this recently. So this is another case where someone reads a best practice and takes it to heart everywhere.
So the best practice in this case is that you should always write code as ANSI compliant as possible to make it easier to port to other platforms. And while that follows a certain logic, what they never tell you is the other side of that debate. And the other side is writing to maximize resources and efficiency on each system. That's really the issue, isn't it? Because when you write an application the same on every platform, you can't take advantage of optimizations on any of them.
A really good example of this is concurrency. Let's look at a simple app that might be written to be cross-platform for Oracle and MSSQL. Oracle and MSSQL both have roughly the same locking capabilities, but they have different defaults. So out of the box, Oracle can have better concurrency if you're using strict ANSI-compliant code because it uses a looser locking mechanism by default. MSSQL however can achieve the same level of concurrency as Oracle, you just have to flag the code properly.
However, the reverse is also true. If you don't want loose locks out of the box, then MSSQL will perform better for you while you'll have to make changes to Oracle. So it really just boils down to what you want. So in a simple concurrency situation like this, you’ve already got differences that will make or break your app in major ways.
And I'll go ahead and say that most of the time when you put code on one of these platforms that doesn’t perform well, you'll end up making the necessary changes so that the app does what it's supposed to do. Because I've written about this before -- the business doesn’t care about your ANSI principles. They want the app to perform.
And complete ANSI-compliance isn't really what the best practice means anyway. What it means is as compliant as you can make it to reduce the coding effort needed to port to another platform. Sometimes it’s not even possible to make the 100 percent compliant. Different platforms have different functions, data types, etc. So they’re going to be different anyway.
Another aspect of this ANSI-compliance myth is when devs take it to heart and write ANSI-compliant apps that will never be ported. An excellent example of this is in my current data warehouse. From time to time, I have devs come to me with solutions that have less than stellar code. And when I ask them why they wrote it like this, they say well, in case we ever need to port it to Oracle. And that sounds like a lofty goal and the managers always approve it because they really like it when their staff thinks to the future.
But it's really a dumb thing to say if you think about it. Our current warehouse is on MSSQL. It was written on MSSQL almost 10 years ago and it has never been ported to any other platform. Not only that, but there's not even an Oracle DB involved in the process, nor do we own any Oracle DBAs or devs. It was actually written by Oracle devs who were forced to write in an unfamiliar platform, and if it wasn’t put on Oracle back then, then I doubt it will be now.
My point is simple. If there’s no chance of this being ported to another system, then why code it to be ported to another system? Even if it's vaguely possible it might be ported one day, apps tend to stick around on a platform for a lot longer than you'd expect.
And in my experience, there are really only two reasons why apps get ported to new platforms. The first is cost. That one's easy. And the second is just what we're talking about. People coding something too generically and not optimizing for the current platform and someone hears that the other platform is better so they decide to solve their performance problems by porting to that platform. And of course since the code isn't optimized for that platform either they’ll probably be disappointed there too. OK, I know there are other reasons that code gets ported, but I'm making a point here. So you actually end up ensuring that you have to change platforms by being too ANSI-compliant.
I'm an analogy kinda guy, so let’s look at it this way. What if you applied this same philosophy to other aspects of your life? Let's say you're in bed with your spouse and he/she asks you to do something specific and you say no because you don’t really want to do anything special to maximize your sexual experience together. You see honey, I might actually end up having sex with someone else some day, and I don't want to get used to doing stuff specifically for you because it would be too hard for me to change for someone else. So I'm just going to do it one way in case I ever have to port my skills to another partner. Of course we know that’s completely unacceptable, but somehow it's OK in coding.
I've got another story about that and I'll make it quick. About 4 years ago, I got hired at a company as its first real DBA. My first week there, I was given the task of looking at their SQL2K box that was having severe problems. It was just crawling, and in order to make it perform better, they were going to increase the hardware and there was major talk of moving it to Oracle as well. Now, it was a fairly simple app, doing about 5 million transactions per hour, and it was failing miserably. The quote they has just gotten from their network admin was $150,000, and they wanted me to look into it because they thought that was a bit expensive.
And they were right. He was taking them to a new system with four CPUs, 10GB of RAM, and putting it on Windows Enterprise and upgrading from MSSQL Standard to Enterprise. So $80,000 of that was MSSQL licenses.
So I put a perfmon trace on the system for a day and when I looked at the results, I was amazed. The CPU and memory were asleep, and there was a sustained disk queue of more than 1,400 -- the biggest sustained disk queue I've ever seen. He was about to spend $150,000 on a solution and then just plug the same RAID array into the new box and not be any better off.
And the system currently had a five-disk RAID 5 array and all the DB files were on it. To top that off, the tables were all set for 100 percent fill factor and had NEVER been reindexed or defragged. So there were major problems here. I submitted a new proposal for $60,000, all in disks. I then split the files, reindexed all the tables, and set a lower fill factor.
This is all pretty straightforward stuff here -- nothing that impressive. But the difference it made was like night and day. Then to get even better performance, I added some concurrency flags in the SPs and we were off to the races.
So you can see that this is another case where simply porting to another platform wouldn't have done them any good at all. They would have seen an initial increase in performance because Oracle has a looser locking mechanism out of the box, and the indexes would have been pristine, but in about a week, they would have been right back where they started because they never did maintenance.
OK, I've said my piece on ANSI.
Here's another issue surrounding outdated coding practice, and this one is just as bad. I'm talking about devs who never bother to keep up with the latest coding constructs for their platform. I see so many devs still coding with SQL from 10 years ago and never even bothering to try to think in another direction. I hate to say it, but my current gig is another perfect example of that. The devs we have in my company don't like change, and they have a hard time breaking the thought patterns they've had for so many years. When I come to them with new ideas they tend to resist them. I've been talking to them, pounding certain concepts into their heads, for over 2 years now and they're finally starting to get it. I can be a persuasive guy and if it takes me that long to start turning these guys around, then imagine how hard it is for companies that don't have someone like me around to push these things.
A dev come to me just yesterday with a new solution, and it's the first time he coded a new Yukon construct on his own without having to be hounded by me. I couldn't believe it. I actually wanted to cry because he's finally starting to get it. He's finally starting to change his thinking. Let's face it -- experience is a big ship with a very small rutter. But I'm finally starting to make a difference here and I've seen it in a couple other guys too.
So guys, seriously, you have to keep your skill set up. If you go from SQL2K to Yukon or Katmai, you have to take a look at the new coding constructs and see what they can do for you. Don't look at it like a chore that now you have to learn something new -- that's the wrong attitude. If you want things to always stay the same, then go make handmade rocking chairs and leave the compute work to those who like computers.
And I’ve heard this before: "I hate it when new versions come out because that means I have to learn all this new crap." Or "what I'm doing now is good enough, why should I bother learning something just so they can justify their new version?" See, you shouldn't dread new versions. You should welcome them and get excited about them because now you get to learn something new. You get to finally have the ability to do something in SQL that you could never do before. Now your job just got easier.
You want to learn the new stuff because now only will it perform better, but it'll allow you to do things you could never do before. There are so many examples of this there’s really no reason for me to even go into it. But really, stop looking at new versions as a burden. I always welcome them with open arms because now I get to go back and clean up all my old code. I know, I know -- I've been told I'm a complete geek.
There's also the problem of existing processes. We have a lot of this going on in our shop too. Processes that are too complicated to just update without extensive testing, and yet the devs don't want to start using new constructs because everything else is done the other way and they don't want to support different code bases.
There's a certain argument that can be made here because not doing everything a different way is a way to build a solid support infrastructure. Or to put that in a more readable way, doing things the same way is easier to support. This way everyone knows where everything is, and they know the ins and outs of it. And I've preached on this general topic before. So you always want to strive to have some set of coding standards and adhere to them.
But there's the other side of the coin that says you'll end up updating this code one day and do you want to update 100 SPs or 1,000? You're only making the conversion harder on yourself and the company, not to mention that the more old code you propagate, the more performance bottlenecks you're introducing into the system, so you're going to have more problems down the road. And if you're writing new processes with new constructs, you'll start seeing how much easier they are and how much better they perform and maybe you'll start to update some of that old code.
Look at it this way: If you keep using old code, you’ll have to do a massive conversion at the end, and it’ll be much harder to ensure everything is correct. It’ll be easier to support in the short term, but it'll keep draining performance, so it'll start getting harder to tune.
However, if you start using new constructs for new processes, then sure, it could be a little harder to support in the beginning because you’ve got this huge code base and only a couple exceptions, but as time goes on, that'll start to reverse. You'll start having more and more processes under the new code base, and the old code base will become the exception. Then it's getting easier again because now not only is the dev/DBA staff trained on the new code now, but there's a synergy to finish updating the rest of the code because everyone sees the benefit. Everyone wins.
I just looked down and saw how long this is, so I'm going to stop now. But really take some of the stuff I said to heart. Learn the new coding techniques as they come out and really ask yourself the question on how portable this code actually has to be. Because if it ever does have to work on another platform, you’ll keep your same logic, but you’ll want to code it specifically for that platform.