How could i write this code in a more performant way?

Question

In our app people have 1 or multiple projects. These projects have a start and an end date. People have a limited amount of available days.

Now we have a page that displays the availability of a given person on a week by week basis. It currently shows 18 weeks.

The way we currently calculate the available time for a given week is like this:

def days_available(query_date=Date.today)
  days_engaged = projects.current.where("start_date < ? AND finish_date > ?",     query_date, query_date).sum(:days_on_project)
  available = days_total - hours_engaged
end

This means that to display the page descibed above the app will fire 18(!) queries into the database. We have pages that lists the availability of multiple people in a table. For these pages the amount of queries is quickly becomes staggering.

It is also quite slow.

How could we handle the availability retrieval in a more performant manner?

Answer 1

This is quite a common scenario when working with date ranges in an entity. Easy and fastest way is in SQL:

Join your events to a number generated date table (see generate days from date range) so that you have a row for each day a person or people are occupied. Once you have the data in this form it is simply a matter of grouping by the week date part of the date and counting the rows per grouping.

You can extend this to group by person for multiple person queries.

Answer 2

From a SQL point of view, I'd advise using a stored procedure and pass in your date/range requirement, you can then return a recordset for a user or possibly multiple users. This way your code just has to access db once.

You can then output recordset data in one go, by iterating through.

Hope this helps.