Skip to content

GitLab

  • Projects
  • Groups
  • Snippets
  • Help
    • Loading...
  • Help
    • Help
    • Support
    • Community forum
    • Submit feedback
    • Contribute to GitLab
  • Sign in
vidjil
vidjil
  • Project overview
    • Project overview
    • Details
    • Activity
    • Releases
  • Repository
    • Repository
    • Files
    • Commits
    • Branches
    • Tags
    • Contributors
    • Graph
    • Compare
  • Issues 1,719
    • Issues 1,719
    • List
    • Boards
    • Labels
    • Service Desk
    • Milestones
  • Merge Requests 81
    • Merge Requests 81
  • CI / CD
    • CI / CD
    • Pipelines
    • Jobs
    • Schedules
  • Operations
    • Operations
    • Incidents
    • Environments
  • Packages & Registries
    • Packages & Registries
    • Container Registry
  • Analytics
    • Analytics
    • CI / CD
    • Repository
    • Value Stream
  • Snippets
    • Snippets
  • Members
    • Members
  • Activity
  • Graph
  • Create a new issue
  • Jobs
  • Commits
  • Issue Boards
Collapse sidebar

Attention une mise à jour du serveur va être effectuée le vendredi 16 avril entre 12h et 12h30. Cette mise à jour va générer une interruption du service de quelques minutes.

  • vidjil
  • vidjilvidjil
  • Issues
  • #2475

Closed
Open
Opened May 19, 2017 by Ryan Herbert@RyanHerbContributor

Réduire le nombre de requêtes pour la requête Sample_Set

Dans la même veine que #2439 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
To upload designs, you'll need to enable LFS and have an admin enable hashed storage. More information
Assignee
Assign to
None
Milestone
None
Assign milestone
Time tracking
None
Due date
None
Reference: vidjil/vidjil#2475