I explored the poor performance of the ID search SQL with larger datasets (e.g. 15k or more). We originally thought it was due to the newish "original order" sorting feature, or maybe because we're pulling the dataset over the db link. But avoiding those does not fix the performance The fix is to remove the wildcard translation. The change below reduces a 60-second query to 2 seconds. I'm not exactly sure what it provides- looks like some additional whitespace protection and so users can use a * wildcard on IDs. But not sure that's useful given our site search / solr search.

@aurreco-uga has a plan to bring this solution to UX the next time we have hanging queries that impact the sites. Along with the "we shouldn't need this with the new SOLR search" argument, this will hopefully exert enough pressure to persuade them to go with this solution. Until then, it sits.

diff --git a/Model/lib/wdk/model/questions/queries/geneQueries.xml b/Model/lib/wdk/model/questions/queries/geneQueries.xml
index ff86fda0f..cbcfb6876 100644
--- a/Model/lib/wdk/model/questions/queries/geneQueries.xml
+++ b/Model/lib/wdk/model/questions/queries/geneQueries.xml
@@ -1051,7 +1051,7 @@ and ps.na_feature_id in ($$phenotypeScoreFileterParam$$)
             (
               SELECT ds.gene_source_id, gi.gene, ds.dataset_value_order
               FROM ApidbTuning.GeneId gi, ( $$ds_gene_ids$$ ) ds
-              WHERE LOWER(gi.id) LIKE REGEXP_REPLACE(REPLACE(LOWER(ds.gene_source_id), '*', '%'), '[[:space:]]', '')
+              WHERE LOWER(gi.id) = LOWER(ds.gene_source_id)
             ) t
         WHERE t.gene = ta.gene_source_id
         GROUP BY ta.gene_source_id, ta.source_id, ta.project_id

Sep 9 2022
i did some testing in my vectorbase (durian): (had to comment out in ListDatasetParser.java lines 78-81 to allow an rtf file)
1- the times using regex and replace, or only replace or nothing, using an rtf original file with 1200 genes:
20mn with rgex and replace, 4mn with only replace and 17 sec with nothing.
i should probably try with a larger txt file..
2- about dataset values stored with a space in the middle of the value (we saw we dont have cases with trailing or leading spaces), example:
SQL> select data1 from userlogins5.dataset_values where dataset_id = 220700633;
AAEL000551, AAEL007948, AAEL008489
i tested an input with 3 iDs separated by comma and space and it works well so no idea how that happened..

sep 8, 2022: i committed in prod and master removing the regex_replace space and teh replace wildcard.
207c21b

reason to remove space:
no cases in database of a space coming with an ID (leading , trailing or in the middle).

reasons to remove wildcard:
1---wildcard is very slow, and breaks the system:
-TIMES are CACHING TABLE SQL (maybe post caching included?):
(loading into userdb and attributes table both under the mn.. 30 sec avg each)
-tested in my vectorbase (durian) using N databases:
(had to comment out in [ListDatasetParser.java] lines 78-81 to allow an rtf file)

1.a) using an rtf original file with 1200 genes:
with no regex replace: about 17 sec
with wildcard only: about 4 mn
with wildcard and space replace: about 20 min
1.b) 14K genes (tab delimited file obtained from a genes result)
with no regex replace: about 40sec
with wildcard and space replace: about 6mn

2---is hardly used anymore, now that we have ss
about wildcard use it does seem it has decreased
2018: 27312
2019: 1336
2020: 5122
2021: 598
2022: 177