Practical API Service Performance Optimization

Optimization Strategy

We implemented the following approaches:

• Batch data retrieval, reducing query count: To address N+1 query problems, we replaced sequential database queries inside loops with a single batch query. Rather than querying by ID individually, we used the database's IN clause to request all required resources at once. We introduced database views combining previously scattered tables, enabling complete results from a single query. For example, we created a view aggregating resource basic information, category data, and other required fields. This lets us fetch all records for the given IDs in one query, eliminating repeated access to multiple tables.

• Adjusting result sorting: For sorting issues that depended on caching, we simplified the logic. First, we removed cache calls from the sorting stage and set sorting criteria that couldn't be retrieved in real time to default values. Second, since batch query results may not preserve the original ID order (database IN queries typically don't guarantee output order matching input ID order), we added a result reordering step in the application layer. After receiving batch query results, we reorder them according to the original ID list, ensuring consistency with pre-optimization business requirements. This approach eliminates cache dependency while maintaining correct result ordering.

• Data preloading and caching (optional): We also explored preloading strategies. For geographic range queries, we could preload all resource data for a specific region into memory or use existing search indexes to retrieve complete data directly, reducing real-time database queries. However, this approach is more complex to implement, requiring trade-offs between data freshness and memory usage. In this optimization effort, we retained it as an alternative approach without immediate implementation.

View Aggregation and Batch Querying

• Aggregate key fields from resource_basic, resource_meta, resource_extra and other tables into a read-only view vw_resource_full.
• Use SELECT * FROM vw_resource_full WHERE resource_id IN (...) to fetch all data at once, eliminating N+1 queries.

CREATE OR REPLACE VIEW vw_resource_full AS
SELECT b.resource_id,
       b.name,
       m.category,
       e.score,
       ...
FROM   resource_basic b
JOIN   resource_meta m   ON m.resource_id = b.resource_id
LEFT JOIN resource_extra e ON e.resource_id = b.resource_id
WHERE  b.status = 'ACTIVE';

Order Correction

• Map batch query results to dict[resource_id] -> data.
• Reassemble results according to the original id_list order to maintain business consistency.

id_list = compute_ids_by_range(params)        # 原始顺序
rows = db.select_many(
    """SELECT * FROM vw_resource_full WHERE resource_id IN %(ids)s""",
    {'ids': tuple(id_list)}
)
row_map = {row['resource_id']: row for row in rows}
result = [row_map[rid] for rid in id_list if rid in row_map]

Sorting Logic Simplification

• Remove cache dependency from the sorting stage and directly use the score field in the view.
• For more complex sorting needs, use SQL: ORDER BY score DESC, distance ASC.

The optimized implementation retrieves data through a single SQL query including all IDs, dramatically reducing database interactions. Query results are then ordered according to the original ID list, replacing the previous custom sorting process. When business requirements call for sorting by a specific field, this can be completed directly with an ORDER BY clause in the database query, avoiding application-layer loop sorting overhead.

Performance Verification

After optimization, we verified interface performance by comparing logs from test environments and monitoring data from production tools. Response times showed marked improvements:

• During local testing, multiple requests under identical conditions revealed the difference. Before optimization, each request took hundreds to thousands of milliseconds; after optimization, requests consistently completed in tens of milliseconds.
• In production monitoring, the 99th percentile response time—previously reaching multiple seconds—dropped to sub-second levels (well under 1000 ms) following the optimization release. Most requests improved from roughly 500 ms to under 100 ms, a several-fold performance gain. Under high concurrency, database load decreased and interface timeout alerts disappeared.

These metrics confirm the optimization achieved its intended effect: improved interface performance while maintaining functional correctness.

Key Lessons

• Avoid N+1 queries: Batch queries or joins significantly reduce database load.
• Perform sorting in the database: Let the database handle what it does best, reducing application-layer loops.
• Maintain order consistency: After batch queries, reorder results as needed; both ORDER BY FIELD and application-layer dictionary remapping work.
• Monitor and verify: Every optimization requires tracing and metrics to quantify the benefit.