| 
  • 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!

View
 

Busy Database Antipattern

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

Abstract:

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

 

 

Source:

https://docs.microsoft.com/en-us/azure/architecture/antipatterns/busy-database/

 

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.

 

    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.