Good evening devs.
I’m Krishiv K. I’ve very limited experience in backend development and Django as well (I’m an undergrad student with ~1 yoe as a Django developer)
Since a lot of places are moving in the direction of fast shipping, I’ve observed that optimizations are often neglected. I wanted to create a tool that can help you show which QuerySet fetches what columns/fields, which fields are used and also build a suggestion string to help you rewrite your QuerySet.
GitHub:- https://github.com/krishiv1545/django-orm-cost
The tool is essentially a decorator and/or a middleware. When an API is executed, the following (example) will displayed in the terminal where the server is running.
═══ Analysis: get_review_trail ═══
Time: 74.05ms | Total Queries: 6
1. QuerySet Analysis
Location: D:\Projects\finnovate_project\fintech_project\core_APP\modules\gl_reviews\gl_reviews.py:1015
SQL 1: SELECT "review_trails"."id", "review_trails"."reviewer_id", "review_trails"."reviewer_responsibility_matrix_id", "review_trails"."gl_review_id", "review_trails"."previous_trail_id", "review_trails"."gl_code", "review_trails"."gl_name", "review_trails"."reconciliation_notes", "review_trails"."action", "review_trails"."created_at" FROM "review_trails" WHERE "review_trails"."gl_code" = '11290300' ORDER BY "review_trails"."created_at" ASC
SQL 2: [3x] SELECT "Users"."id", "Users"."password", "Users"."last_login", "Users"."is_superuser", "Users"."username", "Users"."first_name", "Users"."last_name", "Users"."email", "Users"."is_staff", "Users"."is_active", "Users"."date_joined", "Users"."user_type" FROM "Users" WHERE "Users"."id" = 27 LIMIT 21
Fields Fetched = ['gl_code', 'gl_name', 'reconciliation_notes', 'action', 'created_at', '[3x] password', '[3x] last_login', '[3x] is_superuser', '[3x] username', '[3x] first_name', '[3x] last_name', '[3x] email', '[3x] is_staff', '[3x] is_active', '[3x] date_joined', '[3x] user_type']
Fields Consumed = ['action', 'created_at']
Efficiency = 2/16 | 87.50% over-fetched
>>> N+1 Query Detected
The same database query is being executed multiple times inside a loop.
This usually happens when querying the database for each item individually.
>>> Suggestion: ReviewTrail.objects.only('action', 'created_at')
2. QuerySet Analysis
Location: D:\Projects\finnovate_project\fintech_project\core_APP\modules\gl_reviews\gl_reviews.py:1027
SQL 1: SELECT "review_trails"."id", "review_trails"."reviewer_id", "Users"."id", "Users"."first_name" FROM "review_trails" LEFT OUTER JOIN "Users" ON ("review_trails"."reviewer_id" = "Users"."id") WHERE "review_trails"."gl_code" = '11290300' ORDER BY "review_trails"."created_at" ASC
Fields Fetched = ['first_name']
Fields Consumed = ['reviewer__first_name']
Efficiency = 1/1 | 100.00% over-fetched
>>> Suggestion: ReviewTrail.objects.select_related('reviewer').only('reviewer__first_name')
When something like student.teacher.first_name is called without using ‘select_related()’, the N+1 queries executed are counted under the students queryset for proper grouping.
I’m monkey-patching getattribute and iter which to my current understanding are the 2 ways of fetching data, to track used fields. When SQL ASTs are executed and Django receives the rows, they’re converted to instances, which I’m tracking using the post_init signal to match it to the QuerySet that gave birth to it.
I’m writing this post to ask for feedback about the thought of this project. It works but it is fragile and over-engineered and may not be operable for complex APIs (maybe?).
Can you please provide suggestions, improvements, or feedback? How do I learn inner workings of Django to build such projects? Is this something anyone would actually find useful? Are there existing solutions that I’m missing?