procédure stocké qui prends trop de temps

15370
,

Bonjour,

Voila je rencontre un petit problème avec mon code.

Ce que je fais

j'ai fait une procédure stocké pour récupérer le resulta d'analyse d'un patient lorsque j'execute la requette le système tourne sans fin et dans les logs je retrouve ceci:

2020-10-24 08:46:43 Debug: duration=15 rows=10 SHOW FULL COLUMNS FROM `type_forms`
2020-10-24 08:46:43 Debug: duration=7 rows=4 SHOW INDEXES FROM `type_forms`
2020-10-24 08:46:43 Debug: duration=48 rows=2 SELECT * FROM information_schema.key_column_usage AS kcu
            INNER JOIN information_schema.referential_constraints AS rc
            ON (
                kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
                AND kcu.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA
            )
            WHERE kcu.TABLE_SCHEMA = 'psi' AND kcu.TABLE_NAME = 'type_forms' AND rc.TABLE_NAME = 'type_forms'
2020-10-24 08:46:43 Debug: duration=6 rows=1 SHOW TABLE STATUS WHERE Name = 'type_forms'
2020-10-24 08:46:43 Debug: duration=39 rows=2 SELECT
                                        date_response,
                                        code_patient,
                                        patients_id,
                                        nom,
                                        prenom,
                                        patients.slug,
                                        type_forms_id,
                                        nom_type
                                    FROM
                                        responses,
                                        type_forms,
                                        patients
                                    WHERE
                                    responses.type_forms_id = type_forms.id AND
                                        responses.patients_id = patients.id AND type_forms_id='36' AND patients.entreprises_id ='1' AND
                                        Date(date_response) BETWEEN '2020-09-27' AND '2020-10-24'
                                    GROUP BY
                                        date_response
2020-10-24 08:46:43 Debug: duration=1 rows=40 SELECT `TypeForms`.`id` AS `TypeForms__id`, `TypeForms`.`nom_type` AS `TypeForms__nom_type` FROM `type_forms` `TypeForms` WHERE (`entreprises_id` = 1 AND `is_active_type_form` = 1)
2020-10-24 09:05:02 Debug: duration=6 rows=46 SHOW FULL COLUMNS FROM `patients`
2020-10-24 09:05:02 Debug: duration=4 rows=4 SHOW INDEXES FROM `patients`
2020-10-24 09:05:02 Debug: duration=51 rows=2 SELECT * FROM information_schema.key_column_usage AS kcu
            INNER JOIN information_schema.referential_constraints AS rc
            ON (
                kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
                AND kcu.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA
            )
            WHERE kcu.TABLE_SCHEMA = 'psi' AND kcu.TABLE_NAME = 'patients' AND rc.TABLE_NAME = 'patients'
2020-10-24 09:05:02 Debug: duration=5 rows=1 SHOW TABLE STATUS WHERE Name = 'patients'
2020-10-24 09:05:02 Debug: duration=2 rows=1 SELECT `Patients`.`id` AS `Patients__id`, `Patients`.`nom` AS `Patients__nom`, `Patients`.`prenom` AS `Patients__prenom`, `Patients`.`age` AS `Patients__age`, `Patients`.`poid` AS `Patients__poid`, `Patients`.`taille` AS `Patients__taille`, `Patients`.`created` AS `Patients__created`, `Patients`.`pays` AS `Patients__pays`, `Patients`.`ville` AS `Patients__ville`, `Patients`.`code_patient` AS `Patients__code_patient`, `Patients`.`is_patient_active` AS `Patients__is_patient_active`, `Patients`.`users_id` AS `Patients__users_id`, `Patients`.`avatar` AS `Patients__avatar`, `Patients`.`sexe` AS `Patients__sexe`, `Patients`.`date_premiere_consultation` AS `Patients__date_premiere_consultation`, `Patients`.`domicile` AS `Patients__domicile`, `Patients`.`numero_dossier` AS `Patients__numero_dossier`, `Patients`.`service` AS `Patients__service`, `Patients`.`investigateur` AS `Patients__investigateur`, `Patients`.`emailInvestigateur` AS `Patients__emailInvestigateur`, `Patients`.`nom_institut` AS `Patients__nom_institut`, `Patients`.`date_naissance` AS `Patients__date_naissance`, `Patients`.`etat_civil` AS `Patients__etat_civil`, `Patients`.`niveau_instruction` AS `Patients__niveau_instruction`, `Patients`.`situation_professionnelle` AS `Patients__situation_professionnelle`, `Patients`.`formation_base` AS `Patients__formation_base`, `Patients`.`travail_exerce_habituellement` AS `Patients__travail_exerce_habituellement`, `Patients`.`nationnalite` AS `Patients__nationnalite`, `Patients`.`dateimigrationofficielle` AS `Patients__dateimigrationofficielle`, `Patients`.`residence` AS `Patients__residence`, `Patients`.`pays_origine` AS `Patients__pays_origine`, `Patients`.`province` AS `Patients__province`, `Patients`.`date_arrive_pays_residence` AS `Patients__date_arrive_pays_residence`, `Patients`.`origine_ethnie` AS `Patients__origine_ethnie`, `Patients`.`religion` AS `Patients__religion`, `Patients`.`autre_origine_autochtone` AS `Patients__autre_origine_autochtone`, `Patients`.`autre_origine_europeen` AS `Patients__autre_origine_europeen`, `Patients`.`autre_origine_africaine` AS `Patients__autre_origine_africaine`, `Patients`.`pratiquant` AS `Patients__pratiquant`, `Patients`.`culture` AS `Patients__culture`, `Patients`.`autre_association_cuturelle` AS `Patients__autre_association_cuturelle`, `Patients`.`slug` AS `Patients__slug`, `Patients`.`entreprises_id` AS `Patients__entreprises_id`, `Patients`.`phone_number` AS `Patients__phone_number`, `Patients`.`last_date_update` AS `Patients__last_date_update`, `Patients`.`date_delete` AS `Patients__date_delete` FROM `patients` `Patients` WHERE `Patients`.`slug` = '368f6200281e71839e24cbeac8ce5f9a9717fb17' LIMIT 1
2020-10-24 09:05:02 Debug: duration=58 rows=0 CALL taux_severite_per_patient('36', '50', '2020-09-28 14:27:18')

quelqu'un a t'il déjà rencontrer ce problème? par contre sur le serveur de produit l'execution est rapide et la requet passe sans soucis mais en local depuis un certain temps ce n'est plus possible