Recently, I worked on an enterprise scoring and reporting application hosted on Azure SQL. What started as a simple performance issue eventually became a deep investigation into database usage, application design, caching, batching, and scalability.

Most developers have faced this situation at least once:

Users report that the application is slow.

The first reaction is: "Let's scale the infrastructure."

We thought the same.

But what if the real problem isn't CPU, memory, or database size?

This is the story of how our Scorecard application started hitting 100% DTU on Azure SQL, struggled under a relatively small production workload, and how we significantly improved performance without continuously increasing infrastructure costs.

For anyone unfamiliar with Azure SQL, DTU (Database Transaction Unit) is a blended measure of CPU, memory, and I/O. A database can hit 100% DTU even when CPU usage appears normal.

The Application

The Scorecard is a scoring and reporting platform used by healthcare auditors.

The application allows users to:

• Complete scorecards


• Review assessments


• Generate reports


• Manage templates

Due to budget constraints, the application was hosted on Azure SQL Basic (5 DTUs).

Initially everything worked perfectly.

✅ Development environment was fast.

✅ Testing looked good.

✅ No obvious performance concerns.

Then production happened.

It Worked Fine... Until It Didn't

Development → Production → Slow Performance

             ↓

         Cache Fixes

             ↓

         Scale to S0

             ↓

      Problem Returns

             ↓

      Deep Investigation

             ↓

       Root Cause Found

             ↓

      Multiple Optimizations

             ↓

     Stable Performance

As users started actively using the application, we began receiving complaints:

• Scorecards were loading slowly


• Reports were taking too long to generate


• Save operations felt sluggish

Our first response was straightforward:

• Adjust cache settings


• Tune cache TTL values


• Apply a few quick optimizations

Things improved.

For a while.

Then the same issue returned.

The DTU Surprise

Before making any changes, we needed to understand where the bottleneck actually was.

The most valuable tool during this investigation wasn't caching, indexing, or scaling.

It was monitoring.

If we had looked only at application logs, we might have continued scaling infrastructure without solving the real problem.

Azure monitoring helped us identify that the bottleneck was actually in the database layer.

We started monitoring Azure resources.

First stop:

Azure App Service

Everything looked healthy.

• CPU looked normal


• Memory usage was acceptable


• No obvious bottlenecks

Then we checked Azure SQL.

And found this:

DTU = 100%

At this point we assumed:

"The database tier is too small."

So we upgraded from:

Azure SQL Basic → Azure SQL S0

Problem solved?

Not really.

The application performed better for a few days.

Then the same issue returned.

Even with only 2–3 users.

That's when we realized something important:

Scaling infrastructure was treating the symptom, not the root cause.

Time to Investigate

Instead of increasing DTUs again, we decided to trace every database interaction.

What we discovered was surprising.

Opening a template

5 database calls

Loading a scorecard

40 database calls

Downloading a report

13 database calls

And that was only the visible part.

We also found:

• Queries running inside loops


• Repeated database reads


• Multiple unnecessary API calls


• Expensive search operations


• Large datasets loaded unnecessarily

The application wasn't slow because of the number of users.

It was slow because of the amount of work being done for each user action.

The Hidden Performance Killers

1. Loading Thousands of Records to Show 20

Our grids were loading large datasets even though users could only see a small portion of the records.

This meant:

• More SQL reads


• Larger payloads


• Higher memory usage

2. Saving Questions One by One

Imagine saving a scorecard with 50 questions.

The application was effectively doing:

Question 1 → Save

Question 2 → Save

Question 3 → Save

...

Question 50 → Save

That's a lot of unnecessary network traffic.

3. Repeated Data Fetching

Some lookup data and dropdown values rarely changed.

Yet they were being fetched repeatedly.

Every page refresh.

Every screen load.

Every user.

4. Search Operations

Search functionality was expensive.

Large datasets were being scanned repeatedly, causing significant pressure on the database.

The Optimization Journey

There was no single silver bullet.

Instead, we applied several focused optimizations.

1. Created Optimized Database Views

