• If you are citizen of an European Union member nation, you may not use this service unless you are at least 16 years old.

  • You already know Dokkio is an AI-powered assistant to organize & manage your digital files & messages. Very soon, Dokkio will support Outlook as well as One Drive. Check it out today!


Busy Database Antipattern

This version was saved 4 years, 11 months ago View current version     Page history
Saved by Nathan T Suver
on March 17, 2019 at 10:29:55 am



Offloading processing to a database server can cause it to spend a significant proportion of time running code, rather than responding to requests to store and retrieve data






relevant content:

- ensure the database only does 

Problem description

Many database systems can run code. Examples include stored procedures and triggers. Often, it's more efficient to perform this processing close to the data, rather than transmitting the data to a client application for processing. However, overusing these features can hurt performance, for several reasons:

  • The database server may spend too much time processing, rather than accepting new client requests and fetching data.
  • A database is usually a shared resource, so it can become a bottleneck during periods of high use.
  • Runtime costs may be excessive if the data store is metered. That's particularly true of managed database services. For example, Azure SQL Database charges for Database Transaction Units (DTUs).
  • Databases have finite capacity to scale up, and it's not trivial to scale a database horizontally. Therefore, it may be better to move processing into a compute resource, such as a VM or App Service app, that can easily scale out.


This antipattern typically occurs because:

  • The database is viewed as a service rather than a repository. An application might use the database server to format data (for example, converting to XML), manipulate string data, or perform complex calculations.
  • Developers try to write queries whose results can be displayed directly to users. For example a query might combine fields, or format dates, times, and currency according to locale.
  • Developers are trying to correct the Extraneous Fetching antipattern by pushing computations to the database.
  • Stored procedures are used to encapsulate business logic, perhaps because they are considered easier to maintain and update.



Modern database systems have evolved to run code, which can be efficient since processing is colocated with the data (typically on the same machine).  For fast computations, this might be ideal, but this leads to problems with long-running processes.  Since the database is a shared resource, this can easily become a bottleneck.  With this anti-pattern, the database becomes viewed as both a service and repository.  This article proposes moving certain types of processing out of the database and into a separate service, and analyzing the impact of the change by monitoring CPU time and DTU's (database throughput unit, which measures the performance of a mix of basic database operations https://docs.microsoft.com/en-us/azure/sql-database/sql-database-service-tiers-dtu).  They provide some basic examples of how to accomplish this, and do note that fixing this anti-pattern can cause other unintended consequences.


This is an interesting approach, and could easily be built off of with a lot more detail.  For example, 






This paper has some good overall guidelines for attacking the problem, but doesn't go far enough.  Moving code out of a stored procedure involves minimizing and maxmining desired effects, and the end result is likely not going to be optimal in all cases, the use case needs to dictate which criteria for improving quality is more important.

Comments (0)

You don't have permission to comment on this page.