CODEWITHSUNDEEP
ruby-on-railsperformancepostgresqlcachingruby-on-rails-4
Don P
Don P

Reputation: 63678

Improve performance for a SQL query that returns the same result for 24 hours

In my Rails app, I only do 10 unique SQL queries. However, each takes a long time (1 - 5 seconds). The result of each query only changes every 24 hours.

What solutions are available to make this more performant?

I'm using Postgres and Ruby on Rails.

Should I be caching query results somewhere? Should I be using "stored procedures" (don't know what they are yet)? Is there something else I should be researching?

Upvotes: 2

Views: 273

Answers (3)

Terry Carmen
Terry Carmen

Reputation: 3886

Generate a static HTML or XML document from the query and serve that.

One a day, generate a new static document.

This has the lowest possible overhead.

Upvotes: 0

evanbikes
evanbikes

Reputation: 4171

I would just use Rails.cache like so:

def self.some_long_query_that_changes_every_day
  Rails.cache.fetch ['some_cache_key'], expires_in: 12.hours do
    self.where(field: value)
  end
end

fetch will either read from the unexpired cache or make the db query and store it in the cache with that key.

The only tricky part is the expires_in part. Depending on when exactly your data changes, you might want to compute the expires_in depending on what time it gets stored.

expire_in = Time.now.utc.end_of_day - Time.now.utc
Rails.cache.fetch ['some_cache_key'], expires_in: expire_in....

Or something similar.

Upvotes: 2

Simo Kivistö
Simo Kivistö

Reputation: 4473

If you are using at least version 9.3 then maybe Materialized views? That way you have the stored results in the table-like view and you can refresh the results every 24h with the command: REFRESH MATERIALIZED VIEW.

If your query returns a lot of rows you could consider some other options (caching the results within the program etc.).

Upvotes: 1

Related Questions