Many screens were assembling data through multiple queries and joins.

We introduced optimized database views to prepare the data closer to where it lived.

Benefits:

• Simpler queries


• Reduced processing


• Faster screen loads

2. Reduced Database Trips

Instead of making multiple calls for a single screen:

Template → 5 calls

Scorecard → 40 calls

Report → 13 calls

We consolidated requests wherever possible.

Result:

• Fewer round trips


• Lower database load


• Better response times

3. Implemented Smart Save Logic

Previously every question was updated.

Even if nothing had changed.

We modified the logic to:

✅ Save only changed questions

✅ Skip unchanged records

This immediately reduced unnecessary network traffic.

4. Introduced Batch Processing

Instead of:

50 questions = 50 requests

We switched to:

50 questions = 5 batch requests

Using mutation batching significantly improved save performance.

5. Added Caching

We identified reusable data and cached it.

Examples:

• Dropdown values


• Lookup tables


• Reference data

One important lesson:

Cache aggressively, but always invalidate cache correctly after updates and deletes.

Otherwise performance improves while data accuracy suffers.

6. Implemented Server-Side Pagination

This was one of the most impactful improvements.

Instead of loading thousands of records:

Load only what the user can see

Benefits:

• Smaller payloads


• Faster grids


• Reduced database load

7. Optimized Search

We improved search performance through:

• Better indexing


• Query optimization


• More efficient lookup strategies

Result:

• Faster search


• Lower DTU consumption

8. Controlled Parallel Processing

Initially many operations ran sequentially.

We introduced controlled parallel execution for independent operations.

However, we intentionally limited concurrency.

Why?

Because uncontrolled parallelism can overwhelm smaller database tiers.

Optimization isn't about doing everything in parallel.

It's about doing the right amount of work in parallel.

Results

After implementing these optimizations:

✅ Reduced database round trips by more than 80% on key screens

✅ Reduced unnecessary save operations

✅ Improved report generation performance

✅ Reduced Azure SQL DTU pressure

✅ Improved user experience without increasing infrastructure costs

Most importantly:

We stopped treating the symptoms and fixed the actual problem.

We improved the application instead of continuously increasing Azure spend.

Key Lessons Learned

1. Scaling Is Not Always the Answer

Scaling can provide temporary relief.

It rarely fixes inefficient application behavior.

2. Performance Problems Hide During Development

Development environments rarely have:

• Real data volumes


• Real concurrency


• Real user behavior

Production reveals the truth.

3. Loops + Database Calls = Trouble

One database call inside a loop may look harmless.

But:

1 query × 50 records × 3 users

Can quickly become hundreds of database operations.

4. Small Optimizations Compound

No single change solved our issue.

The combination of multiple improvements did.

5. Always Investigate Before Optimizing

One of the biggest takeaways from this project:

Measure first. Optimize second.

Never assume where the bottleneck is.

The Biggest Mistake We Made

Initially, we assumed:

Slow application = Small database

That assumption led us to scale infrastructure before fully understanding the problem.

The real issue wasn't database size.

The real issue was:

• Too many database calls


• Too much repeated work


• Too much unnecessary data movement

This is a powerful lesson because many teams make the same mistake.

Final Thoughts

"Scaling gave us more capacity. Optimization gave us a solution."

This project completely changed how we approach performance optimization.

Before asking for more infrastructure, we now ask:

• Can this be cached?


• Can this be batched?


• Can this be loaded later?


• Can this be fetched once?


• Can this run in parallel?

Performance optimization is rarely about one big fix.

It's usually about identifying dozens of small inefficiencies and removing them one by one.

And sometimes, that's enough to turn a struggling application into a scalable one without spending an extra dollar on infrastructure.

If there's one lesson I took from this project, it's this:

Before increasing CPU, memory, or DTUs, spend time understanding how your application behaves.

Sometimes the biggest performance gains don't come from bigger servers.

They come from fewer queries, smarter caching, better batching, and cleaner design.

Have you ever faced a similar production performance challenge? I'd love to hear your experience in the comments.