Réduire le nombre de requêtes pour la requête Sample_Set
Dans la même veine que #2439 (closed) il serait bien de réduire la charge de la BDD, et d'essayer de rendre l'application plus rapide.
Voici ce qu'il se passe du point de vue de la BDD pour une requête de 4 patients (découpée en plusieurs blocks à mesure que j'identifie les requêtes) :
SELECT 1
SET FOREIGN_KEY_CHECKS=1
SET sql_mode='NO_BACKSLASH_ESCAPES'
SELECT auth_membership.id, auth_membership.user_id, auth_membership.group_id FROM auth_membership WHERE (auth_membership.user_id = 1)
SELECT auth_group.id, auth_group.role, auth_group.description FROM auth_group WHERE (auth_group.id = 1)
SELECT auth_group.id, auth_group.role, auth_group.description FROM auth_group WHERE (auth_group.id = 2)
SELECT auth_group.id, auth_group.role, auth_group.description FROM auth_group WHERE (auth_group.id = 3)
commit
Ces requêtes sont de self.has_permission()
dans vidjil_accessible_query()
. Le but étant de vérifier si l'utilisateur a les accès "admin" pour cette table et cette action:
SELECT auth_membership.group_id FROM auth_membership WHERE (auth_membership.user_id = 1)
SELECT auth_permission.group_id FROM auth_permission WHERE (((auth_permission.name = 'read') AND (auth_permission.table_name = 'patient')) AND (auth_permission.record_id = 0))
Je suis admin, donc on renvoi tous les patients:
SELECT patient.id, patient.first_name, patient.last_name, patient.birth, patient.info, patient.id_label, patient.creator, patient.sample_set_id FROM patient WHERE (patient.id > 0) ORDER BY patient.id DESC LIMIT 51 OFFSET 0
Ici c'est un appel à load_permissions()
, qui fait appel à vidjil_accessible_query()
deux fois (pour charger tous les patients/runs/sets en mémoire et stocker la permission):
SELECT auth_membership.group_id FROM auth_membership WHERE (auth_membership.user_id = 1)
SELECT auth_permission.group_id FROM auth_permission WHERE (((auth_permission.name = 'read') AND (auth_permission.table_name = 'patient')) AND (auth_permission.record_id = 0))
SELECT patient.id FROM patient WHERE (patient.id > 0)
SELECT auth_membership.group_id FROM auth_membership WHERE (auth_membership.user_id = 1)
SELECT auth_permission.group_id FROM auth_permission WHERE (((auth_permission.name = 'admin') AND (auth_permission.table_name = 'patient')) AND (auth_permission.record_id = 0))
SELECT patient.id FROM patient WHERE (patient.id > 0)
Encore un appel à load_permissions()
cette fois-ci pour les permissions anon. On n'est pas "admin" car les admins doivent voir les vrais noms de leurs patients :) :
SELECT auth_membership.group_id FROM auth_membership WHERE (auth_membership.user_id = 1)
SELECT auth_permission.group_id FROM auth_permission WHERE (((auth_permission.name = 'read') AND (auth_permission.table_name = 'patient')) AND (auth_permission.record_id = 0))
SELECT patient.id FROM patient WHERE (patient.id > 0)
SELECT auth_membership.group_id FROM auth_membership WHERE (auth_membership.user_id = 1)
SELECT auth_permission.group_id FROM auth_permission WHERE (((auth_permission.name = 'anon') AND (auth_permission.table_name = 'patient')) AND (auth_permission.record_id = 0))
SELECT auth_membership.group_id FROM auth_permission, auth_membership WHERE ((((auth_membership.user_id = 1) AND (auth_membership.group_id = auth_permission.group_id)) AND (auth_permission.record_id = 0)) AND (auth_permission.name = 'anon'))
SELECT patient.id FROM patient WHERE ((patient.id IN (SELECT auth_permission.record_id FROM auth_permission, auth_membership WHERE (((((auth_membership.user_id = 1) AND (auth_membership.group_id IN (1))) AND (auth_membership.group_id = auth_permission.group_id)) AND (auth_permission.name = 'access')) AND (auth_permission.table_name = 'patient')))) OR (patient.id IN (SELECT auth_permission.record_id FROM auth_permission, group_assoc, auth_membership WHERE ((((((auth_membership.user_id = 1) AND (auth_membership.group_id IN (1))) AND (auth_membership.group_id = group_assoc.second_group_id)) AND (group_assoc.first_group_id = auth_permission.group_id)) AND (auth_permission.name = 'access')) AND (auth_permission.table_name = 'patient')))))
Ici on a des vérifications d'existence effectuées dans les fonctions can_view_info()
:
SELECT patient.id, patient.first_name, patient.last_name, patient.birth, patient.info, patient.id_label, patient.creator, patient.sample_set_id FROM patient WHERE (patient.id = 11) LIMIT 1 OFFSET 0
SELECT patient.id, patient.first_name, patient.last_name, patient.birth, patient.info, patient.id_label, patient.creator, patient.sample_set_id FROM patient WHERE (patient.id = 10) LIMIT 1 OFFSET 0
SELECT patient.id, patient.first_name, patient.last_name, patient.birth, patient.info, patient.id_label, patient.creator, patient.sample_set_id FROM patient WHERE (patient.id = 9) LIMIT 1 OFFSET 0
SELECT patient.id, patient.first_name, patient.last_name, patient.birth, patient.info, patient.id_label, patient.creator, patient.sample_set_id FROM patient WHERE (patient.id = 8) LIMIT 1 OFFSET 0
Ces requêtes sont relativement raisonnables. Récupèrent les noms des créateurs et les tailles des sample sets:
SELECT patient.id, auth_user.last_name FROM auth_user, patient WHERE ((patient.creator = auth_user.id) AND (patient.id IN (8,9,10,11)))
SELECT patient.id, sequence_file.size_file FROM sample_set_membership, patient, sequence_file WHERE (((sample_set_membership.sample_set_id = patient.sample_set_id) AND (sequence_file.id = sample_set_membership.sequence_file_id)) AND (patient.id IN (8,9,10,11)))
load_config_information
contient deux vidjil_accessible_query
:
SELECT auth_membership.group_id FROM auth_membership WHERE (auth_membership.user_id = 1)
SELECT auth_permission.group_id FROM auth_permission WHERE (((auth_permission.name = 'read') AND (auth_permission.table_name = 'config')) AND (auth_permission.record_id = 0))
SELECT auth_membership.group_id FROM auth_membership WHERE (auth_membership.user_id = 1)
SELECT auth_permission.group_id FROM auth_permission WHERE (((auth_permission.name = 'admin') AND (auth_permission.table_name = 'config')) AND (auth_permission.record_id = 0))
SELECT patient.id, config.name, config.id, fused_file.fused_file FROM patient, config, fused_file WHERE ((((patient.sample_set_id = fused_file.sample_set_id) AND (fused_file.config_id = config.id)) AND ((config.id >
0) OR (config.id > 0))) AND (patient.id IN (8,9,10,11)))
load_permitted_groups()
:
SELECT patient.id, auth_group.role FROM auth_group, auth_permission, patient WHERE ((((((patient.id = auth_permission.record_id) OR (auth_permission.record_id = 0)) AND (auth_permission.table_name = 'patient')) AND (auth_permission.name = 'access')) AND (auth_group.id = auth_permission.group_id)) AND (patient.id IN (8,9,10,11)))
load_anon_permissions()
... On n'avait pas fait ça plus haut ??? :
SELECT patient.id FROM auth_group, auth_permission, patient, auth_membership WHERE (((((((auth_permission.name = 'anon') AND (auth_permission.table_name = 'patient')) AND (patient.id = auth_permission.record_id)) AND (auth_group.id = auth_permission.group_id)) AND (auth_membership.user_id = 1)) AND (auth_membership.group_id = auth_group.id)) AND (patient.id IN (8,9,10,11)))
On charge tous les utilisateurs ? Pas trouvé dans le code... :
SELECT auth_user.id, auth_user.first_name, auth_user.last_name, auth_user.email, auth_user.password, auth_user.registration_key, auth_user.reset_password_key, auth_user.registration_id FROM auth_user WHERE (auth_user.id > 1)
Encore des vérifications d'existence... Pas trouvé l'endroit dans le code:
SELECT patient.id, patient.first_name, patient.last_name, patient.birth, patient.info, patient.id_label, patient.creator, patient.sample_set_id FROM patient WHERE (patient.id = 11) LIMIT 1 OFFSET 0
SELECT patient.id, patient.first_name, patient.last_name, patient.birth, patient.info, patient.id_label, patient.creator, patient.sample_set_id FROM patient WHERE (patient.id = 10) LIMIT 1 OFFSET 0
SELECT patient.id, patient.first_name, patient.last_name, patient.birth, patient.info, patient.id_label, patient.creator, patient.sample_set_id FROM patient WHERE (patient.id = 9) LIMIT 1 OFFSET 0
SELECT patient.id, patient.first_name, patient.last_name, patient.birth, patient.info, patient.id_label, patient.creator, patient.sample_set_id FROM patient WHERE (patient.id = 8) LIMIT 1 OFFSET 0
Vérifie si l'utilisateur a le droit de créer des patients/runs/sets :
SELECT auth_membership.group_id FROM auth_permission, auth_membership WHERE ((((auth_membership.user_id = 1) AND (auth_membership.group_id = auth_permission.group_id)) AND (auth_permission.record_id = 0)) AND (auth_permission.name = 'create'))
commit
A identifier:
SELECT 1
SET FOREIGN_KEY_CHECKS=1
SET sql_mode='NO_BACKSLASH_ESCAPES'
SELECT auth_membership.id, auth_membership.user_id, auth_membership.group_id FROM auth_membership WHERE (auth_membership.user_id = 1)
SELECT auth_group.id, auth_group.role, auth_group.description FROM auth_group WHERE (auth_group.id = 1)
SELECT auth_group.id, auth_group.role, auth_group.description FROM auth_group WHERE (auth_group.id = 2)
SELECT auth_group.id, auth_group.role, auth_group.description FROM auth_group WHERE (auth_group.id = 3)
commit