Bài 3.1: Advanced QuerySets và Database Optimization - Phần 1

**Series Navigation:** - **Bài 3.1 (bài này): QuerySet Fundamentals & Lazy Loading** 👈 - [Bài 3.2: select_related() vs prefetch_related()](/django-advance/advanced-querysets-optimization-phan-2) - [Bài 3.3: N+1 Problem và Solutions](/django-advance/advanced-querysets-optimization-phan-3) - [Bài 3.4: Aggregation, Annotation & F/Q Objects](/django-advance/advanced-querysets-optimization-phan-4)

Mục Tiêu Bài 3.1

Sau khi hoàn thành Bài 3.1 này, bạn sẽ:

✅ Hiểu Django QuerySet evaluation lifecycle
✅ Nắm vững lazy loading mechanism
✅ Biết khi nào queries được executed
✅ Sử dụng QuerySet caching hiệu quả
✅ Tránh duplicate queries
✅ Monitor queries với Django Debug Toolbar

Giới Thiệu

Database optimization là cornerstone skill của Master Django Developer. Một application có thể:

✅ Chạy tốt với 100 users
❌ Sập hoàn toàn với 10,000 users

Nguyên nhân chính: Inefficient database queries.

Why Database Optimization Matters

Performance Impact:

Bad Query:  5000ms response timeGood Query: 50ms response time→ 100x faster!

Real-World Scenario:

Bad: 100 queries per page load → 2s load time
Good: 3 queries per page load → 50ms load time

Cost Impact:

Over-querying → Higher database load
Higher load → Expensive database upgrades
Simple optimization → Save thousands of dollars

Django QuerySet Fundamentals

What is a QuerySet?

QuerySet = Object representing a database query (but not executed yet).

Key Characteristics:

Lazy: Not executed until needed
Chainable: Can add multiple filters
Cached: Results cached after first evaluation
Iterable: Can loop over results

Example:

# This does NOT hit the database yet!articles = Article.objects.filter(status='published')print(type(articles))  # <class 'django.db.models.query.QuerySet'> # Still no database queryarticles = articles.filter(author__username='john')articles = articles.order_by('-created_at') # NOW it hits the database (when we iterate)for article in articles:    print(article.title)

QuerySet Lifecycle

Phase 1: Construction (No Database Hit)

queryset = Article.objects.all()  # No queryqueryset = queryset.filter(status='published')  # Still no queryqueryset = queryset.order_by('-created_at')  # Still no query

Phase 2: Evaluation (Database Query Executed)

# Triggers query execution:list(queryset)  # Convert to listlen(queryset)   # Count itemsbool(queryset)  # Check if existsqueryset[0]     # Access by indexfor item in queryset:  # Iteration    pass

Phase 3: Caching (Results Stored)

queryset = Article.objects.all() # First access: Database queryprint(len(queryset))  # Query: SELECT COUNT(*) FROM articles # Second access: Uses cacheprint(len(queryset))  # No query! Uses cached result

Lazy Evaluation

What is Lazy Evaluation?

Lazy Evaluation = QuerySets don't hit database until you actually need the data.

Benefits:

✅ Build complex queries incrementally
✅ Add filters conditionally
✅ Chain multiple operations
✅ No wasted queries

Building Queries Lazily

Pattern 1: Conditional Filtering

def get_articles(status=None, author=None, category=None):    """Build query based on provided filters"""    queryset = Article.objects.all()  # No query yet        if status:        queryset = queryset.filter(status=status)  # No query yet        if author:        queryset = queryset.filter(author=author)  # No query yet        if category:        queryset = queryset.filter(category=category)  # No query yet        return queryset  # Still no query! # Usagearticles = get_articles(status='published', author=user)# Query executes here when we access resultsfor article in articles:    print(article.title)

Pattern 2: Reusable Base QuerySets

class ArticleManager(models.Manager):    def published(self):        return self.filter(status='published')  # Lazy        def by_author(self, author):        return self.filter(author=author)  # Lazy        def recent(self, days=7):        from datetime import timedelta        from django.utils import timezone        cutoff = timezone.now() - timedelta(days=days)        return self.filter(created_at__gte=cutoff)  # Lazy # Chain lazy operationsarticles = (Article.objects    .published()      # No query    .by_author(user)  # Still no query    .recent(days=30)  # Still no query    .order_by('-created_at')[:10]  # Still no query!) # Query executes herefor article in articles:    print(article.title)

