I'm building a ruby on rails application that uses raw SQL to query my database because I heard that it performs better than using ActiveRecord. I have an array that stores a list of items that are BigInts. For example:
my_items_id = [43627164222, 43667161211, 43667161000]
And my sql statement is supposed to return all values from a table where the id is any of the ones in my_items_id.
sql = "select * from table1 where id IN #{my_items_id}"
records_array = ActiveRecord::Base.connection.execute(sql)
The reason why this doesn't work is because my_items_id is an array and i know that. But what is the best way to convert it to: (43627164222, 43667161211, 43667161000) so that the sql statement actually works.
You write that you want to avoid ActiveRecord because read that raw SQL performs better. That is probably correct especially when you have to handle millions of records.
But what makes ActiveRecord slower compared to raw SQL is certainly not building and sanitizing the query upfront. ActiveRecord is slower because it parses the result and returns instances of your database models instead of a simple hash-like structure.
That said IMO it is perfectly fine to build the query with the ActiveRecord query language but run it as raw SQL on the plain connection. Then you would still benefit from ActiveRecord's query language and it's security features against SQL injections.
my_items_id = [43627164222, 43667161211, 43667161000]
sql = Table1.where(id: my_items_id).to_sql # <= Note the `to_sql` where
records_array = ActiveRecord::Base.connection.execute(sql)
Another issue when handling millions of records is not only the parsing part of ActiveRecord but the fact that millions of records consume a lot of RAM and might, therefore, be slower than expected. ActiveRecord has helper methods for this problem too – like find_each or find_in_batches. These methods do not load all records into memory at the same time but in smaller batches and can improve the overall performance of the operation a lot.
Table1.where(id: my_items_id).find_each do |item|
# handle each item
end
Or you might only need parts of the original records and not all columns then using pluck will be helpful. It improves again the performance of the ActiveRecord query because it returns a simple nested array instead of complex ActiveRecord instances – what saves time on parsing and memory.
Another issue with slow queries on millions of records in certainly missing database indexes. But without knowing more about the database structure and the slow queries it is impossible to give any advice.