# Busy Database: Why Too Much Work on the Database Slows Everything

> A common performance problem where the database runs work that the application should do, how to spot it, and how to fix it.

**In short:** A busy database is a performance problem where the database runs work that the application should do, which slows everything that shares it.

From "System design", a free course by Venkat R. Murty: https://designervenkat.online/learn/system-design/busy-database

## What You Will Learn

This lesson explains one common performance problem in system design. It is called Busy Database. It happens when the database server is asked to do work that belongs in the application tier. You will learn what the problem looks like, why it hurts a system as it grows, and how teams fix it. A worked example then shows the fix step by step.

Many of the words in this lesson are new to beginners. Read the key words below first, then keep them in mind as you go.

- **Antipattern:** A common way of building software that looks reasonable at first but tends to cause trouble later, such as slowness when many people use the system.
- **Application tier:** The layer of programs, often web servers, where the app's rules and logic run. It sits between the user and the stored data.
- **Database server:** A machine that stores data and answers requests to read it or change it.
- **Stored procedure:** A named block of SQL code saved inside the database. The app calls it by name to run the block.
- **Trigger:** Code inside the database that runs by itself when certain rows change.
- **Aggregation:** Combining many rows into one result, such as a total, an average, or a count.
- **Bottleneck:** The one part of a system that limits how fast everything else can go.
- **CPU:** The processing part of a computer that does calculations. High CPU use means it is working very hard.
- **I/O:** Input and output. It is the reading and writing of data to disks or across a network.
- **Timeout:** What happens when a request waits too long for an answer and is given up.
- **Throughput:** How much work a system finishes in a set amount of time.

## What Is the Core Idea?

A database has one main job. It stores data safely and returns it quickly. Many database systems can also run code, and stored procedures and triggers are the usual examples. That ability is useful, but it tempts teams to hand more and more work to the database. Building display text, doing many calculations, and sorting results for a screen can all run in the application tier instead. When the database does this extra work, it has less time left for the requests that only it can answer.

Busy Database is the name for this pattern. The database becomes busy running code, so it responds more slowly to everyone. Because the database is shared, the slowdown is felt across the whole system, not just on one page.

## Why Do Teams Fall Into It?

The problem rarely comes from one bad decision. It usually grows out of habits that seem sensible when they start.

The sections below describe the common causes. Knowing them helps you spot the pattern in a design before it causes trouble.

### What Are the Common Causes?

- **The database as a helper:** Some teams treat the database as a service that should hand back finished results. A repository, by contrast, simply keeps the raw facts safe.
- **Fixing too much fetching:** A team fetches more data than it needs. To fix that, it moves filtering and calculation into SQL, and the database starts doing heavy work.
- **Display-ready queries:** Developers write queries whose output goes straight to the screen. These queries join text fields and format dates and money to suit each region.
- **Rules inside procedures:** Business rules are placed inside stored procedures because that seems easier to update in one place.

## How Do You Recognize It?

You can spot Busy Database in two places. The first is the code that runs inside the database. The second is the set of numbers that show how the system behaves when many people use it.

### Signs in the Queries

- **Text building:** The query joins strings, adds spaces, or pads names to make a label. The app could build the same label with simpler code.
- **Date and money formatting:** The SQL turns dates or amounts into display text. Formatting is a display job, and display usually belongs to the app.
- **Row-by-row math:** A loop, sometimes called a cursor, walks through rows one at a time and does a calculation for each one. The app could do this work after the rows arrive.
- **Business rules in procedures:** A stored procedure holds discount rules, approval checks, or sort order for a page.

> **Tip:** Look at the statements that run while the database is busy. Some of them are real data access, such as reading one order. Only the work that the app could do is the warning sign.

### Signs in the Numbers

- **High CPU and I/O on the database:** The database server stays busy, even while the app servers have plenty of spare capacity.
- **Rising response time:** Requests take longer and longer as the data grows and the load increases.
- **Timeouts:** Requests give up while they wait for the database to answer.
- **Uneven load:** The database is near its limit while the app tier sits mostly idle. That imbalance is a strong hint that work is in the wrong place.

Watch for a pattern in which the slowdown gets worse as usage grows. A system that is quick for one user but slow for many often has work piled on the database.

## Check Your Recognition

