Search by candidate number has become unworkably slow

[camerojo]

Naji has stopped using it - and found other ways to locate candidate by candidate numbers. It used to be quick.

Actually Postgres is pretty quick doing wild card searches on an indexed field - see below. It is possible that doing candidate number search by Elastic search will not improve performance.

For example doing the following Postgres query on the production database from Intellij completes in about 0.25 of a second.

select candidate_number from candidate where candidate_number like '123%' limit 10;

Searching for 123 on the TC takes just under 6 seconds. Of course there will be extra for the HTTP requests to complete - but it seems unlikely that would add that much delay.

[camerojo]

The problem doesn't appear to be with the wild card fetch (see the stats in the introduction above).

By turning on JPA logging you can see that it relates to the number of database accesses related to the results of the search.

To do the search there are just the few database accesses you would expect.

However, the full candidate details - including all one to many associated objects are returned by the DTO builder. The same as you would need to display full details for each candidate - whereas all that is really needed is the candidate number and name.

Instead of just a few database accesses, there are over a hundred database accesses needed to retrieve full candidate data for each of the search results.

Note that an Elastic search has exactly the same issue.

The solution is just to return the data needed to display the results.

• [x] Reinstate Postgres wildcard candidate number search (from Elastic version). This does not appear to be the performance issue.
• [x] Add minimal candidate DTO for all candidate searches based on partial data - this looks like it will fix the performance problems.