What's the fastest way to do division calculation in an sqlite database

Question

I have an sqlite database table like this

I need to calculate for each TimeIndex, the value for ObjectIndex == 1, divided by the value for ObjectIndex == 2, e.g. for TimeIndex == 1, 100 / 5 = 20

My code uses Ruby Sequel, here's what I have so far:

array_1 = db[:my_table].where(ObjectIndex: 1).order(:TimeIndex).select_map(:Value)
array_2 = db[:my_table].where(ObjectIndex: 2).order(:TimeIndex).select_map(:Value)
division = array_1.zip(array_2).map { |x, y| y == 0 ? 0 : (x / y).round(2)}

...then use division in my ruby code elsewhere

And currently it takes about 10 seconds to process one ObjectIndex (10s if I print out array_1.first). There are about 5813k rows in the table and each ObjectIndex has about 9000 rows. Is this speed normal or is there a way to decrease the processing time?

Answer 1

Currently you are running two separate database queries and converting the result to Ruby objects, then zipping and mapping those objects in pure Ruby (no DB involved). Generally speaking, the simplest way to speed up DB usage is to minimize queries, minimize conversions between DB and non-DB types, and to do calculation in the DB as part of the query.

I don't know the pretty way to do this with the Sequel gem, but it can run arbitrary queries, so this might work:

db.run(<<-SQL
  SELECT T1.Value / T2.Value
  FROM my_table T1 JOIN my_table T2
  ON T1.TimeIndex = T2.TimeIndex
  WHERE T1.ObjectIndex = 1 AND T2.ObjectIndex = 2
SQL
)

you can add more columns to the select if needed.