Friday, September 09, 2005
What Separates DBAs from Developers?
08:49 | 
Posted by
Sean McCown
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:
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
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
Subscribe to:
Post Comments (Atom)
About Me
- 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.
Page Views
41 comments:
Someone has a chip on thier shoulder =). I am a Sr. Developer and I can tell I have seen my share of shit from DBA's. Don't make me post some examples.
Its not if you are Coder or a DBA, its, if your any good at your job.
This is systemic and applies to all careers, non-IT as well.
I could not agree with the previous comment more. I'm not a DBA by a long shot, but I know SQL Server very well. I understand the set-based mindset that is necessary to write high-performance SQL, I understand how the storage engine works, how the query optimizer works and so on - I just don't have the mindset necessary to be a good DBA and I know it. Still, there's no way that I would ever be caught dead writing any of the code that you describe.
Your "All DBAs are great and all devs suck" argument just falls flat. If you're using a tool you don't understand, your code is bound to suck, regardless of your job title or position. I know people who (at least according to their employers) are DBAs who would have difficulty writing a stored procedure, and need to look in Books Online to find the Index Tuning Wizard. No, I'm not exaggerating. Yes, developers are probably more likely to write shitty SQL code than are DBAs, but you could also argue that DBAs are equally likely to write shitty GDI+ code - the big difference is that devs are always asked to write SQL, and DBAs are never asked to write "real" code outside the database.
Still, I'll agree that any skilled professional will learn what he/she needs to know before starting to work with any new tool, and that the devs in your anecdotal examples certainly deserve a bullet or two. But I'll counter with an anecdote of my own. A dev friend of mine is currently working on a project where he needs to rewrite 100 or so stored procedures written by his new employer's DBA, because every single one of the stored procedures involves opening a cursor (SELECT * with no WHERE clause) on each table involved in the query, and then looping through to find the row or rows affected. Performance was fine when there were a few hundred records and a few dozen users; obviously performance went straight downhill as the database grew.
I assume you'll respond by saying that this wasn't a real DBA. I'll beat you to it by saying that the people in your examples weren't real developers.
Please - next time you rant, why not rant about the lack of skills in most IT people, and not try to turn it into an "us vs. them" diatribe?
And don't forget that the entire database engine is written by developers. The problem here is that you have bad DATABASE developers attempting to do DATABASE development.
This is a common mistake made by companies when they hire developers. They assume that a programmer who is competent with funcitonal programming will be able to pick up database programming instantly. This just isn't true and it is necessary to really pound in the set-based programming into someone who has never had to deal with perf in a database. This is fairly easy to do with a simple book and some mentoring if the developer is solid, however.
Just my two cents...
I have spent the best part of 20 years as a developer in areas varying from COBOL, RPG & Pick to VB, Delphi & .NET and the last couple of years as an Oracle/SQL Server DBA and I would have to say there is a vast majority of developers who think they know how to write a query but are really only kidding themselves (in most university courses database theory is generally only 1 or 2 units).
Here ar 2 examples of developers "work" from where I am currently working
a stored procedure name (one of many like it):
usp_SEL_get_committees_membered_W_committee_member__not_zero_committee_member__position_
I am guessing that it was dumb luck that they prefixed it with "usp_"
This one the developer asked me why this view ran slowly or on ocassions not at all
CREATE VIEW DBO.v1033_service_call AS
SELECT T1.ser_oid AS object_id
,T1.ser_description AS description
,T1.ser_id AS id
, CAST(563019801 AS NUMERIC) AS item_type
,T1.ser_actualcost AS actual_cost
,T1.ser_actualduration AS actual_duration
,T1.ser_actualfinish + CAST(8.0 AS DECIMAL)/24 AS actual_finish
,T1.ser_actualstart + CAST(8.0 AS DECIMAL)/24 AS actual_start
,T2.per_name AS caller_name
,T1.ser_caller_per AS caller_object_id
,T4.rct_name AS category
,T1.ser_cat_oid AS category_object_id
,T6.cdl_name AS category_service_today_
,T1.ser_cat_oid AS category_service_today_object_
,T7.per_name AS change_manager_name
,T1.ser_initiator_per_oid AS change_manager_object_id
,T9.cdl_name AS classification
,T1.ser_cla_oid AS classification_object_id
,T11.cdl_name AS closure_code
,T1.ser_clo_oid AS closure_code_object_id
,T12.cit_searchcode AS configuration_item_search_code
,T12.cit_id AS configuration_item_id
,T1.ser_cit_oid AS configuration_item_object_id
,T13.scf_sershorttext2 AS contact_phone
,T1.ser_deadline + CAST(8.0 AS DECIMAL)/24 AS deadline
,T13.scf_scdate7 + CAST(8.0 AS DECIMAL)/24 AS deadline_fired
,T13.scf_scdate6 + CAST(8.0 AS DECIMAL)/24 AS deadline_saved
,T1.ser_earlyfinish + CAST(8.0 AS DECIMAL)/24 AS early_finish
,T1.ser_earlystart + CAST(8.0 AS DECIMAL)/24 AS early_start
,T14.per_name AS entered_by_person_name
,T1.slc_ser_per_oid AS entered_by_person_object_id
,T13.scf_scnumber9 AS escalation_action
,T13.scf_scnumber10 AS escalation_phase
,T16.cdl_name AS faq_group
,T1.ser_faq_oid AS faq_group_object_id
,T18.rct_name AS folder
,T1.ser_poo_oid AS folder_object_id
,T1.ser_frequentlyaskedquestion AS frequently_asked_question
,T20.org_name1 AS globals_name_1
,T13.scf_org1_oid AS globals_object_id
,T22.cdl_name AS impact
,T1.ser_imp_oid AS impact_object_id
,T25.cdl_name AS impact_
,T13.scf_cod1_oid AS servicecallcode1
,T26.sei_information AS information_and_detail
,T1.ser_latefinish + CAST(8.0 AS DECIMAL)/24 AS late_finish
,T1.ser_latestart + CAST(8.0 AS DECIMAL)/24 AS late_start
,T1.ser_maximumduration AS max_duration
,T28.cdl_name AS medium
,T1.ser_med_oid AS medium_object_id
,T13.scf_sershorttext8 AS next_rule
,T13.scf_boolean1 AS one_hundred_sp
,T29.org_name1 AS organization_name_1
,T1.ser_caller_org AS organization_object_id
,T13.scf_sershorttext3 AS original_service
,T1.ser_plannedcost AS planned_cost
,T1.ser_planduration AS planned_duration
,T1.ser_planfinish + CAST(8.0 AS DECIMAL)/24 AS planned_finish
,T13.scf_sershorttext4 AS planned_outages
,T1.ser_planstart + CAST(8.0 AS DECIMAL)/24 AS planned_start
,T31.cdl_name AS priority
,T1.ser_pri_oid AS priority_object_id
,T13.scf_sershorttext1 AS priority_
,T13.scf_sershorttext10 AS reasonfired
,T13.scf_sershorttext9 AS reasonsaved
,T13.scf_scdate8 + CAST(8.0 AS DECIMAL)/24 AS renegotiated_date
,T13.scf_scnumber8 AS renegotiated_indicator
,T34.cdl_name AS repair_type
,T13.scf_cod4_oid AS repair_type_object_id
,T35.per_name AS requestor_name
,T1.ser_requestor_per_oid AS requestor_object_id
,T13.scf_scnumber7 AS resolution_indicator
,T13.scf_scdate10 + CAST(8.0 AS DECIMAL)/24 AS resolution_target
,T38.cdl_name AS response
,T13.scf_cod3_oid AS response_object_id
,T13.scf_scnumber6 AS response_indicator
,T13.scf_scdate9 + CAST(8.0 AS DECIMAL)/24 AS response_target
,T13.scf_sershorttext6 AS serial_number
,T39.srv_name AS service_name
,T39.srv_id AS service_id
,T1.ser_srv_oid AS service_object_id
,T40.sel_name AS service_level
,T1.ser_sel_oid AS service_level_object_id
,T41.sla_name AS sla_name
,T41.sla_id AS sla_id
,T1.ser_sla_oid AS sla_object_id
,T42.scs_solution AS solution
,T1.ser_sourceid AS source_id
,T44.rct_name AS status
,T1.ser_sta_oid AS status_object_id
,T46.rct_name AS status_service_today_
,T1.ser_sta_oid AS status_service_today_object_id
,T47.tem_name AS template_name
,T1.ser_tem_oid AS template_object_id
,T50.cdl_name AS third_party_status
,T13.scf_cod10_oid AS third_party_status_object_id
,T53.cdl_name AS urgency
,T13.scf_cod2_oid AS urgency_object_id
,T1.ser_waitforwo AS wait_for_work_order_completion
,T54.scw_workaround AS workaround
,T1.ser_apt_nrofapproversrequired AS approvers_required
,T1.ser_apt_deadline + CAST(8.0 AS DECIMAL)/24 AS approval_deadline
,T55.wog_name AS approval_group_name
,T1.ser_apt_wog_oid AS approval_group_object_id
,T57.cdl_name AS approval_status
,T1.ser_apt_status AS approval_status_object_id
,T1.ser_apt_description AS approval_description
,T59.cdl_name AS assignment_priority
,T1.ser_assignpriority AS assignment_priority_object_id
,T61.cdl_name AS assignment_status
,T1.ser_assignstatus AS assignment_status_object_id
,T1.ser_assign_extdeadline + CAST(8.0 AS DECIMAL)/24 AS external_deadline
,T62.per_name AS from_person_name
,T1.ser_ass_per_from_oid AS from_person_object_id
,T63.wog_name AS from_workgroup_name
,T1.ser_ass_wog_from_oid AS from_workgroup_object_id
,T64.sai_ass_information AS information_from_sender
,T1.ser_assign_referencenumber AS reference_
,T65.org_name1 AS to_external_organization_name_
,T1.ser_ass_contr_out_org AS to_external_organization_objec
,T66.per_name AS to_external_person_name
,T1.ser_ass_contr_out_per AS to_external_person_object_id
,T67.per_name AS to_person_name
,T1.ser_ass_per_to_oid AS to_person_object_id
,T68.wog_name AS to_workgroup_name
,T1.ser_ass_wog_oid AS to_workgroup_object_id
,T1.ser_attachment_exists AS attachment_exists
,T1.reg_created + CAST(8.0 AS DECIMAL)/24 AS created
,T69.acc_showname AS created_by_display_name
,T1.reg_created_by_oid AS created_by_object_id
,T1.reg_modified + CAST(8.0 AS DECIMAL)/24 AS modified
,T70.acc_showname AS modified_by_display_name
,T1.reg_modified_by_oid AS modified_by_object_id
,T71.inc_description AS subcontract_call_from_incident
,T71.inc_id AS subcontractcallfromsupportedev
,T1.scc_from_inc_oid AS scc_from_inc_oid
,T72.ser_description AS subcontract_call_from_servicec
,T72.ser_id AS ser_id
,T1.scc_from_ser_oid AS scc_from_ser_oid
FROM ((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((( itsm_servicecalls T1
LEFT OUTER JOIN itsm_servicecalls T72 ON (T72.ser_oid = T1.scc_from_ser_oid))
LEFT OUTER JOIN itsm_incidents T71 ON (T71.inc_oid = T1.scc_from_inc_oid))
LEFT OUTER JOIN rep_accounts T70 ON (T70.acc_oid = T1.reg_modified_by_oid))
LEFT OUTER JOIN itsm_workgroups T68 ON (T68.wog_oid = T1.ser_ass_wog_oid))
LEFT OUTER JOIN itsm_persons T67 ON (T67.per_oid = T1.ser_ass_per_to_oid))
LEFT OUTER JOIN itsm_persons T66 ON (T66.per_oid = T1.ser_ass_contr_out_per))
LEFT OUTER JOIN itsm_organizations T65 ON (T65.org_oid = T1.ser_ass_contr_out_org))
LEFT OUTER JOIN itsm_ser_ass_information T64 ON (T64.sai_ser_oid = T1.ser_oid))
LEFT OUTER JOIN itsm_workgroups T63 ON (T63.wog_oid = T1.ser_ass_wog_from_oid))
LEFT OUTER JOIN itsm_persons T62 ON (T62.per_oid = T1.ser_ass_per_from_oid))
LEFT OUTER JOIN itsm_codes T60 ON (T60.cod_oid = T1.ser_assignstatus))
LEFT OUTER JOIN itsm_codes_locale T61 ON (T61.cdl_cod_oid = T60.cod_oid))
LEFT OUTER JOIN itsm_codes T58 ON (T58.cod_oid = T1.ser_assignpriority))
LEFT OUTER JOIN itsm_codes_locale T59 ON (T59.cdl_cod_oid = T58.cod_oid))
LEFT OUTER JOIN itsm_codes T56 ON (T56.cod_oid = T1.ser_apt_status))
LEFT OUTER JOIN itsm_codes_locale T57 ON (T57.cdl_cod_oid = T56.cod_oid))
LEFT OUTER JOIN itsm_workgroups T55 ON (T55.wog_oid = T1.ser_apt_wog_oid))
LEFT OUTER JOIN itsm_ser_workaround T54 ON (T54.scw_ser_oid = T1.ser_oid))
LEFT OUTER JOIN rep_templates T47 ON (T47.tem_oid = T1.ser_tem_oid))
LEFT OUTER JOIN rep_codes T45 ON (T45.rcd_oid = T1.ser_sta_oid))
LEFT OUTER JOIN rep_codes_text T46 ON (T46.rct_rcd_oid = T45.rcd_oid))
LEFT OUTER JOIN itsm_ser_solution T42 ON (T42.scs_ser_oid = T1.ser_oid))
LEFT OUTER JOIN itsm_service_level_agreements T41 ON (T41.sla_oid = T1.ser_sla_oid))
LEFT OUTER JOIN itsm_service_level T40 ON (T40.sel_oid = T1.ser_sel_oid))
LEFT OUTER JOIN itsm_services T39 ON (T39.srv_oid = T1.ser_srv_oid))
LEFT OUTER JOIN itsm_persons T35 ON (T35.per_oid = T1.ser_requestor_per_oid))
LEFT OUTER JOIN itsm_codes T30 ON (T30.cod_oid = T1.ser_pri_oid))
LEFT OUTER JOIN itsm_codes_locale T31 ON (T31.cdl_cod_oid = T30.cod_oid))
LEFT OUTER JOIN itsm_organizations T29 ON (T29.org_oid = T1.ser_caller_org))
LEFT OUTER JOIN itsm_codes T27 ON (T27.cod_oid = T1.ser_med_oid))
LEFT OUTER JOIN itsm_codes_locale T28 ON (T28.cdl_cod_oid = T27.cod_oid))
LEFT OUTER JOIN itsm_ser_information T26 ON (T26.sei_ser_oid = T1.ser_oid))
LEFT OUTER JOIN itsm_codes T21 ON (T21.cod_oid = T1.ser_imp_oid))
LEFT OUTER JOIN itsm_codes_locale T22 ON (T22.cdl_cod_oid = T21.cod_oid))
LEFT OUTER JOIN rep_codes T17 ON (T17.rcd_oid = T1.ser_poo_oid))
LEFT OUTER JOIN rep_codes_text T18 ON (T18.rct_rcd_oid = T17.rcd_oid))
LEFT OUTER JOIN itsm_codes T15 ON (T15.cod_oid = T1.ser_faq_oid))
LEFT OUTER JOIN itsm_codes_locale T16 ON (T16.cdl_cod_oid = T15.cod_oid))
LEFT OUTER JOIN itsm_persons T14 ON (T14.per_oid = T1.slc_ser_per_oid))
LEFT OUTER JOIN itsm_ser_custom_fields T13 ON (T13.scf_ser_oid = T1.ser_oid))
LEFT OUTER JOIN itsm_configuration_items T12 ON (T12.cit_oid = T1.ser_cit_oid))
LEFT OUTER JOIN itsm_codes T10 ON (T10.cod_oid = T1.ser_clo_oid))
LEFT OUTER JOIN itsm_codes_locale T11 ON (T11.cdl_cod_oid = T10.cod_oid))
LEFT OUTER JOIN itsm_codes T8 ON (T8.cod_oid = T1.ser_cla_oid))
LEFT OUTER JOIN itsm_codes_locale T9 ON (T9.cdl_cod_oid = T8.cod_oid))
LEFT OUTER JOIN itsm_persons T7 ON (T7.per_oid = T1.ser_initiator_per_oid))
LEFT OUTER JOIN itsm_codes T5 ON (T5.cod_oid = T1.ser_cat_oid))
LEFT OUTER JOIN itsm_codes_locale T6 ON (T6.cdl_cod_oid = T5.cod_oid))
LEFT OUTER JOIN rep_codes T3 ON (T3.rcd_oid = T1.ser_cat_oid))
LEFT OUTER JOIN rep_codes_text T4 ON (T4.rct_rcd_oid = T3.rcd_oid))
LEFT OUTER JOIN itsm_persons T2 ON (T2.per_oid = T1.ser_caller_per))
INNER JOIN itsm_ser_custom_fields T19 ON (T19.scf_ser_oid = T1.ser_oid))
LEFT OUTER JOIN itsm_organizations T20 ON (T20.org_oid = T19.scf_org1_oid))
INNER JOIN itsm_ser_custom_fields T23 ON (T23.scf_ser_oid = T1.ser_oid))
LEFT OUTER JOIN itsm_codes T24 ON (T24.cod_oid = T23.scf_cod1_oid))
LEFT OUTER JOIN itsm_codes_locale T25 ON (T25.cdl_cod_oid = T24.cod_oid))
INNER JOIN itsm_ser_custom_fields T32 ON (T32.scf_ser_oid = T1.ser_oid))
LEFT OUTER JOIN itsm_codes T33 ON (T33.cod_oid = T32.scf_cod4_oid))
LEFT OUTER JOIN itsm_codes_locale T34 ON (T34.cdl_cod_oid = T33.cod_oid))
INNER JOIN itsm_ser_custom_fields T36 ON (T36.scf_ser_oid = T1.ser_oid))
LEFT OUTER JOIN itsm_codes T37 ON (T37.cod_oid = T36.scf_cod3_oid))
LEFT OUTER JOIN itsm_codes_locale T38 ON (T38.cdl_cod_oid = T37.cod_oid))
INNER JOIN rep_codes T43 ON (T43.rcd_oid = T1.ser_sta_oid))
INNER JOIN rep_codes_text T44 ON (T44.rct_rcd_oid = T43.rcd_oid))
INNER JOIN itsm_ser_custom_fields T48 ON (T48.scf_ser_oid = T1.ser_oid))
LEFT OUTER JOIN itsm_codes T49 ON (T49.cod_oid = T48.scf_cod10_oid))
LEFT OUTER JOIN itsm_codes_locale T50 ON (T50.cdl_cod_oid = T49.cod_oid))
INNER JOIN itsm_ser_custom_fields T51 ON (T51.scf_ser_oid = T1.ser_oid))
LEFT OUTER JOIN itsm_codes T52 ON (T52.cod_oid = T51.scf_cod2_oid))
LEFT OUTER JOIN itsm_codes_locale T53 ON (T53.cdl_cod_oid = T52.cod_oid))
INNER JOIN rep_accounts T69 ON (T69.acc_oid = T1.reg_created_by_oid))
WHERE ((T4.rct_lng_oid IS NULL OR T4.rct_lng_oid = 1033)
AND (T6.cdl_lng_oid IS NULL OR T6.cdl_lng_oid = 1033)
AND (T9.cdl_lng_oid IS NULL OR T9.cdl_lng_oid = 1033)
AND (T11.cdl_lng_oid IS NULL OR T11.cdl_lng_oid = 1033)
AND (T16.cdl_lng_oid IS NULL OR T16.cdl_lng_oid = 1033)
AND (T18.rct_lng_oid IS NULL OR T18.rct_lng_oid = 1033)
AND (T22.cdl_lng_oid IS NULL OR T22.cdl_lng_oid = 1033)
AND (T25.cdl_lng_oid IS NULL OR T25.cdl_lng_oid = 1033)
AND (T28.cdl_lng_oid IS NULL OR T28.cdl_lng_oid = 1033)
AND (T31.cdl_lng_oid IS NULL OR T31.cdl_lng_oid = 1033)
AND (T34.cdl_lng_oid IS NULL OR T34.cdl_lng_oid = 1033)
AND (T38.cdl_lng_oid IS NULL OR T38.cdl_lng_oid = 1033)
AND (T44.rct_lng_oid = 1033)
AND (T46.rct_lng_oid IS NULL OR T46.rct_lng_oid = 1033)
AND (T50.cdl_lng_oid IS NULL OR T50.cdl_lng_oid = 1033)
AND (T53.cdl_lng_oid IS NULL OR T53.cdl_lng_oid = 1033)
AND (T57.cdl_lng_oid IS NULL OR T57.cdl_lng_oid = 1033)
AND (T59.cdl_lng_oid IS NULL OR T59.cdl_lng_oid = 1033)
AND (T61.cdl_lng_oid IS NULL OR T61.cdl_lng_oid = 1033))
There should be no separation from DBAs and developers. They should be one and the same person, in that one person should write all of the code. That means that front end and back end development should be done by the same person.
If you are going to program against a relational database, then you have to understand the relational model as well as the technical model used to implement it. Nobody should be given a database programming position without understanding relational databases like Oracle or SQL Server. I would say that there are two main types of programmer, the high level programmer who is responsible for producing business systems using compilers and visual development tools, and the programmers who write these compilers and visual tools. (The easy stuff .v. the hard stuff) The database application programmer can get through their entire career without ever having to understand or use lower-level programming methods and constructs. However, in return, that spare capacity in their brains afforded by not having to understand this side of programming should be devoted to understanding the relational model, SQL, stored procedures, query optimisation, row locking table locking etc.
In my team, the Oracle developers, DBA, and Microsoft VB developers all reside in the one room. Fine for a small team, but at least everyone is aware of each other and knows where to turn for advice.
I find the examples given of bad programming to be appalling. It's all very sad.
I think its unfair to use a blanket statement "DBA good, Developer bad". I have played both roles, and ultimately, I have seen brilliant DBA's and very bad DBAs, just like I've seen excellent Developers, and guys that were lost causes.
I have seen a developer write T-SQL code that ran so fast and efficiently, it would make your head spin.
Ultimately, whether you are a developer or DBA, you have your strengths and weaknesses; but blaming them on a job-title is both infantile and dangerous.
If I were a non-IT person who did not know the difference between the two (Dev vs DBA), and I had to hire someone, well after reading this article I wouldn't touch a developer with a 10-foot pole.
Rather make sure future blogs are based on facts, not emotions!
hey, and a DBA has many hats. You can be a modeler, a coder, a server side guy, etc. A person may be passable in all but excellent as one and that's what you need to round out your team.
I've been playing at dba work for a long time now. Don't consider myself an expert by any means. Have interviewed so many who do consider themselves experts... and they almost never are.
Humility and a willingness to listen are as important skill sets as the ability to be a code jockey. And that's true for developers and dba's.
Blanket statements are bad, so I can see why the developers are getting irritated. We had a similar situation here though - I'll keep everything generic & anonymous....developer AND previous DBAs saw & touched this code. SPs ran to update some tables to be used by web site. Nested cursors everywhere...10s of thousands of rows being processed when they didn't need to be. Took up to 10 hours at times. Using basic temp tables, relational modeling, and set-driven processes, I got it down to < 5 seconds. Amazing.
I can see his point, but there are stupid people everywhere, not just developers.
jerk
The problem I see is that most books don't address either good database design or good performance design. They show examples that work on a small scale, but have problems when the size of the database grows. Do you have some books you could recommend for best practices for DBAs and coders alike?
Like many of the posts here, I am a developer with a stronger background in database development than the DBAs I work with.
If you have developers that are coding the examples shown, you need to talk to their manager. They are giving the rest of the community a bad name.
The real question should be "What Separates assinine know-it-all's from team players?".
Can't say I'm a DBA, but I know enough to have been entrusted with DBA responsibilities, to run performance tests, and to spot poorly performing queries.
On that last point, when that happens, I gently approach the developer and suggest alternatives, in a friendly and productive manner.
Your rant tells everyone way more about you than any of those so-called "poorly educated developers".
You haven't got a leg up on the rest of the world just because you know your data stuff.
I can't imagine what it would be like if other professions were as populated with supercilious, superior people like you. I think it would be hell.
Education is a shared responsibility, don't you think?
The real question should be "What separates assinine know-it-all's from team players?".
Can't say I'm a DBA, but I know enough to have been entrusted with DBA responsibilities, to run performance tests, and to spot poorly performing queries.
On that last point, when that happens, I gently approach the developer and suggest alternatives, in a friendly and productive manner. This tends to effectively re-direct the developers' practice. Plus, we make friends to boot.
Your rant tells everyone way more about you than any of those so-called "poorly educated developers".
You haven't got a leg up on the rest of the world just because you know your data stuff.
I can't imagine what it would be like if other professions were as populated with supercilious, superior people like you. I think it would be hell.
Education is a shared responsibility, don't you think?
BTW, I am a developer.
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?
Shouldnt the DBA solution be Select BATCHID from BatchNumbers where BatchID = '12345'
LOL
Its a nice article but I think the developers are geeting generalised here.
I am a DBA(experienced) and I have to disagree with your comments. I have known a lot of developers like the ones you describe but I have met others that are remarkably brilliant! Therefore you should not make claims like that. Statements like this only shows lack of respect for other people and mind narrowness. How would you feel If someones proof you wrong on one of your "accomplishments" and they choose to offend you for that?
You need to be a bit more humble and learn that there are others better than you!!
Having one up man ship is not a very healthy attitude. Granted there are bad Developer and bad DBA’s but all developers are bad or stupid. Certainly is not the logical way of approaching the argument. I am a developer/DBA I consider with all my knowledge I have miles to go.. Humility should be your basic key to knowledge.
I'm a developer in a shop without a DBA. I've inherited a lot of very poor developer code. It's generally along the lines of load every table in the database into arrays and then walk through the data client-side with a myriad of lookup functions. It's appalling. So I agree that there are many developers that shouldn't be writing SQL code, but many of those developers probably shouldn't be writing any code at all.
I also don't think it's fair to divide people into dev vs dba camps. I know I have much to learn from a good dba, and that some of my SQL code could benefit from professional scrutiny. In fact, I've been trying to get management to understand that.
The general sentiment that the problem is one of education is one that I can agree with. I don't think I should be thrown into the same category as bad developers to be despised by good and bad dbas alike, but I won't get emotional about it either. Let's agree that there is a problem and deal with it.
Funny that all of the developers responding to this thread are so touchy on this topic. Did Sean hit a nerve??? Feeling a little under-skilled, perhaps?
If you really did have the skills you all claim to have, I don't think you would have taken this personally. It's funny how people freak out when their true form is revealed to them.
Signed,
A developer who works extensively with SQL Server, and nodded in agreement while reading this post...
This blog and the article at http://www.sqlservercentral.com/columnists/sMcCown/interviewspart2.asp are by the same author. The blog seems to say developers are pathetic. The article seems to say many DBAs are pathetic. I guess only the author is competent.
Where DOES such an attitude come from? Sadly, it seems all-to-common in the SQL Server world.
The full URL is http://www.sqlservercentral.com/columnists/sMcCown/interviewspart2.asp
Hmmm...
Something insists on truncating the URL.
This is the last part - interviewspart2.asp
After reading all of the comments on this blog is can sympathize with the developers and DBAs. The problem here is that there has been no clear definition of a DBAs responsibilities.
The DBAs where I work (SQL Server/Oracle) spend the vast majority of their time with database infrastructure support. This can be anything from setting up a new server to correcting sp_sendmail issues to restoring a database backup. They are good at this but most of them are not great at query optimization.
This is the problem I have seen most of all. The DBAs blame the developers for writing bad queries and the developers blame the DBAs for not keeping the servers running. Instead they should be trying to find out how to correct the problems. The DBAs would love to work closer with the developers on the queries, but there time is very limited. We are trying to identify the developers in our group that have good SQL skills to assist the others. This way we only have to have regular, short reviews with the DBAs instead a lot of their time.
- Jason
I work for a small company. We are separated with SQL DBA/Developers and Functional Developers. I find that the functional developers code is not always up to par, but then again, either are the SQL Developers. I like the idea of Team Development. If the experts could spend more time teaching on both fronts, more and more people would be better off. If you are an expert, then share your knowledge.
The part that irritates me the most during an interview, is when the people screened from the recruiters make it to a technical interview. If they performed better screening, the technical leads (experts) wouldn't feel like it was a waste of time.
I am a Sr. DBA for Sybase & SQL Server with 18 years of experience. Having worked as both DBA and developer -- I converted 12 years back to DBA :-) --, all that I have to say about your rant is: You are one angry dude.
Take a vacation.
Author is a great DBA (I guess) but a shit web developer if content doesn't even wrap properly. Maybe he should have hired a "real" web dev instead of trying to write his own web code.
I could come up with a few examples of developers having screwed up in my shop and having to re-write their queries or PL/SQL procedures for them. I can probably come up with examples of places we still have broken select statements in applications that could be optimized a lot further. I just fixed an application last month that was doing work in too large of a batch size before committing, resulting in transaction logs filling up, and that's a bad problem.
However, it is my philosophy that developers need to be "owners" and not "renters" of the database. In the event your shop uses home-grown software, Developers need to participate in the design process of the underlying table structure. As a DBA you have to be a guiding force, helping those developers that are not adept design to design the database schema. You also have to be present during testing to catch some of the less optimal statements or procedures prior to going live. These things can all be done with polite and constructive feedback, which at the end, results in a developer who feels like they own their application from stem to stern and wants to help the application function and perform well. That desire extends to the backend. I can rely on my developers to help when their application isn't working or performing as desired by clients or by management.
The problem is never as simple as "developers are no good at writing SQL". The problem may extend to the database being a black box that the developer has no ownership of, which is, in my opinion, wrong. Or, it may extend to a design and deployment process that lacks adequate requirement gathering and load testing.
If your enterprise is not good at coming up with use cases and requirements prior to the actual writing of the code, it's no wonder the developer doesn't have a clue. It's the six P's - Prior Planning Prevents Piss Poor Performance.
Bottom line: If the developer has no ownership of the backend database structures he or she is working with, then he or she has no interest in making it fast. At that point, you're all on your own as a DBA.
As a DBA if you want things to change you have to work with people. You can't just sit in your closet and complain about how bad the SQL is.
I pity you for working with such morons who dare to call them selves developers.
Developers by nature are curious and keen to imerse themselves in whatever technology they can.
The examples you gave are so far fetched it's hard to believe they are real.
I am a DBA and I completely agree with the point that when developers are under pressure most of the time they will write any kind of crap code. It's not just the SQL code it's true for any code they write.
I suspect this was written in humour, but I did nod my head in agreement with the article and LOL at some of the comments.
Step back and think before you flame... it's not personal, it sounds to me like a general exaggerated humorous rant.
IMHO, a lot of developers do not care about SQL when they can write some VB code instead.
I've also seen some DBAs who I would trust with a calculator let alone a database.
< HUMOUR>
BTW, I stoped developing a while ago. After I fully developed, I became a DBA...
< /HUMOUR>
IMHO, narrow minded people get offended and broad minded people wait for others to review their code to get some comments / critics that will help them grow & learn more
I would rather take critics.... :)
Hello All,
I have been a DBA for over 8 years now. I support 1800 users in a 24/7 shop. I have no formal training in the subject and got my start in the industry as a telephone tech at Packard Bell. I say all this to prove a point. If you have a passion for what you do and the tenacity to stay in the game you will grow stronger in your skill sets.
My development as a DBA reminds me of that scene in Conan the Barbarian. The scene starts with him as a boy he is pushing the log around in a circle feet bairly touching the ground he is dragged by others. Then the next scene shows him as older pushing the whole system around by himself. Like Connan I didn't give up and let others pick up my slack. I pushed back and stayed in pace with the DB industry.
Obviously not everyone is going to have the same commitment to the subject, nor shoud they. I'm just stating the fact that good DBA's are homegrown and self motivated.
Think about this, 1978 was the year Edgar F. Codd's idea for relational databases became a reality. Since then (27 years) more has happaned in the database space then imaginable. So much so that there is scarcly any concept that don't become updated/tweeked/expanded in a year or two. That and I have never seen any degree in the database subject that dealt with the current industry or even a good treatment of the subject in a college course! So to improve oneself one must take it all on themselves. Utilizing trial and error and an endless KB of past experience to guide them forward. That an newsgroups!
My view of the writers frustration with developers I believe is this.
Many people look at SQL as easy and therin lies the problem. Yes you don't need to create/compile a DLL then execute an assembly to run a select statment. So any sql statment is like any assembly if it complies with out errors and returns the correct result it's good enough right? Cpu's are so fast these days and memory so plentiful who cares if one program takes a second or so longer then another design, just as long as they both arrive at the same result right?
This flies in the face of what I as a DBA sees as important. I look at the entire server as a holistic entity the many pieces working togther. I'm not just concerned about my SQL code. I care about all code. If one query design runs a second slower then another and is used by 100's of users several 1000 times a day that is alot of contention. So I try to make sure I get the most I can from my queries.
Where I see that the author my go astray is in venting frustration about developers. Let me offer a system that in the long run my work better for you then finger pointing, I'll call it partnering. for example I find a bad piece of SQL code and I print out an example. I review it to try and figure out what is the desired result and then find the programmer. I ask him to look at the SQL statement and show me where it fits into the framework of what he is trying to achieve in his/her applicaiton. I then walk them through the process of tunning the query. What ever it takes; set up a trace, look at the execution plan, look at schema and possible enhancments. Wherever the road leads me I make sure they follow and get involved with why/what I'm doing.
I do this for several reasons.
1. There is to much SQL out there being written for me to find all the poor instances of it and optimize isn't practical. On the job training helps improve my developers skills and makes him more profficent causing me less issues.
2. Often times for me it makes me better in a subject to explain the situation to another. Everyday if I'm lucky I use books online a little less.
3. Because when they have a difficult SQL issue to resolve they will come to me first and I enjoy a challenge of taking a SPROC from 1/2 hour to 5 minutes.
4. I justify the role of DBA to mangement who my be enticed to believe that a DBA is unjustfied to save $.
Many may think that training someone is a recipe for disaster. I say that no one is going to beat you up or complain if you show them a way to learns omething new that works better. the proof is in the execution. I beleive everyone like to learn new tricks. Dog's included. For some organiations this ofcourse may require some suppression of ego on either or both persons part.
Of course tunning is only 30% of my job and there are many other things I'm involved in, but that is off the developer vs. DBA subject.
One more story before I go. I worked as a v-vendor for Microsoft in Redmond, WA. On the job day 3 our group is sitting in the red room no assigments yet shooting the shit. One of the people on our DB migration team was actually a .net developer who was moonlighting as a database developer because he needed work. As we talked about the details of the project it became clear to me that he knew far less then me concerning SQL server. In my arrogance I picked up his SQL for beginners book and exclaimed, "I know every detail that is in this book." Another gentleman on our project named Mr. Fu who had been quite the entire time looks at me and asks me 5 questions I got maybe 3 right. He turned out to be the greatest mentor I've had in my carreer to date.
Lesson: Don't be greedy, frustrated, cynical, or think you know anything about anything.
Share what you know and work hard to become good at what you do whatever it may be. Build the bridge and make sure others walk it with you. Everything is perceived as easy until you devel into the details.
I do (and must) actually agrre to what you wrote. I am a developer, vb and such. But I did this for more than 15 years. I myself always tried to write efficient code (including SQL). Though, what I found as left-behinds from other developers was mostly careless (SQL) code. Just as you said: works fine on a workstation and does not in production. But how do you want to fight that?
Of course, there are deadlines but do the developers not decide themselves on deadlines?
The thing is rather that developers do not CARE. Their job is finished as soon as they have got a working solution. After they just can leave it all behind and start working on another project.
Fire-and-forget!
I better stop here before getting furious about all those careless sunday-developers.
At least it assures us consultants constant income...
Martin
I'm a DBA that started out as a developer. As someone who a little formal training in databases, but no training in SQL, I find it hard to believe that anyone who has spent any amount of time coding SQL would come up with these lame brained examples you have listed here (i.e. Select Top 1 * from tblCurrentBatch b1 where ID in (select ID from tblCurrentBatch b2 where b1.ID = b2.ID to get a single row from a single table containing that single row?) I don't care what anyones job title is, but if you're coding something like this, you shouldn't be coding in ANY kind of programming language.
I got my start in databases with a formal training in computer programming, and jumped feet first into database development with no formal SQL training. I had a friend teach me what he knew about SQL at the time, and it doesn't take a long time to figure out that SQL is made to manipulate sets of data. I think any right minded individual who produces code such as that listed in the example isn't asking themselves questions like "is this the least amount of code required to get the job done?" If someone isn't asking themselves that kind of question about their code, then I don't think they should be a developer, or a DBA.
I dont' have a beef with developers, but I do know that a lack of understanding of any technology, at any level, will ultimately lead to failures at all levels. So my advice to anyone who has anything to do with code of any language is to know that language inside out. If you're working inside a database, get to know the inner workings of that databases locking and concurrency mechanisms. Don't just treat the database like a MS Word document, because it's much more complex than that. If you don't know how to do something in the database, or have questions about the best way to do something, ask a DBA, we'll be your best friend, if you give us a chance. We are more than just the caretakers of your databases, and your applications, we are your database experts, utilize us.
Here's a comment ... thank God that sooner or later, hopefully in my lifetime, that DBA's will be relegated to legacy systems as most applications will use a file based database system that will be built-in and efficient. It's just a matter of time before the developers will invest enough time in THAT concept to make DB servers obsolete. And if you really think that isn't possible I have a few words for you ... AS400 and COBOL.
Funny article, but full of developicism (something like racism but against developers). The queries in your example are extremely dumb, sure, but most developers do NOT write such awful code, haha. Perhaps YOU should help YOUR company hire better developers? And don't start dba v. developer thing, wow, what's your point, not to get along? We are all cool folks, some know more than others, so help eahc other out!! We, developers, don't expect a DBA to write .net code, so why do you get all fussy when a developer writes SQL code? He may simply be not educated in SQL, and he not necessarily should be actually, but usually, of course, we are because it's more efficient to write SQL code ourselves than ask DBA for it. Spread some love and education, not hatred, prejudice, and false beliefs.
Dude, you need to hire some new developers. I have been a developer for 25 years and I have never seen anyone write crap like that. Either they are really bad or you made it up. No one would ever create the solutions you show. No one......
Shouldnt the DBA solution be Select BATCHID from BatchNumbers where BatchID = '12345'
LOL
No this is what a developer would write. we all ready know the batch id why would we select only the batch id
I think your problem is that you are working with .net developers. It seems any idiot can take a Microsoft course and get a job on buzzword compliance alone.
I am a developer and my SQL code looks just like yours.
"Friends don't let developers write SQL code." What an idiot you are?
You mean to say only SQL can save large software? Nothing about balance of both things? And how would you know about business logic and possible options for future enhancement? I am a software developer and but have very deep understanding of SQL (T-SQL). What is more important here to conclude is developer should have equal amount of knowledge of T-SQL (not SQL administration), to keep hits/speed ratio linear.
GREAT REVIEW!
I pretty much agree with all your thoughts you said in your post, especially at the end of your article. Thank you, your post is very valuable as always. Keep up the good work! You’ve got +1 more reader of your super blog.
AimIT Software - Web Development Companies
Post a Comment