Analyze and improve performance of queries based on user access logic

[StefanKock]

Situation Description & Motivation

• Performance of select queries is too bad because auf complicated conditions for user access and the joins related to that.
• Sometimes there are 1+n query patterns that are very inperformant.

Related: #7372

Tasks

directories and general improvements

• [x] #7719
• [x] #8778
• [x] #8357
• [x] #8610
• [x] #8637
  • [x] #8748
  • [x] #8688
  • [x] #8747
• [x] #9559
• [x] #9753
  • [x] #9779
• [x] Immunizations directory slow: 24s -> 60s (1.74.-SN); retested with NatUser on 2022-07-04: ~3s for count, ~ 17s for getIndexList; in total less than 26s: So spike from automatic test not reproducible.
• [x] #9716
• [ ] #10208
• [ ] #10214
• [ ] #8938 -> collect ideas on what else we can improve. E.g. views for archived entities (SQL POC)
• [ ] #9569
• [x] #3845
• [ ] Investigate whether the task directory (and maybe others) has unnecessary count queries

dashboard

• [x] #8704
• [ ] #9528

search and merge

• [x] #8697
• [x] #3915
• [ ] #9053
• [ ] #9054
• [ ] #8615

import and export

• [x] #8609
• [x] #9081
• [x] #9193

unsorted

• [x] #4417
• [ ] #8611
• [ ] #8935

minor/optional

• [ ] #10210

Alternatives

Risks

More data over time will worsen some performance problems much more (not linear but exponential),

[StefanKock]

I categorized the linked tickets to have a better overview.

[StefanKock]

Follow-up for #8938 discussed with @syntakker: create 2 tickets

---

Two step getIndexList for CoreAdos -> #11013

Architecture of getIndexList

 - getIndexListIds
   - sortBy
 - batching (fetch data)
   - sortBy
 - post-processing (calculated values, collections)
 - pseudonymization

• Improve following CoreAdo: Case, Contact, Event, EventParticipant, TravelEntry, Immunization (6)
• Performance before/after
• Extract generic pattern to fetch Ids, sortBy or something else that is repeated code?

---

Two step getIndexList for other entities -> #11014

• Improve others: Task, Sample, ExternalMessageIndexDto, ShareRequestIndexDto (4)
• Performance before/after