CODEWITHSUNDEEP
sqlruby-on-rails-3performance
ErwinM
ErwinM

Reputation: 5131

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

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?

Upvotes: 5

Views: 316

Answers (6)

Vijay Hulmani
Vijay Hulmani

Reputation: 989

As I dont understand ruby on rails,from sql point of view i suggest you to write a stored procedure and return a dataset.And do the necessary table operations on the dataset from front end.It will reduce the unnecessary calls to DB.

Upvotes: 1

Tom Fakes
Tom Fakes

Reputation: 955

There's not really enough information in your question to know exactly what you're trying to achieve here, e.g. the code snippet doesn't make use of the returned database query, so you could just remove it to make it faster. Perhaps this is just a bug in the code you posted?

Having said that, there are some techniques you should look into to implement your functionality.

I would take a look at using data warehouse techniques. I would think of your 'availability information' as a Fact table in a star schema, with 'Dates' and 'People' as Dimension tables.

You can then use queries to get stuff like - list of users for this projects for this week, and their availability.

Data warehousing has a whole bunch of resources you can tap into to help make this perform well, but there's also a lot of terminology that can be confusing, but for this type of 'I need to slice and dice my data across several sets of things (people and time)', Data Warehousing techniques can be quite powerful.

Upvotes: 1

Constantine Kuznetsov
Constantine Kuznetsov

Reputation: 187

see what query is executed. further you may make comand explain to your query

explain select * from  project where start_date < any_date and end_date> any_date2

you see the plan of query . Use this plan to optimized your query. for example : if you have index using field end_date replace a condition(end_date> any_date2 and start_date < any_date) . this step will using index if you have index on this field. But it step is db dependent . example is for nysql. if you want use index in mysql you must have using index condition on left part of where

Upvotes: 1

Sigar Dave
Sigar Dave

Reputation: 2654

USE Stored procedure to fire your query to SQL to get data.

Pass paramerts in your case it is today's date to the SQl query.

Apply your conditions and Logic in the SQL Stored procedure , Using procedure is the goood and fastest way to retrieve data from the SQL , also it will prevent your code from the SQL injection too.

Call that SP from your Code as i dont know the Ruby on raisl I cant provide you steps about how to Call the Stored procedure from it.

After that the data fdetched as per you stored procedure will be available in Data table or something like that.

After getting the data you can perform all you need

Hope this helps

Upvotes: 1

refactorthis
refactorthis

Reputation: 1893

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.

Upvotes: 3

CityCollegeSouthampton
CityCollegeSouthampton

Reputation: 120

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.

Upvotes: 2

Related Questions