⚡ Performance & Best Practices

Database performance is critical for user experience. This section covers fundamental optimization techniques and common pitfalls to avoid.

📇 Indexing Fundamentals

Indexes speed up data retrieval by creating a lookup structure.

When to Use Indexes:

• Columns frequently used in WHERE clauses

• Columns used in JOIN conditions

• Columns used in ORDER BY

• Large tables with many rows

When to Avoid Indexes:

• Small tables (< 1000 rows)

• Columns with many NULL values

• Columns with low cardinality (few unique values)

• Frequently updated columns

Example 1: Creating and Using Indexes

import sqlite3
import time

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

# Create table
cursor.execute("""
    CREATE TABLE IF NOT EXISTS books (
        book_id INTEGER PRIMARY KEY,
        title TEXT NOT NULL,
        author_id INTEGER NOT NULL,
        price REAL,
        isbn TEXT UNIQUE
    )
""")

# Create an index on the author_id column
cursor.execute("CREATE INDEX IF NOT EXISTS idx_author_id ON books(author_id)")
print("Index created on author_id")

# Create a composite index for multiple columns
cursor.execute("""
    CREATE INDEX IF NOT EXISTS idx_author_price 
    ON books(author_id, price)
""")
print("Composite index created on author_id, price")

conn.commit()
conn.close()

Example 2: Comparing Performance with/without Index

import sqlite3
import time

# Create two test databases
conn_no_index = sqlite3.connect(':memory:')
conn_with_index = sqlite3.connect(':memory:')

# Setup both
for conn in [conn_no_index, conn_with_index]:
    cursor = conn.cursor()
    cursor.execute("""
        CREATE TABLE books (
            book_id INTEGER PRIMARY KEY,
            author_id INTEGER,
            price REAL,
            title TEXT
        )
    """)
    
    # Insert test data
    cursor.executemany(
        "INSERT INTO books (author_id, price, title) VALUES (?, ?, ?)",
        [(i % 100, 10 + (i % 50), f'Book {i}') for i in range(10000)]
    )
    conn.commit()

# Add index to second database
cursor = conn_with_index.cursor()
cursor.execute("CREATE INDEX idx_author_id ON books(author_id)")
conn_with_index.commit()

# Test queries
query = "SELECT COUNT(*) FROM books WHERE author_id = 42"

# Without index
start = time.time()
for _ in range(100):
    conn_no_index.cursor().execute(query)
time_without = time.time() - start

# With index
start = time.time()
for _ in range(100):
    conn_with_index.cursor().execute(query)
time_with = time.time() - start

print(f"Without index: {time_without:.4f}s")
print(f"With index: {time_with:.4f}s")
print(f"Speedup: {time_without/time_with:.2f}x faster")

conn_no_index.close()
conn_with_index.close()

🎯 Query Optimization Techniques

Example 3: Efficient Filtering

import sqlite3

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

# INEFFICIENT: Using functions in WHERE clause prevents index use
cursor.execute("""
    SELECT * FROM books
    WHERE LOWER(title) = 'the great gatsby'
""")

# EFFICIENT: Direct comparison
cursor.execute("""
    SELECT * FROM books
    WHERE title = 'The Great Gatsby'
""")

# INEFFICIENT: Trailing wildcard prevents index use
cursor.execute("""
    SELECT * FROM books
    WHERE title LIKE '%Gatsby%'
""")

# EFFICIENT: Leading comparison uses index
cursor.execute("""
    SELECT * FROM books
    WHERE title LIKE 'The Great%'
""")

# INEFFICIENT: OR with multiple columns may not use indexes
cursor.execute("""
    SELECT * FROM books
    WHERE author_id = 5 OR author_id = 10
""")

# EFFICIENT: Use IN instead
cursor.execute("""
    SELECT * FROM books
    WHERE author_id IN (5, 10)
""")

conn.close()

Example 4: SELECT Only Needed Columns

import sqlite3

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

# INEFFICIENT: Fetches all columns even if not needed
cursor.execute("SELECT * FROM books")
all_data = cursor.fetchall()

# EFFICIENT: Select only needed columns
cursor.execute("SELECT book_id, title, price FROM books")
needed_data = cursor.fetchall()

print(f"All columns: {len(all_data[0]) if all_data else 0} fields per row")
print(f"Needed columns: {len(needed_data[0]) if needed_data else 0} fields per row")

conn.close()

🔄 Batch Operations

Example 5: Batch Operations vs Individual Operations

import sqlite3
import time

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

cursor.execute("""
    CREATE TABLE items (
        id INTEGER PRIMARY KEY,
        name TEXT,
        value INTEGER
    )
""")

test_data = [(f'Item {i}', i) for i in range(1000)]

# INEFFICIENT: Individual inserts with separate commits
cursor.execute("DELETE FROM items")
conn.commit()

