IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Access Discussion :

Optimisation d'une requête avec sous-requête [AC-2019]


Sujet :

Access

  1. #1
    Membre émérite
    Homme Profil pro
    Formateur et développeur bureautique
    Inscrit en
    Mars 2007
    Messages
    1 410
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Formateur et développeur bureautique
    Secteur : Conseil

    Informations forums :
    Inscription : Mars 2007
    Messages : 1 410
    Points : 2 871
    Points
    2 871
    Par défaut Optimisation d'une requête avec sous-requête
    Bonjour

    J'ai créé une nouvelle requête dans ma base de données, mais le temps de traitement est trop long (une bonne quinzaine de secondes).
    Je sais bien que cela est dû à la conception de la requête, mais je ne vois pas comment faire autrement.

    Voici les tables :
    Nom : Relations3.png
Affichages : 99
Taille : 18,5 Ko

    Tous les champs commençant par Id sont de type Numérique/Entier Long.
    Les clés primaires sont indéxées sans doublons.
    Les clés étrangères sont indexées avec doublons.
    La table TZPersonnes recense les personnes et contient 180 enregistrements.
    La table TContrats recense les contrats des personnes et contient 200 enregistrements. Certaines personnes ont eu des évolutions et ont ont eu plusieurs contrats successifs. Le champs DateEffet indique à partir de quand le nouveau contrat est effectif. Les dates vont du 01/01/2010 au 12/02/2024 (des contrats prendront effet la semaine prochaine).
    La table TPlanning recense pour chaque personne ce qu'elle fait chaque jour et contient 22500 enregistrements. En général, il y a un enregistrement par jour et par personne. Toutefois, une personne peut avoir plusieurs enregistrements le même jour. il est rare qu'une personne n'ait pas au moins un enregistrement sur un jour.


    Mon objectif :
    Trouver pour chaque enregistrement de la TPlanning, quel est le IdTypeContrat et le HoraireJ qui s'appliquent à la personne pour ce jour-là.

    Voici le code de ma requête :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    SELECT TPlanning.IdPlanning, TPlanning.IdPersonne, TPlanning.DateDeb, TContrats.IdTypeContrat, TContrats.HoraireJ
    FROM (TZPersonnes INNER JOIN TContrats ON TZPersonnes.IdPersonne = TContrats.IdPersonne) INNER JOIN TPlanning ON TZPersonnes.IdPersonne = TPlanning.IdPersonne
    WHERE TContrats.DateEffet 
                 In (SELECT Max(TC1.DateEffet) AS MaxDeDateEffet
                 FROM TContrats as TC1
                 WHERE TC1.DateEffet<=TPlanning.DateDeb
                 GROUP BY TC1.IdPersonne
                 HAVING TC1.IdPersonne=TPlanning.IdPersonne)
    ORDER BY TPlanning.DateDeb;
    J'affiche les champs IdPlanning, IdPersonne, DateDeb, IdTypeContrat, HoraireJ pour lesquels la DateEffet est la plus grande dans la TContrats tout en étant inférieure (ou égale) à la DateDeb pour cet IdPersonne.

    Mais je comprends bien que pour mes 22500 enregistrements, Access exécute cette sous-requête autant de fois. C'est d'autant plus long, qu'il s'agit d'une requête avec regroupement.

    Quelle méthode je pourrais utiliser pour accélérer le traitement de cette requête ?

    Je remercie d'avance ceux qui me répondront.

    Bonne journée à chacun

    Pierre Dumas
    Merci de cliquer sur pour chaque message ayant aidé puis sur pour clore cette discussion.

  2. #2
    Rédacteur/Modérateur

    Avatar de User
    Homme Profil pro
    Développeur informatique
    Inscrit en
    Août 2004
    Messages
    8 260
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : France, Ain (Rhône Alpes)

    Informations professionnelles :
    Activité : Développeur informatique

    Informations forums :
    Inscription : Août 2004
    Messages : 8 260
    Points : 19 423
    Points
    19 423
    Billets dans le blog
    63
    Par défaut
    Bonjour,

    As-tu besoin d'utiliser la table intermédiaire ?

    Peut-être essayer cette requête :

    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    SELECT TPlanning.IdPlanning, TPlanning.IdPersonne, TPlanning.DateDeb, TContrats.IdTypeContrat, TContrats.HoraireJ
    FROM TContrats INNER JOIN TPlanning ON TContrats.IdPersonne = TPlanning.IdPersonne
    WHERE TContrats.DateEffet = (SELECT Max(TC1.DateEffet) AS MaxDeDateEffet
                                 FROM TContrats as TC1
                                 WHERE (TC1.DateEffet<=TPlanning.DateDeb) and (TC1.IdPersonne=TPlanning.IdPersonne))
    ORDER BY TPlanning.DateDeb;
    Vous trouverez dans la FAQ, les sources ou les tutoriels, de l'information accessible au plus grand nombre, plein de bonnes choses à consulter sans modération

    Des tutoriels pour apprendre à créer des formulaires de planning dans vos applications Access :
    Gestion sur un planning des présences et des absences des employés
    Gestion des rendez-vous sur un calendrier mensuel


    Importer un fichier JSON dans une base de données Access :
    Import Fichier JSON

  3. #3
    Membre émérite
    Homme Profil pro
    Formateur et développeur bureautique
    Inscrit en
    Mars 2007
    Messages
    1 410
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Formateur et développeur bureautique
    Secteur : Conseil

    Informations forums :
    Inscription : Mars 2007
    Messages : 1 410
    Points : 2 871
    Points
    2 871
    Par défaut
    Merci pour cette proposition

    On passe à environ 4 secondes.

    En testant, le fait que l'on laisse la table intermédiaire ou pas ne change rien.

    Le vrai apport de la proposition est de ne plus faire de GROUP BY et de HAVING, mais de tout mettre dans le WHERE.

    Est-ce que quelqu'un a une autre idée pour accélérer encore le traitement ?

    Merci encore

    Bonne journée
    Merci de cliquer sur pour chaque message ayant aidé puis sur pour clore cette discussion.

  4. #4
    Expert éminent sénior
    Avatar de tee_grandbois
    Homme Profil pro
    retraité
    Inscrit en
    Novembre 2004
    Messages
    8 641
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 67
    Localisation : France, Yvelines (Île de France)

    Informations professionnelles :
    Activité : retraité

    Informations forums :
    Inscription : Novembre 2004
    Messages : 8 641
    Points : 14 616
    Points
    14 616
    Par défaut
    bonjour,
    Est-ce que quelqu'un a une autre idée pour accélérer encore le traitement ?
    si ils ne le sont pas déjà, indexer les champs utilisés dans les clauses WHERE (ex: DateDeb, DateEffet)
    Quand on est derrière l'écran on n'a aucun clavier sous les mains ...
    ah non ? donc devant l'écran c'est la connectique ?

  5. #5
    Membre émérite
    Homme Profil pro
    Formateur et développeur bureautique
    Inscrit en
    Mars 2007
    Messages
    1 410
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Formateur et développeur bureautique
    Secteur : Conseil

    Informations forums :
    Inscription : Mars 2007
    Messages : 1 410
    Points : 2 871
    Points
    2 871
    Par défaut
    Bonjour tee_grandbois

    J'avais oublié de le préciser, mais ces champs sont bien indexés. Merci quand même pour la suggestion.

    Une autre idée pour accélérer le traitement ?

    Bon après-midi

    Pierre Dumas
    Merci de cliquer sur pour chaque message ayant aidé puis sur pour clore cette discussion.

  6. #6
    Expert éminent sénior
    Avatar de tee_grandbois
    Homme Profil pro
    retraité
    Inscrit en
    Novembre 2004
    Messages
    8 641
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 67
    Localisation : France, Yvelines (Île de France)

    Informations professionnelles :
    Activité : retraité

    Informations forums :
    Inscription : Novembre 2004
    Messages : 8 641
    Points : 14 616
    Points
    14 616
    Par défaut
    Citation Envoyé par Pierre Dumas Voir le message
    J'avais oublié de le préciser, mais ces champs sont bien indexés.
    est-ce aussi le cas de la clé étrangère IdPersonne des 2 tables ?
    Après ça, j'arrive au bout des suggestions ...
    Quand on est derrière l'écran on n'a aucun clavier sous les mains ...
    ah non ? donc devant l'écran c'est la connectique ?

  7. #7
    Membre émérite
    Homme Profil pro
    Formateur et développeur bureautique
    Inscrit en
    Mars 2007
    Messages
    1 410
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Formateur et développeur bureautique
    Secteur : Conseil

    Informations forums :
    Inscription : Mars 2007
    Messages : 1 410
    Points : 2 871
    Points
    2 871
    Par défaut
    Oui, les clés étrangères sont bien indexées sans doublons.

    Cela m'a permis de le vérifier.

    Une idée sur la construction de la requête ?
    J'ai essayé de le faire avec deux ou trois requêtes imbriquées ou avec des liaisons <=, mais je n'y suis pas arrivé.

    Pierre Dumas
    Merci de cliquer sur pour chaque message ayant aidé puis sur pour clore cette discussion.

  8. #8
    Rédacteur/Modérateur

    Avatar de User
    Homme Profil pro
    Développeur informatique
    Inscrit en
    Août 2004
    Messages
    8 260
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : France, Ain (Rhône Alpes)

    Informations professionnelles :
    Activité : Développeur informatique

    Informations forums :
    Inscription : Août 2004
    Messages : 8 260
    Points : 19 423
    Points
    19 423
    Billets dans le blog
    63
    Par défaut
    Peut être avec TOP 1 :

    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    SELECT TOP 1 TPlanning.IdPlanning, TPlanning.IdPersonne, TPlanning.DateDeb, TContrats.IdTypeContrat, TContrats.HoraireJ
    FROM TContrats INNER JOIN TPlanning ON TContrats.IdPersonne = TPlanning.IdPersonne
    WHERE TContrats.DateEffet<=TPlanning.DateDeb
    ORDER BY TContrats.DateEffet Desc;
    Vous trouverez dans la FAQ, les sources ou les tutoriels, de l'information accessible au plus grand nombre, plein de bonnes choses à consulter sans modération

    Des tutoriels pour apprendre à créer des formulaires de planning dans vos applications Access :
    Gestion sur un planning des présences et des absences des employés
    Gestion des rendez-vous sur un calendrier mensuel


    Importer un fichier JSON dans une base de données Access :
    Import Fichier JSON

  9. #9
    Membre émérite
    Homme Profil pro
    Formateur et développeur bureautique
    Inscrit en
    Mars 2007
    Messages
    1 410
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Formateur et développeur bureautique
    Secteur : Conseil

    Informations forums :
    Inscription : Mars 2007
    Messages : 1 410
    Points : 2 871
    Points
    2 871
    Par défaut
    Cela ne fonctionne pas.

    En effet, cela renvoie pour la DateEffet la plus récente dans TContrats et dont au moins un enregistrement dans TPlanning a une DateDebut au moins égale, tous les enregistrements de la Tplanning dont la DateDebut est postérieure.

    Bref, en moins de mots : ce n'est pas bon

    Je vous remercie vraiment de passer du temps à chercher.

    Cela faisait longtemps qu'elle me turlupinait et que je n'y arrivait pas.

    Bonne fin de journée

    Pierre Dumas
    Merci de cliquer sur pour chaque message ayant aidé puis sur pour clore cette discussion.

  10. #10
    Rédacteur/Modérateur

    Avatar de User
    Homme Profil pro
    Développeur informatique
    Inscrit en
    Août 2004
    Messages
    8 260
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : France, Ain (Rhône Alpes)

    Informations professionnelles :
    Activité : Développeur informatique

    Informations forums :
    Inscription : Août 2004
    Messages : 8 260
    Points : 19 423
    Points
    19 423
    Billets dans le blog
    63
    Par défaut
    Ah oui en effet, en fait je pensais filtrer la requête sur un formulaire avec un paramètre ..

    Sinon, pour ne pas revoir la conception :

    Ajouter un champ IdContrat dans la table TPlanning et le mettre à jour à l'aide d'une requête update :

    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    UPDATE TContrats INNER JOIN TPlanning ON TContrats.IdPersonne = TPlanning.IdPersonne SET TPlanning.IdContrat = TContrats.IdContrat
    WHERE (TPlanning.IdContrat Is Null) and TContrats.DateEffet = (SELECT Max(TC1.DateEffet) AS MaxDeDateEffet
                                                                   FROM TContrats as TC1
                                                                   WHERE (TC1.DateEffet<=TPlanning.DateDeb) and (TC1.IdPersonne=TPlanning.IdPersonne))

    Pour ensuite faire un lien direct dans ta requête entre TPlanning et TContrats sur le champ IdContrat.

    Après il faut voir quand exécuter l'update..
    Vous trouverez dans la FAQ, les sources ou les tutoriels, de l'information accessible au plus grand nombre, plein de bonnes choses à consulter sans modération

    Des tutoriels pour apprendre à créer des formulaires de planning dans vos applications Access :
    Gestion sur un planning des présences et des absences des employés
    Gestion des rendez-vous sur un calendrier mensuel


    Importer un fichier JSON dans une base de données Access :
    Import Fichier JSON

  11. #11
    Membre émérite
    Homme Profil pro
    Formateur et développeur bureautique
    Inscrit en
    Mars 2007
    Messages
    1 410
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Formateur et développeur bureautique
    Secteur : Conseil

    Informations forums :
    Inscription : Mars 2007
    Messages : 1 410
    Points : 2 871
    Points
    2 871
    Par défaut
    Bonjour

    C'est effectivement une très bonne idée pour accélérer le traitement car cela deviendrait instantané.

    Cependant, il faudrait remplir ce champ IdContrat dans la TPlanning à chaque saisie (et à chaque modification). Et là, cela demande pas mal de réflexion en plus.

    Je crois que l'on va en rester là s'il n'y a pas d'autres idées des contributeurs.

    Merci en tout cas pour l'optimisation dans le premier post.

    Bonne journée

    Pierre Dumas
    Merci de cliquer sur pour chaque message ayant aidé puis sur pour clore cette discussion.

  12. #12
    Rédacteur/Modérateur

    Avatar de User
    Homme Profil pro
    Développeur informatique
    Inscrit en
    Août 2004
    Messages
    8 260
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : France, Ain (Rhône Alpes)

    Informations professionnelles :
    Activité : Développeur informatique

    Informations forums :
    Inscription : Août 2004
    Messages : 8 260
    Points : 19 423
    Points
    19 423
    Billets dans le blog
    63
    Par défaut
    Bonjour,

    Citation Envoyé par Pierre Dumas Voir le message
    ...
    Cependant, il faudrait remplir ce champ IdContrat dans la TPlanning à chaque saisie (et à chaque modification). Et là, cela demande pas mal de réflexion en plus.
    Idéalement il faudrait pouvoir exécuter cette requête update juste avant d'ouvrir ta requête sélection et voir si ça prend du temps.
    Vous trouverez dans la FAQ, les sources ou les tutoriels, de l'information accessible au plus grand nombre, plein de bonnes choses à consulter sans modération

    Des tutoriels pour apprendre à créer des formulaires de planning dans vos applications Access :
    Gestion sur un planning des présences et des absences des employés
    Gestion des rendez-vous sur un calendrier mensuel


    Importer un fichier JSON dans une base de données Access :
    Import Fichier JSON

  13. #13
    Membre émérite
    Homme Profil pro
    Formateur et développeur bureautique
    Inscrit en
    Mars 2007
    Messages
    1 410
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : Formateur et développeur bureautique
    Secteur : Conseil

    Informations forums :
    Inscription : Mars 2007
    Messages : 1 410
    Points : 2 871
    Points
    2 871
    Par défaut
    Et merci à vous pour m'avoir aidé.



    Bonne fin de journée

    Pierre Dumas
    Merci de cliquer sur pour chaque message ayant aidé puis sur pour clore cette discussion.

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. optimisation d'une vue avec plusieurs sous-requêtes
    Par jean62 dans le forum Développement
    Réponses: 7
    Dernier message: 08/08/2012, 15h29
  2. Réponses: 4
    Dernier message: 29/07/2009, 14h19
  3. Débutant: UPDATE d'une table avec sous-requête ?
    Par ctobini dans le forum Débuter
    Réponses: 2
    Dernier message: 03/10/2007, 11h45
  4. Optimisation d'une base avec des tables liés
    Par snoopy69 dans le forum Access
    Réponses: 2
    Dernier message: 28/04/2006, 09h11
  5. optimisation d'une requête avec jointure
    Par champijulie dans le forum PostgreSQL
    Réponses: 8
    Dernier message: 07/07/2005, 09h45

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo