This topic has been on my mind a lot lately. Exactly what is it about developers that makes them write such crappy code? I mean seriously guys… would it kill you to put just a little thought behind the code you write?
I’ll get to a couple examples in a minute, but first I want to explore the reasons why there’s such a difference in mentality.

I think part of the reason is because developers are quite often under such strict deadlines they just don’t have time to explore a lot of options. More often than not, if the query works, then it’s good enough. I can’t tell you how many times I’ve heard a developer say “well, it worked just fine on my workstation”. This is after having 50 instances of their query running in production at the same time, and each one blocking everything it touches.

One of the biggest reasons I feel though, is lack of education. Even under strict deadlines it doesn’t take any more time to write something correctly than it does to write it so it’ll bring the system down. I give training sessions at work sometimes for the developers downstairs, and it’s amazing to me how many of these top-notch .Net guys don’t know the simplest things about SQL.

A quick example:
We have a table that holds just a single value at a time. It changes every week or so, but at any given time it only holds 1 value. 1 row, 1 column.
Here’s the query brainchild chose to use.

Select Top 1 * from tblCurrentBatch b1 where ID in (select ID from tblCurrentBatch b2 where b1.ID = b2.ID)

I really can’t stress this enough… there’s ONE value and ONE column. the DDL is thus:

Create Table tblCurrentBatch
(
ID bigint
)

Dude, you may be under deadline, but you can’t tell me that’s the reason for this gross lack of knowledge.

Probably one of the better reasons is that they simply don’t care. The above example is a clear example of this as well I think. A fair amount of developers I’ve talked to see a clear distinction between them and DBAs. It’s their job to make the query run, and my job to make it run well. Even when I teach them how to write better code they quite often do it their way anyway. They just don’t care. Then, they go out of their way to compile the query inside the code instead of making SPs so we can optimize it later. It’s their way of fighting back. This way you have to come to them, explain the problem, and then get it fit into their busy schedule to be changed… and that’s only if they agree. Their going to keep their code in production as long as possible, and they’ll be damned if any DBA’s going to tell them how to program.
I’ve written on this topic before, and I received a comment from someone that just doesn’t make sense to me because I’m a DBA, but I think it sums up the developer’s mind quite well.

This email is completely unedited and exactly as I received it:

You are a fucking idiot. Everything has its place – and having a dba tell me I can’t use something I will respond with, YOU ARE FIRED – cause the database isn’t the product. The business logic is what is sold, and performance is a factor, not the driver.

Frankly, this is just absurd. I’ll tell you what’s going to happen in this scenario. He’s going to release his product and the customer will love it in the demo. Then he’s going to put it into production with 100 users hitting it and it’s going to crash and die. Then all his business logic will be out the window and he’ll be running to us BEGGING AND PLEADING to fix his code.
The truth is we simply live in different worlds. We live in a sandbox where everybody has to get along, and they live in a closed world controlled by strict deadlines.

OK, so what kinds of things do we usually see from these crack developers? Well, I’ve seen the following…

Problem:
Delete all records past a certain date.

Developer Solution:
Open a cursor against 75 million rows and compare each row to the given date and delete it if it passes.
Runtime: 18 hrs.

DBA Solution:
Delete from table where date < compareDate
Runtime: 30mins, depending on how much data was deleted.
*Make sure to use batch deletes… I’ll write about that next week.

———————————————————

Problem:
Select a specific batch number from a table.

Developer Solution:
Select * from BatchNumbers where BatchID like ‘%12345%’
Runtime: 45secs.

DBA Solution:
Select * from BatchNumbers where BatchID = ‘12345’
Runtime: < 1sec.
* I know, it’s varchar… what can I do?
———————————————————

Problem:
Get a list of customers with a sum of all their orders.

Developer Solution:
A complicated cursor solution that kept track of different variables and temp tables. Very limited in usefullness. I believe it was about 3 pages of code.
Runtime: 3hrs.

DBA Solution:
Select Customer, count(*) as ‘Total Orders’ from Orders group by Customer
Runtime: < 10secs.
*This went against a couple hundred million rows.

I’d love to hear about your similar experiences.

And remember… Friends don’t let developers write SQL code.

One more thing…

