Who Should Write Database Code?

March 20, 2009 · 4 comments

This week, I ran into a query that did 770 GB worth of logical reads…on a 3 GB database!!!

Kimberly Tripp asked “Who’s job is it anyway?” Should database design and database coding be done by the Application Developer, the DBA, or should there be a separate “Database Developer” role? There are many variables that make this a complicated question. Here are a few of my thoughts:

The Application Developer

PRO: Database development is part of the overall application development process. Basic T-SQL syntax is pretty easy for application developers to pick up, and most should already know how to write select statements. Also, application developers already have to be familiar with the requirements of the application.

CON: The best practices for good application code don’t translate well into good database code. Application developers are trained to break everything up into small manageable steps. When they need to do something in the database, the natural tendency is to try to use the tools that work well on the application side. This often results in things like cursors, while loops, temp tables, UDFs, and lots of small steps. Database development requires a very different mind set. Also, SQL Server is a huge product. It’s hard enough for someone dedicated to SQL Server to keep up with and master it, let alone someone who is trying to do application development as well.

The DBA

PRO: DBAs work with databases. They understand indexes and are familiar with database tools like SSMS, SSIS, and Profiler. They know that cursors are not the answer to everything.

CON: Most (not all) DBAs are, as the name implies, focused on administration and don’t write code. DBAs are responsible for database uptime and stability which may get in the way of meeting development deliverables.

The Database Developer

PRO: Dedicated database developers are focused on writing good database code. They should be good at thinking in terms of set-based logic, and be familiar with the various tools that SQL Server provides. In theory, this should result in higher quality database code in less time.

CON: Many companies don’t have enough work to justify a full-time person dedicated to database development. Even if they do, it is likely spread across multiple projects which requires the database developer to keep track of multiple sets of requirements and deal with competing priorities.

Other Thoughts

While dedicated database developers should on average write better code, it doesn’t always matter if a query runs in 1-2 seconds or if it runs in 100-200 ms. The less efficient code may be “good enough.”

On the other hand, most development is done on a very small subset of data. A good database developer will know how the query will react as the amount of data increases. Will it perform the same, get linearly worse, or will performance decrease exponentially as rows are added.

If you plan on scaling your application, it’s worth it to make sure your database development is done right. To me, it doesn’t matter if the work is done by the application developer, the DBA, or a dedicated database developer. Just use the person who does the best job.

I’d love to hear your thoughts.

{ 4 comments… read them below or add one }

Jeremiah Peschka March 22, 2009 at 6:50 am

Personally, I think that a specialized Database Developer should be handling the job if scalability or performance are at all concerns. Why? Well, for starters, I am one.

Besides a shameless personal plug, I also think that a dedicated database developer is generally going to understand the data that is present in any given application and will also understand the business requirements of the application developers and can usually work with them to create a quick, scalable data access plan that is flexible enough to meet their needs while still staying performant.

Sankar Reddy March 22, 2009 at 8:36 pm

Over the years I have seen horrible practices not only by application developers but by some experienced (over 12+ years) database developers too. It comes down to who has better understanding of database concepts than just a title of database developer/dba.

The other issue we see is, corporations tend to throw hardware on a problem that could be fixed by carefully choosing the resources, spending more time during the recruitment process.

Sharing some horrible experiences.
1) Storing int values in varchar (7). After 4 years and building 5-8 different systems on top of this data model and running of loan numbers after 9.9 million, this system was scrapped to for a better model. There were other reasons also to move to a better model.
2) One table had 3 separate triggers, few of them calling cursors which had business logic per state and this code was called repeatedly.
3) 64 bit, 32 Gig, 8 core box with 40 Gigs of data runs with 60-80% cpu usage. 90 % of the tables were fragmented with over 70-100% fragmentation.
4) Defragmenting indexes once a month or still stopping the whole enterprise for 2-3 hours to defragment indexes on SQL 2K5 box. Online Indexing for them is considered bad or less efficient.
5) Performance Tuning means dropping & re-creating the SP after every 30/60 minutes.
6) Its not important to run dbcc checkdb; it takes 8-10 hours and we don’t have time is their comment. These guys should get a whipping from Paul Randal.
7) Updating statistics manually is considered bad practice.

The problem is some corporations don’t employ a (good) database architect and these so called experienced database developers/dba’s were hired by managers who had little knowledge of dbms systems. Corporations should spend more time at the recruitment process to filter out people like them who boast themselves as gurus. I was called bad sport for calling out issues like above. And my colleagues didn’t talk with me for several months until they were shown the door.

Stewart Blacklock March 24, 2009 at 6:57 am

Rob – The environment I learned and went from an application developer to an experienced database developer was a partnership. Application developers would write their SQL. The Application DBA would monitor production queries and would find problem ones. As he found bad queries, he would point out the issues to the developer and help them improve the queries. Expectation is that application developers would improve over time (which did occur)
Yes – this meant that production always had a few issues and their always is an element of rework, but it was much more scalable than having the DBA having to write them all by scratch.
On my current project we do something similar where I’m now the mentor. We are finding stored procedures that take 30 minutes…that I’ve been able to re-write down to 1-3 seconds. If I just do the re-writes though, next code that comes along will have problems…so I have to force myself to step back and get the team to understand how to do things better themselves.

Atif Shehzad November 18, 2009 at 7:51 am

I found it not efficient to let the developers code for DB objects. Functions and procedures with simple logic and with less pitfalls should be coded by developers, and DBA should take a look for optimization, before running on production server. And critical/complicated objects should be coded by DBA. I think it is a very plus point for DBA to be capable of optimized coding the required objects.

Leave a Comment