When QuerySets Are Evaluated

1. Iteration:

queryset = Article.objects.all()for article in queryset:  # 🔥 Query executed    print(article.title)

2. Slicing with Step:

queryset = Article.objects.all()articles = queryset[::2]  # 🔥 Query executed (with step) # But simple slicing doesn't executearticles = queryset[:10]  # No query yet (LIMIT added to SQL)

3. Pickling/Serialization:

import picklequeryset = Article.objects.all()data = pickle.dumps(queryset)  # 🔥 Query executed

4. repr() or str():

queryset = Article.objects.all()print(queryset)  # 🔥 Query executedprint(repr(queryset))  # 🔥 Query executed

5. len():

queryset = Article.objects.all()count = len(queryset)  # 🔥 Query executed

6. list():

queryset = Article.objects.all()articles = list(queryset)  # 🔥 Query executed

7. bool():

queryset = Article.objects.all()if queryset:  # 🔥 Query executed    print("Has articles")

8. Accessing by Index:

queryset = Article.objects.all()first = queryset[0]  # 🔥 Query executed with LIMIT 1

Methods That DON'T Execute Queries

These return new QuerySets without hitting database:

queryset = Article.objects.all()  # No query # All these return QuerySets (lazy)queryset.filter(status='published')     # No queryqueryset.exclude(author=user)           # No queryqueryset.order_by('-created_at')        # No queryqueryset.reverse()                      # No queryqueryset.distinct()                     # No queryqueryset.select_related('author')       # No queryqueryset.prefetch_related('comments')   # No queryqueryset.only('title', 'content')       # No queryqueryset.defer('content')               # No queryqueryset.values('id', 'title')          # No queryqueryset.values_list('id', flat=True)   # No query

Methods That DO Execute Queries

These immediately hit the database:

queryset = Article.objects.all() # All these execute queriesqueryset.count()           # SELECT COUNT(*)queryset.exists()          # SELECT 1 FROM ... LIMIT 1queryset.get(id=1)         # SELECT ... WHERE id=1queryset.create(...)       # INSERT ...queryset.update(...)       # UPDATE ...queryset.delete()          # DELETE ...queryset.first()           # SELECT ... LIMIT 1queryset.last()            # SELECT ... ORDER BY ... DESC LIMIT 1queryset.aggregate(...)    # SELECT aggregate_function(...)

QuerySet Caching

What is QuerySet Caching?

When a QuerySet is evaluated, results are cached in memory. Subsequent accesses use the cache instead of hitting database again.

How Caching Works

Example 1: Cached Iteration

queryset = Article.objects.all() # First iteration: Database queryfor article in queryset:    print(article.title)  # Query: SELECT * FROM articles # Second iteration: Uses cachefor article in queryset:    print(article.title)  # No query! Uses cached results

Example 2: Cached Length Check

queryset = Article.objects.all() # First len(): Database querycount = len(queryset)  # Query: SELECT * FROM articles # Second len(): Uses cachecount = len(queryset)  # No query! Returns cached count

Cache Behavior Patterns

Pattern 1: Full Cache (Good)

queryset = Article.objects.all() # Evaluate entire querysetarticles = list(queryset)  # Query: SELECT * FROM articles # Now cache is populatedprint(len(articles))  # No queryfor article in articles:  # No query    print(article.title)

Pattern 2: Partial Cache (Tricky)

queryset = Article.objects.all() # Access first itemfirst = queryset[0]  # Query: SELECT ... LIMIT 1 # Access second item - NEW query!second = queryset[1]  # Query: SELECT ... LIMIT 1 OFFSET 1 # Slicing doesn't fully cachesubset = queryset[:10]  # Query: SELECT ... LIMIT 10 # Accessing outside slice - NEW query!queryset[15]  # Query: SELECT ... LIMIT 1 OFFSET 15

Pattern 3: No Cache (Iterator Pattern)

queryset = Article.objects.all() # iterator() doesn't cache resultsfor article in queryset.iterator():    print(article.title)  # Query executed, no caching # Second iteration - NEW query!for article in queryset.iterator():    print(article.title)  # Query executed again!

When to Use iterator()

Use Case: Processing large datasets that don't fit in memory.

Without iterator() - Memory Issue:

