0

I have the following code that takes results, groups them by date and creates a customer count for that date, and then inserts it in to a Calculation table:

customers.group_by { |c| Date.parse c.created.to_s }.map do |date, v|
  value = v.map {|h1| 1}.sum

  Calculation.find_or_initialize_by(
    account_id: a.id,
    metric: 'active_customers',
    occurred_on: date
  ).update(
    value: value
  )
end

If there are hundreds of dates, it has to run find_or_initialize_by...hundreds of times.

What I'd love to do is somehow avoid doing hundreds of "finds" and "creates" and somehow pull of just a couple of database calls.

The reason being that I'm hitting issues with number of database connections with so many finds/inserts happening at the same time (this bit of code is getting run simultaneously via backgrounds jobs).

I'm using Rails 4 and Postgres 9.3.1.

Shpigford
  • 24,748
  • 58
  • 163
  • 252
  • don't have time to answer it now but can give you clue. You can use postgresql `upsert` but you have create its sql query manually.Else i will answer it tomorrow – Paritosh Piplewar Mar 03 '14 at 19:27
  • @ParitoshPiplewar: Huh? There is no `upsert` in Postgres (yet). Only manual implementations of the same, like [here](http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql) or [here](http://stackoverflow.com/questions/17152860/upsert-with-a-transaction). – Erwin Brandstetter Mar 03 '14 at 20:55
  • Most probably you can do it all in a *single* SQL statement, which would be faster by orders of magnitude. Maybe someone can work off your Ruby snippet - I would need your table definition (what you get with `\d tbl` in psql), some sample data (ideally as SQL Fiddle - [random example](http://sqlfiddle.com/#!15/8ace9/2)) and what the desired result should look like. – Erwin Brandstetter Mar 03 '14 at 21:02
  • @ErwinBrandstetter yes, i know. I have followed this answer http://stackoverflow.com/a/8702291/1377943 long ago and it works greatly. It allows us to do 300+K transactions (Update or INsert) everyday smoothly. But i have manually build the sql and execute it. – Paritosh Piplewar Mar 03 '14 at 21:45

0 Answers0