Compliance Assessments list endpoint extremely slow (~8s for 15 records)

[dorianborovina]

Describe the bug
The GET /api/compliance-assessments?offset=0&limit=20 endpoint takes approximately 8-8.5 seconds to respond with only 15 compliance assessments in the database.
This makes the Audits page in the UI painfully slow for day-to-day work.

A direct SQL query against the same data returns in ~3.5 ms:

SELECT ca.id, ca.name FROM core_complianceassessment ca;
-- Returns 15 rows in ~3.5 ms

This confirms the bottleneck is entirely in Django's ORM serialization / view logic, not the database.
The response payload is ~60 KB of JSON for 15 records, suggesting extensive nested serialization.
The issue is likely an N+1 query problem or missing select_related / prefetch_related on related objects (frameworks, requirement nodes, scores, etc.).

To Reproduce

• Go to Compliance > Audits
• Wait for the page to load
• Observe ~8-8.5 seconds load time in browser DevTools (Timing tab shows "Waiting for server response: 8.26s")

Expected behavior
The compliance assessments list endpoint should return 15 records in under 1-2 seconds.

Screenshots

Environment

Device: VM (AMD EPYC 7282, 8 vCPUs, 8GB RAM, SSD)
OS: Debian 12 (Docker deployment)
Browser: Chrome (latest)
Version: CISO Assistant Community Edition, 3.12.0
Database: PostgreSQL 16 (Alpine), tuned (shared_buffers=512MB, work_mem=16MB, effective_cache_size=4GB)
Gunicorn: 5 workers, 4 threads, timeout 600

Additional context

• The same slow behavior was observed on SQLite before migrating to PostgreSQL. The migration did not improve this endpoint's performance.
• Other pages in the application load at acceptable speeds.
• The dataset is small: 15 compliance assessments, ~28,800 total objects in the database.
• Database-level tuning (shared_buffers, work_mem) and gunicorn worker scaling had no impact on the per-request processing time, further confirming this is an application-layer issue.

[ab-smith]

Hello,
We weren't able to reproduce this. Can you please share what frameworks are used?
Also, sqlite is generally faster in our case, given that it doesn't go through network latency and has WAL mode enabled.
Regards

[dorianborovina]

Hey @ab-smith ,

We're using "BSI IT Grundschutz" frameworks.

I actually didn't test the SQL query before migrating to postgresql. I just assumed that's the culprit.

Thanks,
Dorian

[ab-smith]

ok it's a heavy framework indeed, we've added some extra optimization on #3471 that will be available on the next release.

[ab-smith]

you can check the impact of this optimisation after updating to the latest release

[dorianborovina]

All sorted out!! 👍🏻

Thanks so much!!