Don’t forget Rahul Sharma is a cheat and a fraud.
So nobody buy his book… ever, Ever, EVER!
His book is: Microsoft SQL Server™ 2000: A Guide to Enhancements and New Features

There has been some buzz lately that everyone should know about. There is an author out there who has written several articles on SQL Server and even a book. This author is a complete fraud. He is a plagiarist who has ripped off the hard work of many others and I could only hope that Microsoft sues his ass. Unfortunately it’ll probably his publisher who will suffer.

As an author, I am outraged by this fraud who profits from others’ work and research. I will do whatever I can to make sure that everyone knows that we won’t tolerate this type of behavior. I work hard on all my articles and even the sarcasm in them. I borrow from BOL or other sources sometimes, but I always cite the source.

Steve Jones at SQLServerCentral.com wrote a blog on this topic as well, and he’s already contacted MS and some publishers to let them know this has been going on.
Kevin Kline has also blogged on this.

The fraud’s name is Rahul Sharma.
So nobody buy his book… ever, Ever, EVER!
His book is: Microsoft SQL Server™ 2000: A Guide to Enhancements and New Features

Check out “his” sample chapter here:

http://www.awprofessional.com/articles/article.asp?p=28787&rl;=1

Compare that to the BOL replication stuff if you’re bored.

Rahul my friend, you haven’t heard the last of me. I’m not going to let this go. You’ve been faking your way through for a long time now, and I’m going to make it my personal mission to make sure everyone knows it.

Recently I’ve been on a tear about the quality of SQL editors out there. They all claim to be loaded with features that make our jobs easier and save time. The problem is that none of them really deliver… Ok, lets be honest, most of them are crap.

The problem as I see it is that SQL editors are being written by C++ programers and not SQL programmers. Of course, you wouldn’t want a SQL programmer writing an editor unless he could actually do it in SQL, but you do want him scoping it out. Sometimes it’s like they don’t even ask the SQL guys what’s important to them. They just figure that since they’re developers too, that that have the same needs. This really couldn’t be further from the truth.

Most of the editor features don’t actually save you any significant time. Sure, they make the letters pretty and connect to code vaults like VSS, but what else do you have for me? Anything that will actually save me time? No likely.

There are so many SQL editors out there and they all have pretty much the same features, but they all have the same major flaw. They all treat DBAs and SQL developers the same. DBAs have completely different needs from developers and if you truly want to save me some time, then build an editor that I can use too.

So how do SQL developers and DBAs differ? Well, for starters, developers write long, complex processes that can be complicated and very difficult to troubleshoot. And though they may write large chunks of code that is very similar, it’s quite often not the same, so templates are useful to them. With templates they can just replace the important data and keep the rest of the query in tact. These templates tend to be rather small, and completely void of logic. So, they would create a template for creating a certain type of function, or a blank cursor, but neither of them would have the actual queries they needed. Developers also need debuggers, but really not as often as you would think. I have developed some pretty decent SPs myself, and the number of times I’ve pulled out the debugger in 10yrs, I can count on one hand. That’s mainly because most errors are fairly recognizable and a debugger really only needs to be invoked for logic errors that are difficult to chase down.

DBAs don’t work like that though. We mainly do production type work which tends to be more troubleshooting, conflict resolution, and query/index tuning. We also do some connectivity testing. The code we write tends to be a lot shorter, often times just a single line, or several lines of independent code. Some examples of our code would be DBCC SHOWCONTIG() or sp_who2, etc. As you can see, every bit of the logic we need is right there. So DBAs tend to run the exact same code on every system they work on, not just code that’s similar to other code.

That’s really just one example of the differences in the way DBAs and developers work. I’m going to be putting together a list of features, and shopping it around to the different companies so we can get the editor we want built.
What I’d like you guys to do is email me the features you want with a brief explanation of why it’s necessary, and I’ll put it on the list. My plan is to not just submit the list to a company, but to take it to them and insist this is what their community wants, and not give them the list until they promise to build it for us. We want reasonable timelines too… not just a promise.

Let’s get this done so we can finally get a real editor that will actually save us some time.

There are several religious debates in databases.
One of the biggest debates is on platform. If I hear one more DBA scream that Oracle is vastly superior to SQL I think I’m going to start ripping out my eyebrow hair from inside my eyelids.

