How to (massively) reduce the number of SQL queries in Rails app?
Whenever revenue_between
is called, it fetches the payments
in the given time range and the associated invoices
and items
from the db. Since the time ranges have lot of overlap (month, quarter, year), same records are being fetched over and over again.
I think it is better to fetch all the payments of the user once, then filter and summarize them in Ruby.
To implement, change the revenue_between
method as follows:
def revenue_between(range, kind)
#store the all the payments as instance variable to avoid duplicate queries
@payments_with_invoice ||= payments.includes(:invoice => :items).all
@payments_with_invoice.select{|x| range.cover? x.created_at}.sum(&:"#{kind}_amount")
end
This would eager load all the payments along with associated invoices and items. Also change the invoice
summation methods so that it uses the eager loaded items
class Invoice < ActiveRecord::Base
def total
items.map(&:total).sum
end
def subtotal
items.map(&:subtotal).sum
end
def total_tax
items.map(&:total_tax).sum
end
end
How can I prevent my Rails query from maxing out memory
The issue was with sending tasks to Resque. Thanks to @user2864740 for having this idea.
First I tested by using an empty find_each
block, and that didn't cause memory growth. Then I tried a find_each
block that created an ActiveRecord object, and that didn't cause memory growth.
Finally I tested doing a find_each
over records and sending off a task to resque. This alone caused the memory to steadily climb and not fall. I should have also mentioned that I'm using the ResqueDelayable gem to dispatch tasks to resque, and it might be implicated by this.
auto-filtering all activerecord queries in rails app
use default_scope for this
http://apidock.com/rails/ActiveRecord/Base/default_scope/class
in your case s.th. like
default_scope where("product_id in (?)",[allowed_products_array])
you can extract it into moduleand include into every model you'd like to have this functionality.
Implementing a filter for a SQL query in a Rails app
I would consider creating a materialized view of the union of the two tables.
CREATE MATERIALIZED VIEW unified_notifications AS
SELECT
sn.id,
sellers.name,
sn.seller_id AS member_id,
sn.title,
sn.body,
sn.created_at,
sn.is_new,
sellers.platform_id,
sn.pending_action_id,
'seller_notifications' AS type
FROM seller_notifications sn
INNER JOIN sellers ON sellers.id = sn.seller_id
UNION ALL
SELECT
un.id,
users.name,
un.user_id AS member_id,
un.title,
un.body,
un.created_at,
un.is_new,
users.platform_id,
un.pending_action_id,
'user_notifications' AS type
FROM user_notifications un
INNER JOIN users ON users.id = un.user_id
The scenic gem can be used to create views with Rails migrations. Note that you need to switch to SQL schema dumps.Since the view behaves like a table (at least from the perspective of ActiveRecord) you can just create an ActiveRecord model and query it like it was a single table:
class UnifiedNotification < ApplicationRecord
def readonly?
true
end
end
UnifiedNotification.where(
platform_id: @user.platform_id,
# ...
)
Rails3: SQL execution with hash substitution like .where()
query = ActiveRecord::Base.connection.raw_connection.prepare("INSERT INTO users (name) VALUES(:name)")
query.execute(:name => 'test_name')
query.close
Counting the number of queries performed
I think you answered your own question by mentioning assert_queries
, but here goes:
I would recommend taking a look at the code behind assert_queries
and using that to build your own method which you can use to count queries. The main magic involved here is this line:
ActiveSupport::Notifications.subscribe('sql.active_record', SQLCounter.new)
I had a bit of a tinker this morning and ripped out the parts of ActiveRecord that do the query counting and came up with this:module ActiveRecord
class QueryCounter
cattr_accessor :query_count do
0
end
IGNORED_SQL = [/^PRAGMA (?!(table_info))/, /^SELECT currval/, /^SELECT CAST/, /^SELECT @@IDENTITY/, /^SELECT @@ROWCOUNT/, /^SAVEPOINT/, /^ROLLBACK TO SAVEPOINT/, /^RELEASE SAVEPOINT/, /^SHOW max_identifier_length/]
def call(name, start, finish, message_id, values)
# FIXME: this seems bad. we should probably have a better way to indicate
# the query was cached
unless 'CACHE' == values[:name]
self.class.query_count += 1 unless IGNORED_SQL.any? { |r| values[:sql] =~ r }
end
end
end
end
ActiveSupport::Notifications.subscribe('sql.active_record', ActiveRecord::QueryCounter.new)
module ActiveRecord
class Base
def self.count_queries(&block)
ActiveRecord::QueryCounter.query_count = 0
yield
ActiveRecord::QueryCounter.query_count
end
end
end
You will be able to reference the ActiveRecord::Base.count_queries
method anywhere. Pass it a block wherein your queries are run and it will return the number of queries that have been executed:ActiveRecord::Base.count_queries do
Ticket.first
end
Returns "1" for me. To make this work: put it in a file at lib/active_record/query_counter.rb
and require it in your config/application.rb
file like this:require 'active_record/query_counter'
Hey presto!A little bit of explanation probably is required. When we call this line:
ActiveSupport::Notifications.subscribe('sql.active_record', ActiveRecord::QueryCounter.new)
We hook into Rails 3's little notifications framework. It's a shiny little addition to the latest major version of Rails that nobody really knows about. It allows us to subscribe to notifications of events within Rails by using the subscribe
method. We pass in the event we want to subscribe to as the first argument then any object that responds to call
as the second.In this case when a query is executed our little query counter will dutifully increment the ActiveRecord::QueryCounter.query_count variable, but only for the real queries.
Anyway, this was fun. I hope it comes useful to you.
ActiveRecord Rails - create a batch of empty records
Take a look at the activerecord-import gem. It handles bulk inserts like you described, while keeping the number of ActiveRecord queries to a minimum. And it's probably a good idea to do this in a background job since you are creating so many records.
Related Topics
When Is The Enumerator::Yielder#Yield Method Useful
More Ruby Way of Doing Project Euler #2
Ruby Symbols Are Not Garbage Collected!? Then, Isn't It Better to Use a String
Rails/Postgres, 'Foreign Keys' Stored in Array to Create 1-Many Association
What Is Returned in Ruby If The Last Statement Evaluated Is an If Statement
Remove Adjacent Identical Elements in a Ruby Array
Clean Install Osx 10.9.1 Returns "Undefined Method 'Path2Class'" When Trying to Install Gems
How to Interact with a Caldav Server from Ruby
How to Use C# Style Enumerations in Ruby
Gem Ransack Doesn't Return Any Results When Searched with Full Name
How to Get Records Created at The Current Month
How to Remove Ruby on Rails 4 Beta