Now, let's take a look at the kind of caching we can use at the application level, which can prove to be a major help.

To understand the concept of caching at the application block level, let's take a look at the following simple code snippet:

for name in ['super_admin', 'admin', 'user']:
  if db_session.query(User).first().role.role_name == name:
    print("True")

From what we can assume, this could have done a query once and then retrieved the data from the database, and then will have used it again and again to compare it with the name variable. But let's take a look at the output of the preceding code:

INFO sqlalchemy.engine.base.Engine SELECT users.username AS users_username, users.id AS users_id, users.role_id AS users_role_id 
FROM users 
 LIMIT %(param_1)s
INFO sqlalchemy.engine.base.Engine {'param_1': 1}
True
sqlalchemy.engine.base.Engine SELECT users.username AS users_username, users.id AS users_id, users.role_id AS users_role_id 
FROM users 
 LIMIT %(param_1)s
sqlalchemy.engine.base.Engine {'param_1': 1}
sqlalchemy.engine.base.Engine SELECT users.username AS users_username, users.id AS users_id, users.role_id AS users_role_id 
FROM users 
 LIMIT %(param_1)s
sqlalchemy.engine.base.Engine {'param_1': 1}
sqlalchemy.engine.base.Engine SELECT users.username AS users_username, users.id AS users_id, users.role_id AS users_role_id 
FROM users 
 LIMIT %(param_1)s
INFO sqlalchemy.engine.base.Engine {'param_1': 1}

From this output, we can see that the ORM layer didn't cache the query and act as a simple object, but rather executed four queries to fetch the data again and again.

Although this mistake was made intentionally, it shows the side effects of not caching data at the block level, and how costly it would be if this code was to iterate over a lot of records. Although this mistake was costly, it can be avoided quite easily with the use of block-level caching. The following code snippet modifies the preceding example to use block-level caching:

user = db_session.query(User).first()
user_role = user.role.role_name 
for name in ['super_admin', 'admin', 'user']:
  if user_role == name:
    print("True")

When run, this code just executes one query to fetch data from the database, and then uses the same data again and again to compare it without issuing a query to the database.

A simple implementation of a block-level variable to cache the data helped us to reduce the load on the database considerably, while also improving the response of the application.