Database Performance Optimization: Simple Fixes for Slow Queries

by | Jan 30, 2025 | Database, DevOps

Why does a reliable system suddenly fail to perform? Discover how a series of overlooked details caused a performance crisis and what steps solved it.

A Reliable Software, a Growing Problem

Our software has proven itself over the years. Customers trusted it for efficiency and stability. But during a new installation, performance issues began to emerge. The problem surfaced during billing runs, where the system processes customer invoices based on data from a large table. Processing slowed dramatically, and deadlines for generating invoices were missed. As tensions grew, potential revenue losses loomed. The database administrator (DBA) suggested updating to the latest database release to improve performance. This seemed reasonable, so we implemented the update.

Performance Crumbles Despite Simplicity

Technically, the process looked straightforward. The system stored billing data in a single table, processed it during invoice runs, and cleared it after several weeks. The table didn’t involve complex JOINs or relationships with other tables. However, it had an unusual characteristic: 300 columns. This large column count wasn’t the root cause of the problem, but it did contribute to it. Surprisingly, the same software and database worked perfectly for other customers. The only recent change? The database update.

The Escalation Begins

As performance continued to decline, we investigated step by step. A closer look at the query execution plan revealed a shocking discovery: instead of using indexes, the database performed a full-table scan. This approach slowed processing drastically. Why wasn’t the database leveraging its indexes? Was it a hardware issue? Could a failing disk be the culprit? We considered every possibility. Meanwhile, another team exported the database to test it on a different server. The data export took all night, but processing on the new server ran smoothly. The mystery deepened—if the data wasn’t the problem, what was?

A Long Delete and an Unbelievable Result

We decided to empty the table to eliminate any residual data. A simple DELETE FROM table query should have sufficed, but it ran for an unexpectedly long time. When an operator accidentally interrupted the query, the rollback process caused further delays. Next, we ran a SELECT COUNT(*) to check the actual number of rows. This query strained the server, triggering heavy disk activity. After 20 minutes, the result finally appeared: 0 rows. Everyone stared in disbelief. How could a query take so long to count an empty table?

The Solution: Simplifying the Complex

The investigation revealed several overlooked factors contributing to the problem:
  1. Missed Maintenance: Over months, the system failed to delete old records. Large volumes of outdated data filled the table, but the issue only became noticeable when the disk filled up.
  2. Database Updates: The new database version introduced a Cost-Based Optimizer (CBO) to replace the older Rule-Based Optimizer (RBO). The CBO relies on table statistics to create efficient query plans.
  3. Outdated Statistics: The missing updates to table statistics misled the optimizer. It assumed the table was empty, resulting in inefficient full-table scans.
  4. Empty Data Segments: Despite having no records, the table retained many empty segments. The SELECT COUNT(*) query scanned every segment, creating unnecessary overhead.

How We Fixed It

Here’s how we resolved the issue and optimized database performance:
  • We used a TRUNCATE TABLE command instead of DELETE FROM table. This command instantly removed all table segments, including empty ones.
  • We refreshed the table statistics, ensuring the optimizer had accurate information for query planning.
  • We automated housekeeping scripts to prevent similar data accumulation in the future.

Conclusion: Lessons in Database Maintenance

This experience taught us that small oversights can escalate into significant problems. Regular housekeeping, timely updates to database statistics, and careful monitoring of new system changes are essential to maintaining performance.

If your system struggles with slow queries or performance issues, follow these steps to troubleshoot and optimize. Need help optimizing your database? Contact us today to ensure your system stays reliable and efficient.

Find more articles on this blog or in the internet.

0 Comments

Leave a Reply

Explore Articles That Align With Your Interests

Overprovisioned Host System – A Nightmare

Overprovisioned host systems in virtualized environments often cause performance issues. Steal Time is a reliable indicator for identifying such bottlenecks. This article explains how to monitor Steal Time using top, the impact of high values, and how monitoring tools...

Well documented: Architecture Decision Records

Heard about Architecture Decision Records? Anyone who moves to a new team quickly faces familiar questions. Why did colleagues solve the problem in this way? Did they not see the consequences? The other approach would have offered many advantages. Or did they see...

Why Event-Driven Architecture?

What is event-driven architecture? What are the advantages of event-driven architecture, and when should I use it? What advantages does it offer, and what price do I pay? In the following, we will look at what constitutes an event-driven architecture and how it...

On-Premise? IaaS vs. PaaS vs. SaaS?

What does it mean to run an application in the cloud? What types of clouds are there, and what responsibilities can they take away from me? Or conversely, what does it mean not to go to the cloud? To clarify these questions, we first need to identify the...