Database Performance: From Slow to Instant

When PatronView started gaining traction, we hit a wall: our homepage was taking 3+ seconds to load. The culprit? Expensive COUNT(*) queries across our growing database of museum donors.

The Problem

With over 50,000 donor records and growing, our real-time counting approach was killing performance:

-- This was taking 2-3 seconds on every page load
SELECT COUNT(*) FROM patrons;
SELECT COUNT(*) FROM institutions; 
SELECT COUNT(*) FROM contributions;

The Solution: Static Generation

Instead of counting on every request, we built a smart caching system that pre-generates homepage statistics:

1. Background Generation Script

A Node.js script that runs the expensive queries once and saves results to JSON:

// Generate static data for instant loading
const stats = {
  totalPatrons: await db.prepare("SELECT COUNT(*) as count FROM patrons").first(),
  totalInstitutions: await db.prepare("SELECT COUNT(*) as count FROM institutions").first(),
  totalContributions: await db.prepare("SELECT COUNT(*) as count FROM contributions").first()
};

2. Build-Time Integration

The homepage now loads pre-computed data instead of hitting the database:

// Fast static data loading - no database queries
const homepageData = JSON.parse(await fs.readFile('public/data/homepage-leaderboards.json'));

Results

The transformation was dramatic: - Homepage load time: 3+ seconds → 95ms - Database pressure: Eliminated heavy COUNT queries - User experience: Instant page loads - Scalability: Ready for 10x growth

Production Deployment

We integrated this into our build pipeline so statistics update automatically:

npm run homepage:generate && npm run build

This approach lets us maintain real-time accuracy while delivering lightning-fast performance.

Sometimes the best optimization is avoiding the work entirely.