[PERFORMANCE]: Fix N+1 queries in single-entity retrieval functions (get_server, get_gateway, etc.)

[crivetimihai]

Summary

Single-entity retrieval functions use db.get() without eager loading, then access relationships (.tools, .resources, .prompts), triggering N+1 lazy load queries. This was observed during load testing with 218K+ lazy load queries per relationship.

Problem

Functions like get_server() fetch an entity with db.get(), then iterate over relationships:

# server_service.py:965 - 1 query, no eager loading
server = db.get(DbServer, server_id)

# Lines 976-978 - Each triggers a separate lazy load query
"associated_tools": [tool.name for tool in server.tools],      # +1 query
"associated_resources": [res.id for res in server.resources],  # +1 query  
"associated_prompts": [prompt.id for prompt in server.prompts], # +1 query

Query count per call: 4 (1 server + 3 lazy loads)

Additionally, export_service.py has an N+1 in a loop:

# Lines 879-884 - Batch query without eager loading
db_servers = db.execute(select(DbServer).where(DbServer.id.in_(server_ids))).scalars().all()
for db_server in db_servers:
    tool_ids = [str(tool.id) for tool in db_server.tools]  # N lazy loads!

Evidence

pg_stat_statements during 4000-user load test:

SELECT ... FROM tools ... servers_1 ... WHERE servers_1.id IN ...: 218,456 calls
SELECT ... FROM prompts ... servers_1 ...: 218,234 calls
SELECT ... FROM resources ... servers_1 ...: 217,891 calls

Affected Functions

Service	Function	Line	Impact
server_service	get_server()	965	High - accessed frequently
server_service	toggle_server_status()	1362	Medium
server_service	update_server()	1076	Medium
export_service	_export_selected_servers()	879	High - N queries in loop

Root Cause: convert_server_to_read() Helper

The convert_server_to_read() helper at lines 314-317 accesses 4 relationships:

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

Any code calling this helper on a non-eagerly-loaded server triggers N+1.

Correct Pattern (Already in Codebase)

list_servers() at lines 746-750 uses proper eager loading:

query = (
    select(DbServer)
    .options(
        selectinload(DbServer.tools),
        selectinload(DbServer.resources),
        selectinload(DbServer.prompts),
        selectinload(DbServer.a2a_agents),
    )
)

Proposed Fix

Replace db.get() with eager-loading query:

# BEFORE
server = db.get(DbServer, server_id)

# AFTER
server = db.execute(
    select(DbServer)
    .options(
        selectinload(DbServer.tools),
        selectinload(DbServer.resources),
        selectinload(DbServer.prompts),
        selectinload(DbServer.a2a_agents),
    )
    .where(DbServer.id == server_id)
).scalar_one_or_none()

For export_service, add eager loading to the batch query:

db_servers = db.execute(
    select(DbServer)
    .options(selectinload(DbServer.tools))
    .where(DbServer.id.in_(server_ids))
).scalars().all()

Files to Modify

• mcpgateway/services/server_service.py
• mcpgateway/services/export_service.py

Related Issues

• [PERFORMANCE]: Fix N+1 Query in list_tools - Missing joinedload for gateway #1879, [PERFORMANCE]: Fix N+1 Query in list_prompts - Missing joinedload for gateway #1880, [PERFORMANCE]: Fix remaining N+1 queries in list_servers, list_agents, and gateway sync #1883 (closed) - Fixed N+1 in list operations
• [PERFORMANCE]: Admin UI endpoint /admin/ has high latency under load #1907 - Admin UI latency (related symptom)

Acceptance Criteria

• get_server() uses eager loading for tools/resources/prompts/a2a_agents
• toggle_server_status() uses eager loading
• update_server() uses eager loading where relationships are accessed
• _export_selected_servers() uses eager loading in batch query
• No increase in query count when accessing relationships
• Existing tests pass

[jonpspri]

All of these cases should be defined as a relationship in db.py (they may already be) and in some cases it may be appropriate to perform a join load consistently if we always see the underlying access to the database.

We can organize the database table structure more hierarchically to optimize IOs when we know such loads are coming.

I'll take a closer look -- consistency and definition in the metadata rather than the functional code is going to get us a long way.

[jonpspri]

Further note: db.get accepts an options argument where loading options can be specified, allowing continued use of the ORM capabilities of SQLAlchemy.