**Quick check:** A team's reporting screen gets slower every month. Database CPU sits at 95%, while the app servers sit at 15%. The queries join strings, add up totals row by row, and format dates in SQL. What is the antipattern, and what is the first change you would make?

A. Too few app servers. First, add more app servers so the reports finish faster.
B. Busy Database. First, move the text, date, and row-by-row work out of SQL and into the app tier.
C. A slow network. First, buy a faster link between the app and the database.
D. Heavy caching. First, cache every report forever.

**Answer:** B. The database is doing display and per-row work while the app tier sits nearly idle. That is Busy Database. Adding app servers would not help, because they are not the busy part. The first step is to move the formatting and row math into the app, and leave simple data reads in SQL.

## Why Does It Hurt as You Grow?

Growth usually means more work. A system that copes well with a few users may struggle with many more if its database does heavy work on every request. Extra work in the database slows down the requests that only the database can handle, such as saving an order or checking a login.

Two problems stand out. The database is hard to grow, and it is shared by everyone.

### The Database Is Hard to Grow

The app tier is often the easiest part of a system to grow. You can usually run more copies of an app server and share traffic among them. The database is different, because it holds the data itself. Teams often respond by buying a bigger database machine, but a single machine has a limit. Splitting a database across several machines is also far from simple.

Because of this, work that lands in the database is expensive to scale. Work in the app tier can usually be spread across more machines at lower cost.

### One Heavy Task Slows Everyone

The database is usually shared by every page and every user. If one feature keeps the database CPU busy, other features wait in line. A slow discount calculation on a product page can delay the login check and the shopping cart, even though those features never touch discounts.

Cost matters too. Some managed database services charge according to the amount of work done. Extra processing in the database can therefore raise the bill as well as slow the system.

## How to Fix It

The core fix is easy to state. Let the database fetch and store data, and let the application tier do the rest. The database should do the jobs it is built for, such as totals over many rows. Formatting, business rules, and display logic should move to other code.

### Move the Processing Out

1. **Find the non-data work:** List each procedure or query that builds text, formats dates or money, does math on each row, or applies business rules.
2. **Return the raw facts:** Change the query so it returns plain values, such as prices, quantities, and dates, instead of finished text.
3. **Write the rules in the app:** Put the formatting and the rules in application code. Code there is easier to test, and the app tier can add more machines when load grows.
4. **Keep set work in the database:** Leave jobs such as totals across many rows in SQL, because databases are optimized for that kind of work.

### Keep Queries Lean

- **Fetch only what the screen needs:** Select the columns the page uses, not every field in the table. Fetching only what is needed cuts the data that moves through the database.
- **Make lookups quick:** Lookups should be able to find rows fast. The details of how to do this belong to the SQL Tuning lesson.
- **Avoid heavy logic on the server:** Stop running loops, string work, and long rule chains inside procedures and triggers.

### Cache or Precompute Results

When many users ask for the same result, compute it once and reuse it. A cache is a fast copy of data kept close to where it is used. Results that are worked out ahead of time, such as a daily total saved in a table, can also cut repeated work. The full rules for caching belong to their own lessons.

> **Tip:** A cached result can become out of date. Before you cache, decide how old a result may be and who would notice if it were stale.

### Confirm the Fix Works

1. **Measure first:** Record database CPU, response times, and error counts under normal load. You need this baseline to know whether the change helped.
2. **Load test the change:** Run a simulated crowd of users against the old code and the new code.
3. **Compare the numbers:** Check that the database is doing less work and that the app tier copes with the extra load without new errors.
4. **Keep watching:** Keep monitoring after the release, so a new hot spot is noticed early. Setting up the monitoring itself is a separate topic.

## Worked Example: The Product Page

Imagine an online store. Its product page calls one stored procedure. The procedure joins product, price, and discount tables. It builds the price text, works out the sale price, and sorts the results, all inside the database server. On normal days the page loads fine. On sale day, traffic jumps, database CPU reaches 100%, and pages start to time out.

### Walking Through the Fix

