Best way to approach a heavy SQL query on a dashboard view in rails

Question

I Have this dashboard(report) which basically performs a heavy sql query and display its results on a table grid, pretty standard stuff.

However, sometimes, the query is way to heavy, due to large volume of data and sql complexity, and the browser just hangs until the user gets an error.

I have no n+1 queries, and tried adding pagination, but I am still facing this issue.

What would be the best approach on this? Using a background job?

Is there a way to perform this query on the background, to prevent the request from failing?

I tried implementing a sidekiq worker, but I am not sure if I can get the results back from the worker, after the job is completed.

@results = ActiveRecord::Base
   .connection
   .select_all(query)
   .map do |record|
    Hashie::Mash.new(record)
   end

query contains the SQL query on a string.

Any insights?

Answer 1

worker are asyncs. I don't know if you need to here this but because of this. worker and your mains process ( the server ) are not on the same thread so they can't really communicate

But you can find a way to process it in the same worker, or even write the result in a file that can be lookup after, or send it thought a request at the same server on the main thread ( but because of heavy data it might slowy ) etc .. .

Maybe a cache server like redis can help to store it temporaly and send a request to main thread telling him that there is something to look at on Redis server