Storing result set for later fetch

Question

I have some queries that run for a quite long (20-30 minutes). If a lot of queries are started simultaneously, connection pool is drained quickly.

Is it possible to wrap the long-running query into a statement (procedure) that will store the result of a generic query into a temp table, terminanting the connection, and fetchin (polling) the results later on demand?

EDIT: queries and data stuctures are optimized, and tips like 'check your indices and execution plan' don't work for me. I'm looking for a way to store [maybe a] byte presentation of a generic result set, for later retreive.

Answer 1

First of all, 20-30 minutes is an extremely long time for a query - are you sure you aren't missing any indexes for the query? Do check your execution plan - you could get a huge performance gain from a well-placed index.

In MySQL, you could do

INSERT INTO `cached_result_table` (
    SELECT your_query_here
)

(of course, cached_result_table needs to have the exact same column structure as your SELECT returns, otherwise you'll get an error).

Then, you could query these cached results (instead of the original tables), and only run the above query from time to time - to update the cached_result_table.

Of course, the query will need to run at least once initially, which will take the 20-30 minutes you mentioned. I suggest to pre-populate the cached table before the data are requested, and keep some locking mechanism to prevent the update query to run several times simultaneously. Pseudocode:

init:
insert select your_big_query

work:
if your_big_query cached table is empty or nearing expiration:
  refresh in the background:
     check flag to see if there's another "refresh" process running
     if yes
       end // don't run two your_big_queries at the same time
     else 
       set flag
       re-run your_big_query, save to cached table
       clear flag
serve data to clients always from cached table