[Bug]: Severe Performance Degradation Due to N+1 Queries and Non-Batch Operations in Gateway/Tool/Server Services

[kevalmahajan]

🐞 Bug Summary

Several service modules (gateway_service.py, tool_service.py, and server_service.py) were suffering from major performance bottlenecks caused by:

1. N+1 query patterns when fetching team names or related entities
2. Per-item database operations instead of bulk/batch operations
3. Multiple repeated metrics queries instead of aggregated SQL functions

These issues caused severe scalability problems, high DB load, and increased latency across multiple endpoints. The optimizations implemented in the recent PR were necessary to address these underlying problems.

---

🧩 Affected Component

Select the area of the project impacted:

• mcpgateway - API
• mcpgateway - UI (admin panel)
• mcpgateway.wrapper - stdio wrapper
• Federation or Transports
• CLI, Makefiles, or shell scripts
• Container setup (Docker/Podman/Compose)
• Other (explain below)

---

🔁 Steps to Reproduce

1. Populate the system with a large number of gateways, tools, or servers (e.g., 100–500 items).
2. Call any of the following endpoints:

• list_gateways()
• list_gateways_for_user()
• list_tools()
• list_tools_for_user()
• list_servers()
• list_servers_for_user()
• aggregate_metrics() on tools or servers

3. Observe application logs and database query traces.

---

🤔 Expected Behavior

Listing operations should execute constant-time (O(1)) database queries, not one query per item.
Tools/resources/prompts should be fetched in bulk, not with N individual lookups.
Metric aggregation should be handled by a single SQL query, not 7–8 separate queries.
CPU-intensive operations should not run repeatedly when unnecessary.

---

🧠 Environment Info

You can retrieve most of this from the /version endpoint.

Key	Value
Version or commit	e.g. v0.9.0 or main@a1b2c3d
Runtime	e.g. Python 3.11, Gunicorn
Platform / OS	e.g. Ubuntu 22.04, macOS
Container	e.g. Docker, Podman, none

---