Issue 55: Use a explicit Query for fetching the Submission Type list.

[kaladay]

Relates #55

The test data consisted of randomly generated data with:

• 44075 submissions.
• 1458591 field values.

The submission list page took ~3.5 minutes to load the Submission Type list data. Switching to an explicit query, the submission list page took ~320 milliseconds to load the Submission Type list data.

This is an overwhelming clear improvement that shows that the use of Spring+JPA internals are highly inefficient for the Field Values and related data. For the filters, the actual model does not need to be loaded and only the value.

The use of a JDBC query is chosen after doing tests against Postgresql and H2. The use of a native query via @Query worked for Postgresql but H2 used clob2 and failed to convert the results into strings. Using a literal query is chosen over a JPA query because the JPA wants to understand the model structure. The Field Predicate is not loaded immediately available for JPA for the FieldValue JPA Repository.

Only very basic SQL is used in the query and so there should be very little SQL compatibility risks. The biggest risk is that of the table naming structure. This structure appears to be sufficiently consistent between Postgresql and H2 in this regard.

Spring+JPA Performance:

JDBC Performance:

[kaladay]

I have been able to confirm that this works in MySQL/MariaDB.

To get MySQL/MariaDB working, I had to alter src/main/java/org/tdl/vireo/model/Organization.java, changing:

    @ElementCollection(fetch = EAGER)
     private List<String> emails;

Into

    @LazyCollection(LazyCollectionOption.FALSE)
    @ElementCollection
     private List<String> emails;