Transforming a sluggish patient list API into a lightning-fast, production-ready endpoint through strategic database optimization and response streamlining.
The Problem: A Performance Nightmare
Picture this: You're working on a healthcare application with a patient list API that's taking 27+ seconds to load just 600 patients. Users are frustrated, the database is overwhelmed, and your server costs are skyrocketing. This was the reality I faced when analyzing our patientList
API.
Initial Performance Metrics (The Bad News)
- Response Time: 27.43 seconds 😱
- Database Queries: 4,440+ queries
- Model Instantiations: 5,320+ objects
- Memory Usage: 20MB
- User Experience: Completely unusable
The API was suffering from the classic N+1 query problem on steroids, with additional issues like unnecessary data transfer and missing database indexes.
The Investigation: Root Cause Analysis
1. The N+1 Query Monster
// The problematic code (simplified)
foreach($patientData as $key => $pd) {
$pd->userInfo->user_uuid; // Query 1 per patient
$pd->officeInfo->office_name; // Query 2 per patient
$this->findPatientDefaultData(...); // Query 3 per patient
$this->findPatientInsurance(...); // Query 4 per patient
// ... and so on
}
For 600 patients, this meant 4,440+ individual database queries just in the foreach loop!
2. Unnecessary Data Transfer
The API was returning 27+ fields per patient, including:
- Personal details (SSN, addresses, BMI, weight)
- Insurance information
- Questionnaire data
- Profile assets
- Referral information
Most of this data wasn't being used by the frontend.
3. Missing Database Indexes
Critical columns lacked proper indexing, causing full table scans on every query.
The Solution: A Multi-Layered Optimization Strategy
Phase 1: Eliminate N+1 Queries with Eager Loading
// Before: N+1 queries
foreach($patientData as $key => $pd) {
$pd->userInfo->user_uuid; // Individual query per patient
}
// After: Batch loading with eager loading
$query = PatientUnderOffice::with([
'userInfo:id,user_uuid,email,type',
'officeInfo:id,office_name'
])->where('dentist_office_id', $dentist_office_id);
// Only load clinical visits in batch (for last_clinical_visit field)
$clinicalVisits = VisitFormMiscellaneousNote::select('patient_profile_id', 'date')
->whereIn('patient_profile_id', $patientProfileIds)
->whereIn('id', function($query) use ($patientProfileIds) {
$query->select(DB::raw('MAX(id)'))
->from('visit_form_miscellaneous_notes')
->whereIn('patient_profile_id', $patientProfileIds)
->groupBy('patient_profile_id');
})->get()->keyBy('patient_profile_id');
Phase 2: Streamline Response Structure
I identified that the frontend only needed 15 essential fields out of 42 total fields. Here's what I removed:
// Removed 27 unnecessary fields:
$removedFields = [
'referred_by', 'assets', 'referral_type', 'referred_time', 'referred_note',
'dentist_office', 'gender', 'marital_status', 'height', 'ssn',
'address_one', 'address_two', 'city', 'state', 'zipCode', 'email',
'bmi', 'weight', 'self_pay_status', 'created_at', 'updated_at',
'employer_details', 'questionnaire_id', 'symptoms', 'epworth',
'treatment', 'history', 'insurance', 'temp_updated_value'
];
// Kept only essential fields:
$essentialFields = [
'id', 'user_id', 'user_uuid', 'patient_profile_id', 'dentist_office_id',
'title', 'first_name', 'middle_name', 'last_name', 'preferred_name',
'type', 'dob', 'phone_numbers', 'status', 'last_clinical_visit'
];
Phase 3: Add Strategic Database Indexes
// Migration: Add performance indexes
Schema::table('patient_under_offices', function (Blueprint $table) {
$table->index('dentist_office_id');
$table->index('status');
$table->index('created_at');
$table->index(['first_name', 'last_name']);
$table->index(DB::raw('phone_numbers(50)'));
$table->index('patient_profile_id');
});
Schema::table('users', function (Blueprint $table) {
$table->index('email');
$table->index('user_uuid');
});
// ... and more strategic indexes
Phase 4: Optimize Model Relationships
// Optimized relationships with selective field loading
public function userInfo()
{
return $this->belongsTo('App\Models\User', 'user_id', 'id')
->select('id', 'user_uuid', 'email', 'type');
}
public function officeInfo()
{
return $this->belongsTo('App\Models\DentistOfficeProfile', 'dentist_office_id', 'id')
->select('id', 'office_name');
}
The Results: From Nightmare to Dream
Performance Transformation
Metric | Before | After | Improvement |
---|---|---|---|
Response Time | 27.43s | 95.72ms | 99.7% faster |
Database Queries | 4,440+ | 10 | 99.8% reduction |
Model Instantiations | 5,320+ | 1,796 | 66% reduction |
Memory Usage | 20MB | 8MB | 60% reduction |
Response Size | Large | ~2KB | 70% smaller |
Real-World Impact
- User Experience: From completely unusable to instant loading
- Server Costs: Massive reduction in database load
- Scalability: Can now handle 10x more concurrent users
- Maintenance: Cleaner, more focused codebase
Key Lessons Learned
1. Always Profile Before Optimizing
Use Laravel Debugbar or similar tools to identify the real bottlenecks. Don't guess!
2. N+1 Queries Are Performance Killers
Eager loading and batch queries should be your first optimization targets.
3. Less Data = Better Performance
Only return what the frontend actually needs. Every unnecessary field costs bandwidth and processing time.
4. Database Indexes Are Critical
Proper indexing can make the difference between a query taking 1ms vs 1000ms.
5. Measure Everything
Before and after metrics are crucial for proving the value of your optimizations.
Tools and Techniques Used
- Laravel Debugbar: For performance profiling
-
Eager Loading:
with()
method for relationship optimization -
Batch Queries:
whereIn()
for bulk data loading -
Selective Field Loading:
select()
to limit data transfer - Database Indexes: Strategic indexing for query optimization
- Response Streamlining: Removing unnecessary data fields
Conclusion
This optimization transformed a completely unusable API into a lightning-fast, production-ready endpoint. The key was taking a systematic approach:
- Profile to identify bottlenecks
- Eliminate N+1 queries with eager loading
- Streamline the response structure
- Index the database properly
- Measure the results
The result? An API that's 287x faster and uses 99.8% fewer database queries. Sometimes the biggest performance wins come from the simplest optimizations.
Top comments (0)