The simple truth is that with the exception of some the older crap out there like Faircom and Pervasive, every DB has merit in different situations. Is Oracle better than SQL Server? It really depends on what you’re doing. Oracle is better at some things, but it isn’t the right choice a lot of times. MySQL is better for some things, and it could actually win in some competitions.

First I’d like to talk about why there are mixed shops to begin with. To be perfectly honest, I’m not against mixed shops. I used to be a database purist, but I’m more pragmatic than I used to be. Mixed shops tend to happen in 1 of 2 ways. Either you get a VP in who is loyal to a vendor and insists that everything from here on out be done in their DB of choice, or you get a few 3rd party apps that only use a certain vendor as a backend, so you’re stuck with managing a new database if you want that product. Unfortunately, it’s that vendor loyalty that I’m talking about when I talk about DBs being a religion. So few tend to care about using the best tool for the job, they’re usually more concerned with what makes them feel more macho for some reason.

Come on now… there’s no reason to pick Oracle if all you’re doing is putting together a simple website. And while Yukon is going to close the gap, Oracle has better HA features than SQL2K, so it would be a better choice if you needed some real 24/7 support. The same goes for XML. You wouldn’t choose SQL2K over Oracle if you needed rich XML support. You also wouldn’t choose MySQL if you needed rich stored procedure or transaction support, or really intense 24/7. You would choose MySQL however, if you were putting together a fairly simple app or website and just needed very quick response times to fairly large data sets. Some people swear by MySQL, I just don’t happen to be one of them.

So mixed shops are ok, you just have to start putting more reasoning behind it. Go beyond that ridiculous loyalty you feel towards your favorite vendor because as we’ve found out with Quest, a vendor can turn on you without warning and the next thing you know you’ve got your entire shop full of their product and it’s no longer doing the job for you.

It’s really all just marketing anyway. In my experience most DBAs don’t know enough about DBs to make a decision to begin with. Most SQL DBAs I talk to know so incredibly little about SQL, much less Oracle or MySQL or DB2. Their decisions are made purely off of marketing or basic familiarity. I can’t count the number of times I’ve called Oracle support to have some brain-dead tech on the line make some stupid comment about SQL Server when they clearly don’t have any idea what they’re talking about. It’s pathetic really.

The truth is that there are good reasons to go with pretty much any vendor, and you just have to find the right one. Those reasons aren’t always technical either. Put simply, licensing can quite often be the deciding factor. And TCO is much more than licensing… it’s extensibility, features, east of management, robustness, etc. Holding a lesser cost of not having to keep 5 different types of DBAs in-house is also included in TCO. These are all factors, so let’s be blunt… you’re not going to choose Oracle enterprise for your intranet when MySQL will do. Just the same you’re not going to choose MySQL when you need to load a 4TB warehouse with millions of calculations. You’re probably going to choose an Oracle and RAC solution. And for all the ground in between there are plenty of choices.

See, I know some people who are open source disciples and never waver from their freeware god, but when you get right down to it, none of the open source DBs have the same feature set the big 4 vendors have. They don’t have any BI, and most of the time no real DTS capability, and monitoring is extremely limited. Not many vendors have monitoring solutions coded for MySQL so getting alerts based off of downtime and performance is more involved using an open source solution than a full-blown product. That’s a simple fact of life. You often times get what you pay for, and this is one of those times that it’s abundantly true.

TPC is Useless

If you look at everyone’s latest TPCs you’ll notice that they all reach incredible numbers of transactions/hr. The one thing they don’t mention is that they not only reach these numbers with hardware configurations that almost nobody will ever get to work with, but with very few exceptions NOBODY has the transaction requirements that these products reach. That means that only the smallest percentage of companies worldwide will ever really tax any of the Big 4 with transactions. That also means that there is a lot of middle ground where any of them could easily play without any problem whatsoever. Yukon is going to make some major waves and Oracle’s really going to feel it.

Over the years SQL Server DBAs have had to come to grips with the fact that Oracle actually is better at the higher end processing. Now, Oracle DBAs are having to come to grips with the fact that SQL Server is more cost effective. SQL Server is also really closing the gap in performance, which is another thing that Oracle DBAs are having to get used to.

So seriously… all you guys who turn this stuff into some kind of religious war just get off your high horse. Make intelligent decisions based off of the facts, not just your outdated opinions of what vendor is politically best.