Akaunting version

3.1.18

PHP version

8.3

Operating system

Ubuntu 22

Steps to reproduce

Usage under normal company load

Expected result

sub 1s loading

Actual result

High load times & High resources usage

Additional comments

PR link : #3294

This is part 1 , Here are the problematic areas i found

1.Dashboard users relationship (Critical )

Dashboard index page is loading each dashboard's users relationship individually, this is a known N+1 query

Solution performance in the PR: : 95% improvement : From 101 queries (eg 100 dashboards) to 2 queries

• Page load time: 3-5 seconds ---> around 200-500ms
• Database load reduction: 98%

2.Item Taxes in Autocomplete (Critical )

Item autocomplete API was accessing tax relationships individually for each item during price calculations.

Solution performance in the PR:

• 95% improvement: From 201 queries (50 items × 3 taxes) to 2 queries
• Response time: 2-8 seconds --> around 100-300ms

Document items display (Critical)

Document templates (invoices, bills) were loading items, taxes, and related data without eager loading

Solution performance in the PR:

• 90% improvement**: From ~80 queries to 3-4 queries for complex documents
• PDF generation: 10-15 seconds ---> around 1-2 seconds
• Template rendering: 5-8 seconds ---> around 500ms-1 second

PS: this Affects all document viewing, printing, and PDF generation operations

My PR also includes a tests file (N1QueryOptimizationTest.php) to validate the refactoring impact which are :

✅ Dashboard index optimization: Users relationship is eager loaded
✅ Item autocomplete optimization: Tax relationships are eager loaded
✅ DocumentService optimization: All critical relationships are loaded
✅ N+1 prevention: Eager loading works correctly
✅ Controller optimizations: Real endpoints work without performance issues

Relevant log output

https://github.com/akaunting/akaunting/pull/3294