[PERFORMANCE] Opening the samples leads to not acceptable loading times

[markusmann-vg]

Feature Description

As a user with the role clinician or case office the loading times of the samples should be reasonable.

Problem Description

In some German health departments the loading times for opening the samples directorytakes way too long. The health departments are reporting loading times up to 2,5 minutes.
The Long Query Logs can be found here

Additional info

It seems that for samples the problem occurs with Surveillance Officer, Clinician, contact supervisor and the importing user

Possible Alternatives

Additional Information

[MartinWahnschaffe]

See #8637

[StefanKock]

I checked the SQL of SampleFacade.getIndexList and it shows known patterns of not reused joins for jurisdiction checks and user filters.

...
LEFT OUTER JOIN Contact contact5_ ON sample0_.associatedContact_id=contact5_.id
LEFT OUTER JOIN District district6_ ON contact5_.district_id=district6_.id
LEFT OUTER JOIN cases case7_ ON contact5_.caze_id=case7_.id
LEFT OUTER JOIN District district8_ ON case7_.district_id=district8_.id
LEFT OUTER JOIN Person person9_ ON contact5_.person_id=person9_.id
LEFT OUTER JOIN cases case10_ ON contact5_.caze_id=case10_.id  -- <- duplicates case7_ (jurisdiction)
LEFT OUTER JOIN cases case11_ ON contact5_.caze_id=case11_.id  -- <- duplicates case7_ (jurisdiction)
LEFT OUTER JOIN cases case12_ ON contact5_.caze_id=case12_.id  -- <- duplicates case7_ (jurisdiction)
LEFT OUTER JOIN cases case13_ ON contact5_.caze_id=case13_.id  -- <- duplicates case7_ (user filter)
LEFT OUTER JOIN Contact contacts14_ ON case13_.id=contacts14_.caze_id
LEFT OUTER JOIN EventParticipant eventparti15_ ON sample0_.associatedEventParticipant_id=eventparti15_.id
LEFT OUTER JOIN EVENTS event16_ ON eventparti15_.event_id=event16_.id
LEFT OUTER JOIN LOCATION location17_ ON event16_.eventLocation_id=location17_.id
LEFT OUTER JOIN District district18_ ON location17_.district_id=district18_.id
LEFT OUTER JOIN Person person19_ ON eventparti15_.person_id=person19_.id
LEFT OUTER JOIN EVENTS event20_ ON eventparti15_.event_id=event20_.id  -- <- duplicates event16_ (jurisdiction)
LEFT OUTER JOIN LOCATION location21_ ON event20_.eventLocation_id=location21_.id
LEFT OUTER JOIN EVENTS event22_ ON eventparti15_.event_id=event22_.id  -- <- duplicates event16_ (jurisdiction)
LEFT OUTER JOIN LOCATION location23_ ON event22_.eventLocation_id=location23_.id
LEFT OUTER JOIN EVENTS event24_ ON eventparti15_.event_id=event24_.id  -- <- duplicates event16_ (user filter)
LEFT OUTER JOIN LOCATION location25_ ON event24_.eventLocation_id=location25_.id
LEFT OUTER JOIN users user26_ ON event24_.reportingUser_id=user26_.id  -- <- not needed join, fixed with #8637
LEFT OUTER JOIN users user27_ ON event24_.responsibleUser_id=user27_.id  -- <- not needed join, fixed with #8637
LEFT OUTER JOIN cases case28_ ON eventparti15_.resultingCase_id=case28_.id
LEFT OUTER JOIN Contact contacts29_ ON case28_.id=contacts29_.caze_id
LEFT OUTER JOIN Facility facility30_ ON sample0_.lab_id=facility30_.id
LEFT OUTER JOIN samples sample31_ ON sample0_.referredTo_id=sample31_.id
LEFT OUTER JOIN users user32_ ON sample0_.reportingUser_id=user32_.id  -- <- not needed join, could be changed in SampleJurisdictionPredicateValidator

SampleFacade.getIndexList BEFORE.sql.txt

[StefanKock]

When #8688 and #8747 are done, I currently only see a minor change to get rid of a user join LEFT OUTER JOIN users user32_ ON sample0_.reportingUser_id=user32_.id

[vidi42]

Waiting for #8688 and #8747

[StefanKock]

With previous tickets and small changes on SampleJurisdictionPredicateValidator the joins are now significantly reduced.

...
LEFT OUTER JOIN contact contact5_ ON sample0_.associatedContact_id=contact5_.id
LEFT OUTER JOIN District district6_ ON contact5_.district_id=district6_.id
LEFT OUTER JOIN cases case7_ ON contact5_.caze_id=case7_.id
LEFT OUTER JOIN District district8_ ON case7_.district_id=district8_.id
LEFT OUTER JOIN contact contacts9_ ON case7_.id=contacts9_.caze_id
LEFT OUTER JOIN Person person10_ ON contact5_.person_id=person10_.id
LEFT OUTER JOIN EventParticipant eventparti11_ ON sample0_.associatedEventParticipant_id=eventparti11_.id
LEFT OUTER JOIN EVENTS event12_ ON eventparti11_.event_id=event12_.id
LEFT OUTER JOIN LOCATION location13_ ON event12_.eventLocation_id=location13_.id
LEFT OUTER JOIN District district14_ ON location13_.district_id=district14_.id
LEFT OUTER JOIN EventParticipant eventperso15_ ON event12_.id=eventperso15_.event_id
LEFT OUTER JOIN cases case16_ ON eventperso15_.resultingCase_id=case16_.id
LEFT OUTER JOIN contact contacts17_ ON case16_.id=contacts17_.caze_id
LEFT OUTER JOIN Person person18_ ON eventparti11_.person_id=person18_.id
LEFT OUTER JOIN Facility facility19_ ON sample0_.lab_id=facility19_.id
LEFT OUTER JOIN samples sample20_ ON sample0_.referredTo_id=sample20_.id
...

SampleFacade.getIndexList AFTER.sql.txt

But I found another flaw in the logic: An afterwards fetch for data of last pathogen test results (SampleService.getIndexList ~ line 329) uses an IN clause with sample uuids that is not batched. It might be inperformant and will hit the 32k parameter limit of PostgreSQL. Update on finding: Since the initial query is already batched (usually with 100 entries), the parameter limit is not a concern. I also checked the resulting query with explain analyze and it fetched first the relevant samples by uuid, then joined the corresponding pathogen tests by Sample.id - I see nothing concerning here.

Pathogen test attributes query:

SELECT pathogente0_.testType AS col_0_0_,
       pathogente0_.cqValue AS col_1_0_,
       sample1_.uuid AS col_2_0_
FROM PathogenTest pathogente0_
CROSS JOIN samples sample1_
WHERE pathogente0_.sample_id=sample1_.id
  AND pathogente0_.deleted=FALSE
  AND (sample1_.uuid in ('WU3KU2-2T636F-RARIE4-XLRA2O3U'))
ORDER BY pathogente0_.changeDate DESC