Optimize query in Rails

Question

I have the following code:

results = Report.where(:car => 'xxxx').group(:date, :name, :car).select('date, name ,car, info, MAX(price) AS max_price')
for customer in customers
  result = results.where(:date => customer.date, :name => customer.name, :car => customer.car).first
 .... rest of the code ....
end

I have a database with many records ~20,000, so I want to optimize the code and cache results in memory. Once again: my overall intention is make this code more efficient in terms of time. I want it to run faster than it is now and I want to reduce amount of database calls.

I am thinking of making my inital results object an array. I have a remote database so each .where query takes sometime. When I make results an array by adding .to_a - I load it to memory. So I think, it should be better(but not really sure)

Something like:

results = Report.where(:car => 'xxxx').group(:date, :name, :car)
                .select('date, name ,car, info, MAX(price) AS max_price')
                .to_a

for customer in customers
 result = results.select {|result| result.date == customer.date and result.name == customer.name and result.car == customer.car }
                 .first
end

"Will this work" seems like an odd question. Have you tried it? BTW your methodology does not do a whole lot it simply overwrites a local variable (result) n times (where n is the number of customers). So result is equivalent to the result of the last customer only. Please explain what your overall intention is because I do not think optimization is your issue here — engineersmnky
– engineersmnky, Commented Sep 21, 2017 at 15:19
Why can't you have customer_id in reports table which makes it easier for you to fetch all reports for given customers? — Surya
– Surya, Commented Sep 21, 2017 at 15:27
@Surya because I have a certain database structure which is not written by me and this structure might be not so good but I can't modify it. — user2950593
– user2950593, Commented Sep 21, 2017 at 15:29
My statement about your methodology still stands and I still implore you to "Please explain what your overall intention is because I do not think optimization is your issue here" — engineersmnky
– engineersmnky, Commented Sep 21, 2017 at 15:32

Surya · Accepted Answer · 2017-09-21 15:36:29Z

3

Well, the best things to have an association to fetch all reports for customers. In the case when you can not do so, I would recommend making only one query instead of n+1(as stated in the question) like this:

results = Report.where(:car => 'xxxx').group(:date, :name, :car)
                .select('date, name ,car, info, MAX(price) AS max_price')
                .where(:date => customers.map(&:date), :name => customers.map(&:name), :car => customers.map(&:car))

Assuming customers is an array of objects which respond to :name, :car, and :date methods.

One thing that should be noted is it does not guarantee that it will fetch reports of an exact customer. For that, you'd have to verify it by iterating through the results object yourself.

answered Sep 21, 2017 at 15:36

Surya

16k3 gold badges55 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user2950593 Over a year ago

So how do I rewrite this line: result = results.where(:date => customer.date, :name => customer.name, :car => customer.car).first with your code?

Surya Over a year ago

Just like in the last line on second block of code mentioned in your question:

customers.each do |customer| result = results.detect{ |result| result.date == customer.date and result.name == customer.name and result.car == customer.car }

user2950593 Over a year ago

Okay, right now as understand my code makes n calls to database. Where n is number of customers. Will modified code as you suggested make just one call?

Surya Over a year ago

Yes, except now you that in Ruby as there's no way to pinpoint exact customer's report in reports table. It will also limit the call to include only possible dataset instead of fetching all reports.

user2950593 Over a year ago

How did you find that with this line: .where(:date => customers.map(&:date), :name => customers.map(&:name), :car => customers.map(&:car)) code makes just one call to database?

jansha · Accepted Answer · 2024-06-26 09:34:01Z

-1

Also you can optimize the queies by utilizing pg_query_optimizer gem, this will internally perform database level query optimization ways to optimize the queries without manual involvement.

For more info https://rubygems.org/gems/pg_query_optimizer.

The only thing is You need to run the query inside that pg module class, like

 result = Yourmodel.pg_optimize do
            Report.where(:car => 'xxxx').group(:date, :name, :car).select('date, name ,car, info, MAX(price) AS max_price')
end

like this. Refer this documentaiton for more details, https://github.com/janarthanan-shanmugam/pg_query_optimizer

answered Jun 26, 2024 at 9:34

jansha

1711 silver badge12 bronze badges

Collectives™ on Stack Overflow

Optimize query in Rails

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related