This is in preparation for using the new VDI data schema.

Here are all of the references: https://github.com/search?q=repo%3AVEuPathDB%2FApiCommonModel%20apidbuserdatasets&type=code

(this issue was triggered by the connection timeout in VB on July 25-26 , seems the cause was a user running geneId slow searches, each trying to create a new cache table and some got stuck in oracle)

This issue has been co-opted from the description below to instead rework how dataset param data is imported into a WDK cache table. Reading data over the DB link as a subquery has been shown to be consistently slow and exponentially slower as data size grows. Thus, maybe we can get a speed-up if we create a temp table in appDb containing the dataset_values rows for a particular dataset and use, as the dataset param's internal value, a select * from the_tmp_table. This would not be hard to set up (would just need to build the table when asked for the dataset param's internal value), but we will have to add a mechanism to tear the table down when complete. Steve suggested reusing tables (one per dataset_id) but this would require additional concurrency logic akin to the WDK cache. Or we could just let them sit out there and add their deletion to the wdkCache clearing logic.

First step, however is to confirm that ID query's ID sql is significantly faster when reading from a tmp table in appDb than it is when reading from dataset_values over the db link AND make sure that create table as (select from dataset_values) is not so slow that it offsets any performance gains. In short, check to make sure creating a tmp table from select over db_link + reading from tmp table is significantly faster than selecting directly over db_link.