start = time.time()
for name, value in test_data:
    cursor.execute("INSERT INTO items (name, value) VALUES (?, ?)", (name, value))
    conn.commit()  # Commit each insert
inefficient_time = time.time() - start

# EFFICIENT: Batch insert with single commit
cursor.execute("DELETE FROM items")
conn.commit()

start = time.time()
cursor.executemany(
    "INSERT INTO items (name, value) VALUES (?, ?)",
    test_data
)
conn.commit()  # Single commit
efficient_time = time.time() - start

print(f"Individual inserts: {inefficient_time:.4f}s")
print(f"Batch insert: {efficient_time:.4f}s")
print(f"Speedup: {inefficient_time/efficient_time:.1f}x faster")

conn.close()

🚫 Common Performance Mistakes

Example 6: N+1 Query Problem

import sqlite3

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

# INEFFICIENT: N+1 queries
cursor.execute("SELECT book_id, author_id, title FROM books")
books = cursor.fetchall()

print("INEFFICIENT (N+1):")
for book_id, author_id, title in books:
    # Extra query for each book!
    cursor.execute("SELECT name FROM authors WHERE author_id = ?", (author_id,))
    author = cursor.fetchone()
    print(f"  {title} by {author[0]}")

# EFFICIENT: Single join query
print("\nEFFICIENT (JOIN):")
cursor.execute("""
    SELECT b.title, a.name
    FROM books b
    JOIN authors a ON b.author_id = a.author_id
""")

for title, author in cursor.fetchall():
    print(f"  {title} by {author}")

conn.close()

Example 7: Unnecessary Data Processing

import sqlite3

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

# INEFFICIENT: Fetch all data then filter in Python
cursor.execute("SELECT * FROM books")
all_books = cursor.fetchall()
expensive_books = [b for b in all_books if b[3] > 25]  # Price in column 3

# EFFICIENT: Filter in database
cursor.execute("SELECT * FROM books WHERE price > 25")
expensive_books = cursor.fetchall()

print(f"Expensive books: {len(expensive_books)}")

conn.close()

📊 Analyzing Query Performance

Example 8: Using EXPLAIN QUERY PLAN

import sqlite3

conn = sqlite3.connect('bookstore.db')
cursor = conn.cursor()

# Analyze how SQLite executes a query
cursor.execute("""
    EXPLAIN QUERY PLAN
    SELECT title FROM books
    WHERE author_id = 5
    ORDER BY price DESC
""")

plan = cursor.fetchall()
print("Query Execution Plan:")
for row in plan:
    print(f"  {row}")

# Look for these phrases:
# - "SEARCH" indicates index usage (good)
# - "SCAN" indicates full table scan (potentially slow)
# - "USE TEMP B-TREE" indicates sorting in memory

conn.close()

✅ Best Practices Summary

Example 9: Complete Example with Best Practices

import sqlite3

def setup_optimized_database():
    conn = sqlite3.connect('optimized.db')
    cursor = conn.cursor()
    
    # Create table with appropriate types
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS products (
            product_id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            category_id INTEGER NOT NULL,
            price REAL NOT NULL,
            stock_quantity INTEGER,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)
    
    # Create indexes for frequently queried columns
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_category_id ON products(category_id)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_price ON products(price)")
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_created_at ON products(created_at)")
    
    conn.commit()
    return conn

def query_products_optimized(conn, category_id=None, min_price=None):
    """Query products with filters - uses indexes"""
    query = "SELECT product_id, name, price FROM products WHERE 1=1"
    params = []
    
    if category_id is not None:
        query += " AND category_id = ?"
        params.append(category_id)
    
    if min_price is not None:
        query += " AND price >= ?"
        params.append(min_price)
    
    query += " ORDER BY price ASC LIMIT 100"
    
    cursor = conn.cursor()
    cursor.execute(query, params)
    return cursor.fetchall()

# Usage
conn = setup_optimized_database()

# Efficient queries
results = query_products_optimized(conn, category_id=5, min_price=10)
print(f"Found {len(results)} products")

conn.close()

🔑 Key Takeaways

• **Indexes** speed up queries on frequently filtered columns

• **SELECT only needed columns** reduces data transfer

• **Use WHERE clauses in database** instead of filtering in Python

• **Batch operations** with executemany() are much faster

• **Avoid N+1 queries** - use JOINs instead of separate queries

• **Use IN instead of OR** for better index utilization

• **EXPLAIN QUERY PLAN** reveals how SQLite executes queries

• **Profile before optimizing** - measure, don't guess

📚 Further Learning

Master advanced optimization:

• [Advanced Performance Optimization](/courses/python/database_basics/advanced/database_performance) - Query profiling and VACUUM/ANALYZE

• [Advanced Querying](/courses/python/database_basics/advanced/querying_data) - Complex queries with CTEs

• [Challenges & Quizzes](/#) - Test your optimization skills