# Loads ALL articles into memory at oncequeryset = Article.objects.all()  # Imagine 1 million articles for article in queryset:    process(article)  # 💥 Out of memory!

With iterator() - Memory Efficient:

# Loads in chunks, doesn't cachequeryset = Article.objects.all() for article in queryset.iterator(chunk_size=1000):    process(article)  # ✅ Memory efficient

Trade-off:

✅ Lower memory usage
❌ No caching (re-query if accessed again)
❌ Can't len(), can't re-iterate

Cache Invalidation

Problem: Cache doesn't auto-update when data changes.

Example:

queryset = Article.objects.all() # Evaluate and cachearticles = list(queryset)  # 10 articles # Add new article in databaseArticle.objects.create(title='New Article', ...) # Cache still has old dataprint(len(articles))  # Still shows 10, not 11!

Solution 1: Create New QuerySet

# Old queryset (cached)old_queryset = Article.objects.all()old_articles = list(old_queryset)  # Cached # New queryset (fresh from DB)new_queryset = Article.objects.all()new_articles = list(new_queryset)  # Fresh query

Solution 2: Force Re-evaluation

queryset = Article.objects.all() # Evaluatearticles = list(queryset)  # Cached # Clear cache by creating new querysetqueryset = Article.objects.all()  # New queryset objectarticles = list(queryset)  # Fresh query

QuerySet Performance Patterns

Pattern 1: Evaluate Once, Use Many Times

❌ Bad: Multiple Evaluations

def bad_view(request):    # Each access hits database    if Article.objects.filter(status='published').exists():  # Query 1        count = Article.objects.filter(status='published').count()  # Query 2                for article in Article.objects.filter(status='published'):  # Query 3            print(article.title)

✅ Good: Single Evaluation

def good_view(request):    # Evaluate once, cache results    articles = list(Article.objects.filter(status='published'))  # Query 1        if articles:  # Uses cache        count = len(articles)  # Uses cache                for article in articles:  # Uses cache            print(article.title)

Pattern 2: Use exists() for Existence Checks

❌ Bad: Loading All Data

# Loads entire queryset into memoryif Article.objects.filter(author=user):    # Query: SELECT * FROM articles WHERE author_id=1    print("User has articles")

✅ Good: Efficient Existence Check

# Only checks existenceif Article.objects.filter(author=user).exists():    # Query: SELECT 1 FROM articles WHERE author_id=1 LIMIT 1    print("User has articles")

Difference:

if queryset: → Evaluates entire queryset
if queryset.exists(): → Only checks if at least one exists (faster!)

Pattern 3: Use count() vs len()

Scenario 1: Only Need Count

# ✅ Good: Database countcount = Article.objects.filter(status='published').count()# Query: SELECT COUNT(*) FROM articles WHERE status='published'

Scenario 2: Need Count AND Data

# ✅ Good: Evaluate once, use len()articles = list(Article.objects.filter(status='published'))count = len(articles)  # No extra query for article in articles:  # Uses cache    print(article.title)

❌ Bad: Both count() and Iteration

# Query 1: Countcount = Article.objects.filter(status='published').count() # Query 2: Fetch datafor article in Article.objects.filter(status='published'):    print(article.title)

Pattern 4: Slicing Optimization

Limit Results with Slicing:

# Only fetch first 10 articlesarticles = Article.objects.all()[:10]# SQL: SELECT * FROM articles LIMIT 10 # Paginationpage = 2per_page = 20offset = (page - 1) * per_pagearticles = Article.objects.all()[offset:offset+per_page]# SQL: SELECT * FROM articles LIMIT 20 OFFSET 20

Avoid Negative Indexing:

# ❌ Bad: Loads everything, then gets last itemlast = Article.objects.all()[-1] # ✅ Good: Use order_by and limitlast = Article.objects.all().order_by('-id').first()# SQL: SELECT * FROM articles ORDER BY id DESC LIMIT 1

Monitoring Queries

Installation:

pip install django-debug-toolbar

Configuration (settings.py):

