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.
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.
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.