Vous êtes ici : Accueil Tutoriels SQLAlchemy Stratégies de chargement

Stratégies de chargement

En SQL plus qu'ailleurs, la performance compte. Or, lorsque l'on utilise un ORM, il n'y a rien de plus simple que de mettre une requête dans une boucle sans s'en apercevoir.

Dans cet exemple, on utilise les classes User et Group que l'on trouve dans des projets comme Turbogears :

user_group_table = Table('auth_user_group', metadata,
Column('user_id', Integer, ForeignKey('auth_user.id', onupdate="CASCADE", ondelete="CASCADE"), primary_key=True),
Column('group_id', Integer, ForeignKey('auth_group.id', onupdate="CASCADE", ondelete="CASCADE"), primary_key=True)
)


class Group(Base):
__tablename__ = 'auth_group'
id = Column(Integer, autoincrement=True, primary_key=True)
name = Column(Unicode(16), unique=True, nullable=False)
display_name = Column(Unicode(255))
created = Column(DateTime, default=datetime.now)

users = relation('User', secondary=user_group_table, backref='groups')


class User(Base):
__tablename__ = 'auth_user'
id = Column(Integer, autoincrement=True, primary_key=True)
name = Column(Unicode(16), unique=True, nullable=False)
email_address = Column(Unicode(255), unique=True, nullable=False, info={'rum': {'field':'Email'}})
display_name = Column(Unicode(255))
_password = Column('password', Unicode(128), info={'rum': {'field':'Password'}})
created = Column(DateTime, default=datetime.now)

Analysons rapidement le code suivant :

users = DBSession.query(User).all()
for user in users:
    user.groups

Lorsque l'on fait ceci, pour n utilisateurs, on crée n+1 requêtes, soit, dans le détail une requête pour récupérer les informations utilisateur :

SELECT auth_user.password AS auth_user_password,
 auth_user.id AS auth_user_id,
 auth_user.name AS auth_user_name,
 auth_user.email_address AS auth_user_email_address,
 auth_user.display_name AS auth_user_display_name,
 auth_user.created AS auth_user_created
FROM auth_user

Et une requête par groupe, pour chaque utilisateur:

SELECT auth_group.id AS auth_group_id,
auth_group.name AS auth_group_name,
auth_group.display_name AS auth_group_display_name,
 auth_group.created AS auth_group_created
FROM auth_group, auth_user_group
WHERE ? = auth_user_group.user_id AND auth_group.id = auth_user_group.group_id

C'est ce que l'on nomme le chargement paresseux (ou lazy loading). Il est très utile lorsque notre souhait est de ne charger que ce dont on a besoin, puisque ce chargement se fait à la demande et qu'il est inutile d'aller charger n données pour n'en lire qu'une. Cependant, si on lit dans une boucle toutes les données, alors ce chargement paresseux (qui est le comportement par défaut) devient coûteux. Il faut alors prendre les devants et demander clairement à ce que toutes les données nécessaires soient chargées d'office (Eager loading).

La réponse apportée par les ORM consiste en l'utilisation de stratégies de chargement. La première d'entre elle consiste à demander explicitement d'utiliser une jointure :

from sqlalchemy.orm import joinedload

users = DBSession.query(User).options(joinedload('groups')).all()
for user in users:
    user.groups

Ceci permet donc de modifier la requête chargeant les utilisateurs pour inclure une jointure vers les groupes :

SELECT auth_user.password AS auth_user_password,
 auth_user.id AS auth_user_id,
 auth_user.name AS auth_user_name,
 auth_user.email_address AS auth_user_email_address,
 auth_user.display_name AS auth_user_display_name,
 auth_user.created AS auth_user_created,
 auth_group_1.id AS auth_group_1_id,
 auth_group_1.name AS auth_group_1_name,
 auth_group_1.display_name AS auth_group_1_display_name,
 auth_group_1.created AS auth_group_1_created
FROM auth_user
LEFT OUTER JOIN auth_user_group AS auth_user_group_1 ON auth_user.id = auth_user_group_1.user_id
LEFT OUTER JOIN auth_group AS auth_group_1 ON auth_group_1.id = auth_user_group_1.group_id

Pour seulement 5 utilisateurs et 3 groupes, l'économie de temps réalisée avec une base SQLite est de 92%. Autrement dit, l'optimisation est très rentable par rapport à ce qu'elle coûte programmatiquement parlant.

L'autre réponse consiste à demander non pas une jointure, mais la réalisation d'une sous-requête en plus de la première requête :

SELECT auth_group.id AS auth_group_id,
 auth_group.name AS auth_group_name,
 auth_group.display_name AS auth_group_display_name,
 auth_group.created AS auth_group_created,
 anon_1.auth_user_id AS anon_1_auth_user_id
FROM (SELECT auth_user.id AS auth_user_id FROM auth_user) AS anon_1
JOIN auth_user_group AS auth_user_group_1 ON anon_1.auth_user_id = auth_user_group_1.user_id
JOIN auth_group ON auth_group.id = auth_user_group_1.group_id
ORDER BY anon_1.auth_user_id

Ainsi, on ne touche absolument pas à la première requête allant chercher les utilisateurs, mais on regroupe toutes les informations sur les groupes dans une seule et unique requête.

Pour seulement 5 utilisateurs et 3 groupes, l'économie de temps réalisée avec une base SQLite est de 89%.

Le choix entre jointure et sous-requête dépend du contexte et sera à réaliser selon les besoins. Toujours est-il que dès lors que l'on a besoin d'une donnée, il faut se poser la question de son chargement avant de découvrir les problèmes de performance en production.

Spinner