0

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?

queroga_vqz
  • 1,031
  • 3
  • 11
  • 25
  • How often is that dashboard called? How often do you need the data to update? How many people are interested in these reports? Do you need realtime info or would it be okay to just update the reports once per hour/day? Did you consider having a decicated data warehouse for business intelligence purposes? – spickermann Jan 24 '20 at 08:19
  • @spickermann I though about cache, to deal with this, but unfortunately, I need realtime info, the dashboard (and the heavy query is called) every time the user hits filter, on the view. Yeah, a time series DB would defintely be ideal – queroga_vqz Jan 24 '20 at 12:40
  • Is the query heavy? Or is the `.map` and record instantiation heavy? – jvillian Jan 24 '20 at 18:34

1 Answers1

0

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

Bafraiki
  • 38
  • 6