Performance testing: GP activation filtering

[suzannehamilton]

Investigate the impact on ehrQL job duration if we implement the more complex version of GP activation filtering.

This probably means implementing version 1b as described here, but we should double-check with Will and Catherine in case there is another proposal which sounds more likely. Alternatively, we could implement the most complex version that has been discussed, to try to get an upper bound on performance issues.

We will have to implement the filtering for all GP tables. See #2602 for the spike we've done on a single table.

For performance testing, we can run jobs on the secure environment, so that we're testing against the real backend. We have a way of running jobs in a testing mode which does not overwrite existing outputs (@evansd can you explain or post a link about this?) We should run each job before and after the changes, rather than comparing the job duration to the last time the study code was run.

Decisions:

• Exactly which version should we implement in this spike?
• Which study repos should we use for performance testing?

[evansd]

We can run ehrQL jobs outside of the pipeline using the run-debug.sh script. The help output of that script explains the basic mechanics.

The assumption is that you've got a clone of the ehrQL repo in your home directory inside the secure environment with the branch under test checked out. And also a clone of the relevant study repo, usually with a specific commit checked out.

I think everyone who has sufficient access to do this has done this at least once before.

[evansd]

I've confirmed that the following expression works on the database server and returns the appropriate proportion of practices (replacing 0.75 with whatever is the target proportion):

CASE
  WHEN
    CONVERT(TINYINT, SUBSTRING(HASHBYTES('SHA1', CONVERT(VARBINARY(8), Organisation_ID)), 1, 1))
    <=
    0.75 * 255
  THEN 1
  ELSE 0
END AS DirectionsAcknowledged