How do you handle halfwit developers?
-
Partly tongue in cheek, but this is a serious administration issue.
How do you deal with developers - particularly .net ones - who think they know about data.
You know - the ones who jump in with a clr assembly that does something 'really complex, no other way to do it' that when you get into the bones of it is simply a self outer join. Perhaps ones that want to wrap everything in a com+ transaction instead of handling it safely in the database. Because what they want to do is 'so difficult' and they have to make multiple calls to insert this update that insert something else if that then means this and be able to roll it all back rather than taking the time out to model it sensibly and come up with a clean way to do it, or consult the dbas?
Or for that matter take a large 'greenfield' project and blat out some pile of code and data structures, then when asked for some kind of documentation - perhaps a simple conceptual model, or even better an entity list/diagram/matrix or you know just something that tells me what this mess is supposed to represent - suggests that I can generate an erd by reverse engineering the database cos its got foreign keys. Brilliant thanks...
Currency tables with identity keys. Relationship tables, relating tables 2 integer pks, with a new identity as its own pk.
And then expect it to handle a few hundred thousand transactions a day.
Without resorting to violence, drugs or alcohol how do you guys handle these things?
-
grraaagh.
Just noticed he's using the return code from the stored procs to pass out data. Like the id of the record he's just inserted.
I need a new job.
-
I usually resort to one of the following
pig headed stubborness
obfuscating database code
boring them to death (ok - that's not hard)
or my favourite so far has been in sql 2005 - allow only access to stored procedures by seeting up a default schema and moving the tables to a different schema. no queries without using stored procs... happy days i've finally got them under control....
alternatively play them at their own game - put database maintenance plans on when they're attempting to test changes and watch them squirm when their new code doesn't work as well as its should.
to following should slow them down a bit
declare @counter int
set @counter=0
do while @count<100
begin
dbcc checkdb mydatabase
@counter=@counter+1
end
no positive results from this - just lots of i/o and locking
MVDBA
-
oooo. Nothing like a little sabotage to escalate the wars.
I hope this thread sees some action, because this is interesting. I'm one of those half wit developers who is really good at what I do, and I use SQL server to stash and return my data. I'm not particularly good at SQL server, and this website helps me immensly.
I work on my own so I have little opportunity to learn from DBAs like you guys.
My reaction to the post was:
1 - I wouldn't want to work there either, neither as a DBA nor as a developer. It takes some major cooperation for a place to run smoothly, and it sounds like it is missing. Management problem, perhaps? (There are no technical solutions to management problems, but there are management solutions to technical problems).
2- I'd like to think that as a developer I wouldn't be so arrogant that I couldn't meet with the DBA and learn how to do things better. I'd also like to think that someone with a good attitude approaching the DBA would not be met with opposing arrogance, because it would probably be the last time I asked.
3 - Now that SQL Server can bury a lot of complex logic in programmable code inside SQL server, doesn't mean that it's the best way to do things. At the launch I concluded, listening to the questions, that were definitely some things that desperately needed to be done that way, but for the most part one should continued to use set-based stored procs. (and even I do not grant table access to my code, my inner DBA would rebel).
4 - Lastly, I genuinely wondered for some of the complaints outlined, what the issue was, from the DBAs pont of view. So I wondered if he had taken the time to try and explain the difficulties the developer's design was creating, or if the climate was too hostile to go that route.
Hmmm. Interesting.
-
I'm lucky in that i'm both developer and DBA as well. (primarilly DBA tho)
i think the problem lies in the mentality difference between developers and DBA's
if you think of the way a dba deals with DataSets we traditioanlly learn SET based operations are good - update or change every row at once. developers are usualyy forced to work on single rows and enumerate values or loop through/iterate.
just think of arrays in VB/C++/C# and see how different they are to T-SQL
i've just finished a project for a customer who's developerss had made the following cringeworthy SQL
declare @id,
create table #mytable (id int,somedata varchar(100))
insert into #mytable select id,somedata from myrealtable where somefield=x
declare curs1 cursor for select id from #mydata
open curs1
fetch next from curs1 into @id
while @@fetch_status=0
begin
delete from myrealtable where id=@id
fetch next from curs1 into @id
end
close curs1
deallocate curs1
the developer was so proud that he's managed to create a cursor and loop through some code it almost reduced him to tears when i showed him that
delet from myrealtable where somefield=x was quicker and only generated 1 transaction instead of 270 thousand individual deletes.....
says it all really
MVDBA
-
OK! Now my morning is made! And here I sit thinking I am a Lone Wolf with my imaginary delights of praying that "This will be the day that not a single developer will make a schema change to Production without it going through the DBA Team first! No More CURSORS!! NO MORE CURSORS!!!" Suffice to say "There are no technical solutions to management problems, but there are management solutions to technical problems" will be my mantra this day!
Thanks for the giggle! I needed it!
-Marti
-
Send everyone to anger management classes. That would be a good first step.
-
Developers work on one row at a time because that's what the humans who work in front of the computers do... one row of one order at a time.
Doesn't mean they shouldn't look for set based operations, but if the DBA shove back at them NO CURSORS without offering a solution then the developers will iterate through recordsets in code, which is probably worse.
'wangkhar' offered several specific complaints (return code problem, primary key problems, identity problems). I couldn't infer what the issues were. Maybe 'wangkhar' could be enticed to write an article to join the thread somewhere in this forum about the 'dumbest' things he's seen, but offer.. what was dumb, why, what should have been done instead. How about it, 'wangkhar'?
Somewhere in here I learned that Microsoft says cursors in SQL Server2005 are quite efficient and are no longer the enemy. I guess that doesn't mean you shouldn't look for a set-based way to do things though.
To Marti, I ask: When I worked in a large shop there were change control meetings daily, weekly, monthly, and development would have had to get their schema changes approved. How do you manage without that level of control?
-
To Marti, I ask: When I worked in a large shop there were change control meetings daily, weekly, monthly, and development would have had to get their schema changes approved. How do you manage without that level of control?
answer - safe in the knowledge that you won't be paged at 2am for something that was out of your conrol.
lol
MVDBA
-
'philM99' doesn't understand what the issues I have are. The suggestions 'philM99' give to write articles telling dba's why poorly implemented surrogate keys, irrational identities, and inline sql translations of simple ansi standard sql are irritating and what to do otherwise is a brilliant one. Or 'philM99' could easily find a plethora of articles (I imagine many are even on this site), and try to stay on topic by giving useful suggestions on how a dba can control the code run on the database, and how to effectively handle the developers (like 'philM99') who clearly know better. 'philM99' might like to provide information about how well he works alongside his dba, and what the stress points were and how they were resolved.
But being a halfwit developer
'philM99' would prefer to make my life difficult.... -
flame!!! right on wangkhar...
I think the key thing here is that developers have to accept that it's the DBA that has to manage the database/server for their application long after they've moved on to another project.
the DBA should therefore be forcefull enough to reject poor SQL and database design that will cause him/her difficulties in the future. many DBA's sit in the background and just complain rather than fighting their corner.
for example embedded sql in applications - recently an application was performing a union query that was dividing for parallelism and then self blocking... the easy answer is to set maxdop 1. but since it's embeded in the applicatoin and not in a stored procedure then the only option is to set maxdop 1 for the whole server ---- whch hosts 5 other databases...
it's up to the DBA to speak their mind and lay down written docuementation to the most effective people (usually project owners, not project managers) to help put you back in charge of the "database".
I've started to take an approach recently that i won't look after an internal system that hasn't passed my auditing - as the developers have not been considerate enough to check what they are doing is correct. if the dev guys want someone to look after it and stop it from falling over every day then they can do it themselves until they actually build a product that's half ways to manageable.
MVDBA
-
I tend to agree with the approach of soliciting the support of your project owners as well as taking on a collaborative and mentoring stance with the developers you work with.
You were once a rookie, too.
Perhaps asking the project managers to have DBA presence at all project meetings and code reviews would be a decent step - although if your workload is anything like mine, that's probably the last thing you want.
OK, I'm cringing as I try to gear up the nerve to ask specifically:
?What's wrong with Currency tables with identity keys? Are you saying that if you're tracking a dollar amount in a table that you shouldn't have an identity field on that table?
?How else do you define a many-to-many relationship other than "Relationship tables, relating tables 2 integer pks, with a new identity as its own pk. "
I'm only asking (as a sometimes-half-sometimes-quarter-sometimes-whole-wit) so I don't make myself or future DBA's I work with completely insane
Please don't hurt me -
-
A rather dicy thread here ...
Before SQL 2005 the same issues existed more or less. It'sjust that SQL 2005 added CLR integration (check out other threads on this site for even more verbosity on the subject). The solution all boils down 'standards'. Standards are:
- needed
- meant to be followed
- need management buy-in
- need management support
- have processes associated with them
- need to have room for exceptions
- need to evolve as things change
Absent the above mentioned ... and not resorting to violence, drugs or alcohol ... you are left with 'The First Step':
We admitted that we were powerless over the stupidity of others and that it is making our lives unmanageable, just like your databases !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Rudy, you forgot Documented.
I don't have to like the standard, but if its documented i will follow it.
Bill - evil developer and part time dba
-
Thanks for the constructive responses - I shall look into that technique.
Ok Currencies, and at least as bad: Countries.
Country Iso codes (ISO 3166) has a name, 2Char code, 3Char code and a numeric code. These are global standards. So imagine my suprise when I see a table like
idCountry int identity, iso2Code varchar(25), iso3Code varchar(25), description varchar(60), countrynumber int
What would you use on the relevant records - one of the Iso codes perhaps? No. The idCountry identity value. Of course.
so we see a customer table looking like customerid int identity, idCountry int references countries(idCountry), CurrencyId references currencies(CurrencyId (or worse just id)).
Then someone blows out the country table... can we recover it from the development environment? No, the data is different/different order. Can we recover it from the original iso source? no.
Can we easily see what country someone is from? No we have to have a lookup. Why, in the name of all that's holy to anyone, can't we just use the 2 or 3 letter code that everyone knows?
Similarly with iso 4217. Why can't I just look at a financial transaction and see that it was GBP rather than currencyId 7? Overhead? Minimal, not worth the effort. If you need to use a number at least use the iso number kindly supplied.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply