Poor performance when queries on some lists which contains some bad KVPs, and failed to delete those lists

[qifeng-bai]

https://lists-test.ala.org.au/speciesListItem/list/dr7485

This list put a large number of Latitudes and Longitudes as a KV pair into KVP table,

When we fired a GroupBy query on its KVP and generated >62000 records. The query took > 18 seconds on MySQL

select kvp.key, kvp.value, kvp.vocabValue, count(sli) as cnt from SpeciesListItem as sli ' +
                    'join sli.kvpValues as kvp where sli.dataResourceUid = :dataResourceUid ' +
                    "${q ? 'and (sli.matchedName like :matchedName or sli.commonName like :commonName or sli.rawScientificName like :rawScientificName) ' : ''} " +
                    'group by kvp.key, kvp.value, kvp.vocabValue, kvp.itemOrder order by kvp.itemOrder, kvp.key, cnt desc

Issues on "Delete" method 1, The transactional, delete(flush) cannot work with the list which contains a large number of KVP records. "Delete this list" will hanging around till time out. https://github.com/AtlasOfLivingAustralia/specieslist-webapp/blob/develop/grails-app/controllers/au/org/ala/specieslist/SpeciesListController.groovy#L87

2, Inconsistent actions on delete" methods: The delete method on server will redirect to 'list' page after deletion: https://github.com/AtlasOfLivingAustralia/specieslist-webapp/blob/develop/grails-app/controllers/au/org/ala/specieslist/SpeciesListController.groovy#L87

However, the frontend , the JS tries to reload current page, which is , for example: which https://lists-test.ala.org.au/speciesListItem/list/dr7485. Since dr7585 has been deleted, it will return a warning that the list cannot be found.

https://github.com/AtlasOfLivingAustralia/specieslist-webapp/blob/develop/grails-app/views/speciesListItem/list.gsp#L228

[qifeng-bai]

After discussed with Peggy, those two bad records should be deleted

PR: #274 Fix deletion

Test:

Go to : https://lists-test.ala.org.au/admin/speciesLists?q=&max=25&sort=itemsCount&order=desc Delete "ALA_alc"

@peggynewman Can you please test the deletion function for me?

[yasima-csiro]

Test passed. Ala_alc was successfully deleted.