1. **Read the symptoms:** On sale day the database CPU hits its limit and pages time out. The web servers still have spare room, so the database is the bottleneck.
2. **Sort the work into two groups:** Joins and filters are data access, so they stay in the database. The price text, the sale math, and the sorting for display are processing, so they move out.
3. **Return raw values:** The query now returns the product name, the list price in cents, and the discount percent. It no longer builds any text.
4. **Calculate in the web application:** The web application works out the sale price and builds the label. The code below shows the idea with sample numbers.
5. **Cache the finished page data:** Many shoppers view the same product, so the app keeps the finished product data for a short time and reuses it.
6. **Re-test before the next sale:** Run a load test on the product page. Check that database CPU stays well below its limit and that timeouts are gone.

```
SELECT p.product_id, p.name, p.list_price_cents, d.percent_off
FROM products p
LEFT JOIN discounts d ON d.product_id = p.product_id
WHERE p.product_id = 42;
```

The query above is the lean version. It fetches the raw facts, and the application does the rest. The Python sample below runs the same kind of sale math in the application tier. It uses only whole cents, so the output is exact.

```python
rows = [('Lamp', 4000, 10), ('Desk', 12000, 25)]
for name, cents, percent_off in rows:
    sale = cents * (100 - percent_off) // 100
    print(f'{name}: ${sale // 100}.{sale % 100:02d} (was ${cents // 100}.{cents % 100:02d})')
```

Output:

```text
Lamp: $36.00 (was $40.00)
Desk: $90.00 (was $120.00)

```

## Balance the Change

Moving work is not always the right answer. Two traps are common, and a good designer watches for both.

### Keep Set Work in the Database

Some work is exactly what a database does well. Totals and counts over many rows are one example. Moving a large sum out to the app means first pulling every row across the network.

So the goal is not to remove all logic from the database. The goal is to stop sending display work and per-row rules into it, while keeping set-based work where it belongs.

### Avoid Many Small Calls and Oversized Data

Fetching far more data than the page needs is also wasteful. Aim for a few well-shaped requests that return just enough data for the screen. The lessons on chatty calls and extra fetching go deeper into these problems.

> **Tip:** For each piece of work, ask two questions. Does it touch many rows inside the database? Is it display formatting or a business rule? Work that touches many rows often belongs in the database. Display and rule work usually belongs in the app.

## Check Your Judgment

**Quick check:** A report needs the total sales for each region. The order table holds millions of rows. Which approach fits the Busy Database fix best?

A. Pull every order row into the app and add up the totals in code.
B. Keep the grouped sum in the database and send the app only the small totals.
C. Build the region names with string joins inside the SQL query.
D. Format each total as a text label inside the query before returning it.

**Answer:** B. Totals across many rows are set work, and databases are built for that. Sending every row to the app would move a lot of data across the network. Text labels are display work, so they are the kind that should leave the database.

## Key Takeaways

- **Know the pattern:** Busy Database means the database spends its time running work that the application tier could do.
- **Fix it with balance:** Move formatting and per-row rules to the app, and keep set-based totals in SQL.
- **Prove the result:** Measure before the change, load test after it, and keep watching the numbers in use.

## Sources

- [Busy Database antipattern, Azure Architecture Center (Microsoft Learn)](https://learn.microsoft.com/en-us/azure/architecture/antipatterns/busy-database/)
- [Extraneous Fetching antipattern, Azure Architecture Center (Microsoft Learn)](https://learn.microsoft.com/en-us/azure/architecture/antipatterns/extraneous-fetching/)
- [Performance testing and antipatterns for cloud applications, Azure Architecture Center (Microsoft Learn)](https://learn.microsoft.com/en-us/azure/architecture/antipatterns/)
- [Database vs Application: Performance and Ops, SQL Server Science](https://www.sqlserverscience.com/data-architecture/database-vs-application-performance-operations/)
- [The Case for Business Logic in Stored Procedures, SQL Server Science](https://www.sqlserverscience.com/data-architecture/business-logic-in-stored-procedures/)
- [Business Logic in database or application, Ajeet Yelandur (blog)](https://www.ajeetyelandur.com/2016/05/bl-in-database-or-application/)
- [Thinking of Scaling MySQL, Say goodbye to stored procedures, Softqube (blog)](https://softqubes.com/blog/thinking-of-scaling-mysql-say-bye-to-store-procedure/)
- [Automatic Partitioning of Database Applications, arXiv paper 1208.0271](https://arxiv.org/pdf/1208.0271)
