Friday, September 23, 2005

What Separates DBAs from Developers--Part II

I don't know if any of you remember last week's post where I talked about some of the bad code I've seen, and the reasons why many developers don't seem to ever learn their lessons. Well, the most amazing thing has happened that may just explain the whole mystery.

I was looking through my wife's copy of Redbook this week and ran across an article about autism. To make a long story short, the article states that autistic people quite often go into programming careers. That really explains a lot doesn't it? As it turns out, programmers are genetically predisposed to buying their underwear at K-Mart. OK-OK, I'm just giving you guys a hard time. I'm just kidding... but Redbook really did say that autistic people tend to go into programming. If you don't believe me check it out. It's in the Sept 2005 issue on pg 197.

Ok, onto some serious business. I wasn't going to write on this topic again, but there was such an overwhelming response last week, I thought some of you needed more time to get it out of your system. And while some of you had some good arguments, others of you went so far as to insult me personally, while still others of you left me with some really interesting grammar lessons. I really don't mind the insults though. I kind of expect it actually after a posting like that.

Now, I think some of you are confusing a rant blog for real life. Just because I come on here and rant about something doesn't mean that I don't have a teamwork attitude at work. In fact, I'm always the first one to turn on People's Court(yeah, yeah, time for Wapner) when our developers need help. I teach SQL classes to show them the right ways to write code, and even give them DOs and DON'Ts. It never makes any difference though. When they turn their code in it always looks the exact same way it did before. Go figure. But of course you have to have a teamwork attitude with everyone. How else would you ever get your job done?

One of you said that it was a training issue. This is quite true. Training is the key. The problem is in getting them to get the training. You can make them sit in a classroom but you can't make them care. And that doesn't mean that I think all devs are bad either. There are exceptions, and I've even met a couple.

I really don't expect everyone to know everything, or even most of everything. And I certainly don't know everything, nor do I claim to. I'm sure that the answers I give to a complicated problem are not only different from what someone like Kalen Delaney or Kim Tripp would give, they're different from the answers I'll be giving 5yrs from now. Just as they're different from the answers I gave 5yrs ago. The good news is that the SQL superstars go through the same thing. Their answers changed based off of their current level of experience.
What I do expect though is for some of the basics to rub off after a certain amount of time. I expect that devs would know not to hardcode index hints, or put a clustered index on description varchar(200). I would also expect that you wouldn't put extra indexes to speed up inserts, or put 4 indexes on a 2 column table. Yet these are all things I've seen numerous times.

Maybe we should all swallow our pride and concentrate more on getting the job done and less on pointing fingers. I think I have a solution though. Why not base a bonus structure on the quality of the code when it hits production? So, you've got a 3 month window. You start out with $500 as a bonus whenever you push a major release into production. Then, for every problem caused by something you coded, you lose a portion of your $500. So, the DBAs have to fix a horrible query, you lose $100. They have to take out an index hint, $50, etc. Now answer me honestly... how many developers do you think would learn to think in sets then? How many times do you think our advice would go ignored? How many of the lazy 'select *' queries do you think we'd see? Anyway, just a thought.

And the same goes for DBAs too. How about the same type of structure for the systems. How many times do you want to lose your bonus because you didn't back up a DB properly, or didn't test the restore? How much money do you want to lose because you did something stupid and brought the system down, or left the where clause off of a delete and killed the whole table?

The problem we have at my office is that the devs don't write good code, and the users have become to expect that SQL runs very slowly. A very good example comes from a conversation I had with one of the production managers last year. She called and said they were having trouble with the reports. They're taking a long time to come up. I said, ok, which report. She told me which one, and said that it was taking about 35mins to come up. I said, and how long does it usually take? She said... it usually comes up pretty fast. The normal time is about 25mins. After I fixed the immediate problem I looked into the queries themselves. Once I finished with them, they came up in less than a second. It's very easy for users to get used to DBs performing poorly. If it's never been a fast system they have nothing to compare it to. It's up to all of us to set their expectations. We had a problem last week with another bit of code in which they were complaining that it had gone from 4secs to over 2mins. One of my DBAs took the ticket, and told the customer that the performance was unacceptable. The customer said, yeah, I know, that's why I'm calling. It used to take 4secs. My DBA said, no I mean the 4secs is unacceptable. Let's get that time down.

OK, maybe next week I can finally talk about batch deletes like I promised last week. Until then, remember... K-Mart sucks.

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
Friday, September 09, 2005

What Separates DBAs from Developers?

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

About Me

My Photo
Sean McCown
I am a Contributing Editor for InfoWorld Magazine, and a frequent contributor to SQLServerCentral.com as well as SSWUG.org. I live with my wife and 3 kids, and have practiced and taught Kenpo for 22yrs now.
View my complete profile

Labels

Blogumulus by Roy Tanck and Amanda Fazani

Page Views