Database query optimization in Flask - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding ORM Lazy Loading

In Flask with SQLAlchemy, what is the main effect of using lazy='dynamic' on a relationship?

A It disables loading of related items completely.

B It loads all related items immediately when the parent object is loaded.

C It caches related items in memory permanently after first load.

D It delays loading related items until explicitly queried, returning a query object instead of a list.

💡 Hint

Think about when related data is fetched from the database.

Using lazy='dynamic' returns a query object for related items, so you can filter or paginate before loading. This delays actual database access until you explicitly ask for data.

❓ component_behavior

intermediate

Effect of N+1 Query Problem

Given a Flask app using SQLAlchemy, what happens if you access a related attribute in a loop without eager loading?

Flask

users = User.query.all()
for user in users:
    print(user.posts)

A Only one query runs for users and posts separately, then cached.

B One query for users, then one query per user to load posts, causing many queries.

C No queries run until posts are accessed outside the loop.

D One single query joins users and posts, loading all data at once.

💡 Hint

Consider how many database queries happen inside the loop.

Without eager loading, accessing user.posts triggers a separate query per user, causing the N+1 query problem.

📝 Syntax

advanced

Correct Use of joinedload for Eager Loading

Which option correctly applies eager loading with joinedload in Flask SQLAlchemy to avoid N+1 queries?

Flask

from sqlalchemy.orm import joinedload

users = User.query.options(_____).all()

A joinedload(User.posts)

B joinedload('User.posts')

C joinedload(posts)

D joinedload('posts')

💡 Hint

Check how to reference the relationship attribute correctly.

The correct syntax uses the class attribute User.posts inside joinedload(). Passing a string or undefined variable causes errors.

🔧 Debug

advanced

Diagnosing Slow Query in Flask App

A Flask app runs slowly when listing users and their posts. The code is: users = User.query.all() for user in users: print(len(user.posts)) What is the main cause of the slowness?

A The code triggers one query for users plus one query per user to load posts (N+1 problem).

B The query uses a join that returns duplicate users causing slow processing.

C The posts attribute is not defined, causing repeated errors slowing the app.

D The database connection is closed before queries run, causing retries.

💡 Hint

Think about how many queries happen inside the loop.

Accessing user.posts inside the loop without eager loading causes a separate query per user, leading to slow performance.

❓ state_output

expert

Output of Query with Subqueryload and Filtering

Consider this Flask SQLAlchemy code: posts = Post.query.options(subqueryload(Post.comments)).filter(Post.title.like('%Flask%')).all() What is true about the loaded comments for each post?

A Comments are loaded lazily one by one when accessed, causing many queries.

B Only comments containing 'Flask' are loaded due to the filter on posts.

C All comments for each post are loaded in one additional query using subqueryload.

D No comments are loaded because subqueryload does not work with filters.

💡 Hint

Remember what subqueryload does with related collections.

subqueryload loads all related comments for the filtered posts in one separate query, improving performance by avoiding N+1 queries.