INSTALLED_APPS = [    # ...    'debug_toolbar',] MIDDLEWARE = [    'debug_toolbar.middleware.DebugToolbarMiddleware',    # ... other middleware] INTERNAL_IPS = [    '127.0.0.1',]

URLs (urls.py):

from django.urls import path, include if settings.DEBUG:    import debug_toolbar    urlpatterns = [        path('__debug__/', include(debug_toolbar.urls)),    ] + urlpatterns

Features:

View all queries per request
See query execution time
Identify duplicate queries
Analyze query complexity
Check for N+1 problems

Logging Queries in Code

Method 1: connection.queries

from django.db import connectionfrom django.test.utils import override_settings @override_settings(DEBUG=True)  # Required for query loggingdef test_view():    # Your code here    articles = list(Article.objects.all())        # Print all queries    for query in connection.queries:        print(f"SQL: {query['sql']}")        print(f"Time: {query['time']}")

Method 2: Custom Context Manager

from django.db import connectionfrom django.test.utils import CaptureQueriesContext def query_debugger():    """Context manager to track queries"""    with CaptureQueriesContext(connection) as context:        yield context # Usagewith query_debugger() as queries:    articles = list(Article.objects.all())    print(f"Number of queries: {len(queries)}")for query in queries:    print(query['sql'])

Query Count Assertion in Tests

from django.test import TestCasefrom django.test.utils import CaptureQueriesContextfrom django.db import connection class ArticleViewTest(TestCase):    def test_article_list_query_count(self):        """Ensure article list doesn't exceed 3 queries"""        with CaptureQueriesContext(connection) as context:            response = self.client.get('/articles/')                    # Assert query count        self.assertLessEqual(            len(context.captured_queries),             3,            f"Too many queries: {len(context.captured_queries)}"        )

Tóm Tắt Bài 3.1

Trong Bài 3.1 này, bạn đã học:

✅ QuerySet Fundamentals:

QuerySet là lazy, chainable, cached, và iterable
3 phases: Construction → Evaluation → Caching

✅ Lazy Evaluation:

Queries không execute cho đến khi cần data
Build queries incrementally
Conditional filtering patterns

✅ When Queries Execute:

Execute: iteration, len(), bool(), slicing with step, indexing
Don't execute: filter(), exclude(), order_by(), select_related()

✅ QuerySet Caching:

Results cached after first evaluation
Subsequent access uses cache
iterator() bypasses cache for memory efficiency

✅ Performance Patterns:

Evaluate once, use many times
Use exists() for existence checks
Choose count() vs len() appropriately
Optimize slicing và avoid negative indexing

✅ Monitoring:

Django Debug Toolbar for development
connection.queries for programmatic access
CaptureQueriesContext for testing

Exercises

Exercise 1: Query Evaluation Analysis

Task: Identify which lines execute database queries:

def process_articles():    # Line 1    queryset = Article.objects.filter(status='published')        # Line 2    queryset = queryset.order_by('-created_at')        # Line 3    count = queryset.count()        # Line 4    first_10 = queryset[:10]        # Line 5    for article in first_10:        print(article.title)        # Line 6    exists = queryset.exists()        # Line 7    article_list = list(queryset)

Questions:

Which lines execute queries?
How many total queries?
How would you optimize to 2 queries?

Exercise 2: Optimize Query Count

Given code with multiple queries:

def article_stats(author):    published_count = Article.objects.filter(        author=author,         status='published'    ).count()        draft_count = Article.objects.filter(        author=author,        status='draft'    ).count()        if Article.objects.filter(author=author).exists():        latest = Article.objects.filter(author=author).order_by('-created_at')[0]        return {            'published': published_count,            'draft': draft_count,            'latest': latest        }        return None

Task: Reduce to 1 query using annotate và conditional expressions.

Exercise 3: Memory-Efficient Processing

Task: Process 1 million articles without running out of memory.

Requirements:

Calculate total word count across all articles
Update is_long field (True if content > 1000 words)
Print progress every 10,000 articles
Don't load all articles into memory at once

Hints:

Use iterator() with chunk_size
Use update() instead of save() for bulk updates
Consider using bulk_update() for better performance

Next Steps

Trong Bài 3.2, bạn sẽ học:

select_related() cho forward ForeignKey và OneToOne
prefetch_related() cho reverse ForeignKey và ManyToMany
Prefetch objects cho advanced prefetching
Khi nào dùng select_related vs prefetch_related
Nested prefetching patterns

Navigation:

← Bài 2.4: Computed Fields & Model Mixins
Bài 3.1: QuerySet Fundamentals (current)
Bài 3.2: select_related() vs prefetch_related() →

#query-evaluation

Advanced QuerySets và Database Optimization - Phần 1