Summary

Multiple list methods and sync operations still have N+1 query patterns that need eager loading with selectinload for many-to-many relationships.

Related: #1879, #1880 (fixed list_tools/list_prompts gateway N+1)

1. Server Service - list_servers()

File: mcpgateway/services/server_service.py
Location: Line 735

Current (N+1):

query = select(DbServer).order_by(desc(DbServer.created_at), desc(DbServer.id))

Problem in convert_server_to_read() at lines 306-309:

server_dict["associated_tools"] = [tool.name for tool in server.tools] if server.tools else []
server_dict["associated_resources"] = [res.id for res in server.resources] if server.resources else []
server_dict["associated_prompts"] = [prompt.id for prompt in server.prompts] if server.prompts else []
server_dict["associated_a2a_agents"] = [agent.id for agent in server.a2a_agents] if server.a2a_agents else []

Each relationship access triggers a lazy load query per server.

Fix:

from sqlalchemy.orm import selectinload

query = (
    select(DbServer)
    .options(
        selectinload(DbServer.tools),
        selectinload(DbServer.resources),
        selectinload(DbServer.prompts),
        selectinload(DbServer.a2a_agents),
    )
    .order_by(desc(DbServer.created_at), desc(DbServer.id))
)

Note: Use selectinload (not joinedload) for many-to-many relationships to avoid cartesian product.

2. Server Service - list_servers_for_user()

Location: Line 858
Same issue as above.

3. A2A Service - list_agents()

File: mcpgateway/services/a2a_service.py
Location: Line 457

Current (N+1 when include_metrics=True):

query = select(DbA2AAgent).order_by(desc(DbA2AAgent.created_at), desc(DbA2AAgent.id))

Problem in convert_agent_to_read() at lines 1326-1337:

successful_executions = sum(1 for m in db_agent.metrics if m.is_success)
response_times = [m.response_time for m in db_agent.metrics if m.response_time is not None]
last_execution_time = max((m.timestamp for m in db_agent.metrics), default=None)

Fix (conditional):

if include_metrics:
    query = query.options(selectinload(DbA2AAgent.metrics))

4. A2A Service - list_agents_for_user()

Location: Line 587
Same issue as above.

5. Gateway Service - Sync Operations

File: mcpgateway/services/gateway_service.py
Locations: Lines 1126, 1136, 1147, 1742-1780, 2134-2172

Pattern:

stale_tool_ids = [tool.id for tool in gateway.tools if tool.original_name not in new_tool_names]
gateway.tools = [tool for tool in gateway.tools if tool.original_name in new_tool_names]

Fix: When fetching gateway for sync operations, add eager loading:

gateway = db.execute(
    select(DbGateway)
    .options(
        selectinload(DbGateway.tools),
        selectinload(DbGateway.resources),
        selectinload(DbGateway.prompts),
    )
    .where(DbGateway.id == gateway_id)
).scalar_one()

Impact

• list_servers: With 10 servers having 5 items each = 41 queries → 2 queries
• list_agents: With 10 agents = 11 queries → 2 queries (when include_metrics=True)
• gateway sync: Reduces queries during federation sync

Acceptance Criteria

• Add selectinload for tools/resources/prompts/a2a_agents in list_servers()
• Add selectinload for tools/resources/prompts/a2a_agents in list_servers_for_user()
• Add conditional selectinload for metrics in list_agents() when include_metrics=True
• Add conditional selectinload for metrics in list_agents_for_user() when include_metrics=True
• Add selectinload when fetching gateway for sync operations
• Verify with make dev-query-log and make query-log-analyze
• Passes make verify

References

• SQLAlchemy selectinload vs joinedload
• todo/n-plus-one.md - Full N+1 analysis document