SQL Views for API Query Performance: A Real-World Optimization

Serving high-volume location queries exposed several performance bottlenecks:

• Single-request response times exceeded 1 second, degrading user experience
• Concurrent access handling was insufficient, leading to frequent timeouts
• Query logic contained repeated database round-trips (classic N+1 problem)
• Returned data was redundant, increasing network load and processing overhead

Optimization Strategy

We addressed these constraints with four complementary techniques:

1. SQL Optimization: Replace iterative queries with batch queries to reduce database interactions
2. Pre-built Views: Consolidate multi-table joins into a single view, lowering query complexity
3. In-application Ordering: Restore results to the caller's expected order when needed
4. Data Field Reduction: Return only essential fields to compress transfer size

Detailed Optimization Process

1. From Iterative to Batch Queries

The original logic queried information for each location ID separately (Python example):

for entity_id in entity_id_list:
    query_entity(entity_id)
    query_supplier(entity_id)
    query_delivery_info(entity_id)

This works for small datasets, but with thousands of IDs per request, a single API call triggered thousands of SQL round-trips, severely degrading response time.

Improved approach:

Use SQL IN clause to fetch all required data in one query:

entity_infos = query_stores_batch(entity_id_list)

A single SQL statement now retrieves all information, dramatically reducing database load and network overhead.

2. Consolidating Logic with Views

To further simplify SQL and reduce backend assembly, we built an aggregated view that pre-joins fields scattered across multiple tables:

CREATE VIEW entity_view AS
SELECT 
  s.entity_id, s.name, s.location, 
  n.partner_id, n.partner_name, 
  f.ship_time, f.ship_limit_price
FROM 
  entity_table s
INNER JOIN 
  partner_table n ON s.entity_id = n.entity_id
LEFT JOIN 
  shipping_table f ON n.partner_id = f.partner_id
WHERE 
  n.state = 'active';

Views provide immediate benefits:

• Unified data structure frees interface code from managing multi-table join logic
• Database optimizer handles JOIN execution, improving query performance
• Schema extensions are simpler and require less development and testing effort

3. Restoring Caller-Supplied Order

Batch queries improved throughput, but results rarely preserve input order. Some business logic depends on ordering—by distance, priority, or other criteria.

We added an ordering recovery step in the interface layer (Python example):

# 假设 result_map 是 {entity_id: entity_info} 的字典
ordered_list = []
for entity_id in entity_id_list:
    entity_info = result_map.get(entity_id)
    if entity_info:
        ordered_list.append(entity_info)

Dictionary lookup provides O(1) lookups with minimal overhead. This ensures the returned list matches the caller's original sequence while keeping recovery cost negligible.

4. Slimming the Response Payload

Examining original response fields revealed structural issues:

• Redundant fields: longitude/latitude duplicated as both longitude/latitude and coordinateX/coordinateY
• Unused backend fields carried through to clients
• Large, rarely-accessed field contents

We pruned the response to the essential minimum:

• Core IDs, names, location, delivery metadata
• Removed duplicates, retaining only standard names such as longitude/latitude
• Eliminated internal backend fields to prevent accidental exposure

Field reduction delivered direct gains:

• Response size shrank significantly
• Serialization/deserialization cost decreased
• Network transmission latency improved

Performance Results

Combined optimizations produced measurable improvement:

Load-testing and APM monitoring (Pinpoint) confirmed that end-to-end latency curves shifted downward across the board, with anomalous spikes significantly reduced.

Observations

This optimization demonstrates four inseparable components working together:

1. Database Access Patterns—Batch queries vastly outperform iterative ones; this is the primary lever for reducing database pressure under concurrency.
2. Database Views—Pre-joining complex multi-table logic simplifies interface code and makes future maintenance straightforward.
3. Application-Layer Ordering—Batch results lack guaranteed order and require explicit handling in the application to preserve caller expectations.
4. Field Reduction—Eliminating redundant and unused fields directly lowers transmission cost, with outsized gains in high-concurrency scenarios.

This optimization pattern originated with location queries but applies equally to any API facing high concurrency and large data volumes.