Discussion :

[Pierre Dumas]

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 :


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

[User]

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;

[Pierre Dumas]

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

[tee_grandbois]

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)

[Pierre Dumas]

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

[tee_grandbois]

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 ...

[Pierre Dumas]

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

[User]

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;

[Pierre Dumas]

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

[User]

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..

[Pierre Dumas]

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

[User]

Bonjour,

...
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.

[Pierre Dumas]

Et merci à vous pour m'avoir aidé.



Bonne fin de journée

Pierre Dumas