From 1 Minute 56 Seconds to 3 Seconds: Optimizing Database Inserts with Batch Processing

Sometimes, a feature works perfectly but takes so long that it becomes practically unusable.

Recently, while working on a camera management feature at my office, I encountered exactly this problem.

The Problem

Whenever we assigned cameras to a user, the operation took approximately 1 minute and 56 seconds to complete.

But there was another problem: our server had a request timeout of just 30 seconds.

This meant the API would time out long before the operation could finish.

Clearly, something needed optimization.

Finding the Bottleneck

I started debugging the camera assignment workflow to understand where the time was being consumed.

After tracing the execution, I found the issue.

There was a "for" loop that processed every user-camera mapping individually.

For each mapping, the application was:

1. Opening a database connection.
2. Executing an INSERT query.
3. Closing the database connection.

This process repeated for every camera-user mapping.

When assigning cameras in bulk, this resulted in hundreds or potentially thousands of individual database operations and unnecessary connection overhead.

The Solution: Batch Inserts

Instead of inserting every mapping individually, I changed the implementation to use batch processing.

The approach was simple:

• Created batches of 500 records.
• Collected 500 user-camera mappings in each batch.
• Inserted the entire batch using a single bulk INSERT query.
• Repeated the process until all mappings were inserted.

This significantly reduced the number of database operations and connection overhead.

Instead of executing an individual INSERT for every mapping, the application could now insert hundreds of records in one query.

The Result

The performance improvement was significant!

Metric| Before| After
Execution time| 1 min 56 sec| 3 sec
Database operations| Individual inserts| Batch inserts
Server timeout| 30 sec| Completed successfully

From 116 seconds to just 3 seconds!

That's approximately a 97.4% reduction in execution time.

What I Learned

This experience reinforced an important lesson:

Sometimes, the biggest performance improvements don't require a completely new architecture or a complicated technology.

Understanding how your application interacts with the database can make a huge difference.

A simple change from individual inserts to batch processing can eliminate unnecessary overhead and dramatically improve performance.

Always investigate the bottleneck before trying to optimize the entire system.

Have you encountered a similar database performance issue? I'd love to hear how you solved it!