Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Counting the number of queries performed

I'd like to test that a certain piece of code performs as few SQL queries as possible.

ActiveRecord::TestCase seems to have its own assert_queries method, which will do just that. But since I'm not patching ActiveRecord, it's of little use to me.

Does RSpec or ActiveRecord provide any official, public means of counting the number of SQL queries performed in a block of code?

like image 480
Ian Lesperance Avatar asked Mar 30 '11 18:03

Ian Lesperance


People also ask

What are counting queries?

Defining Counting Queries Informally, counting queries have the form: "how many rows in the database have the property X?" For example, each row could correspond to a survey respondent, and the property X could be “replied 'yes' to the survey”.

How do you count in a query?

On the Design tab, in the Show/Hide group, click Totals. The Total row appears in the design grid and Group By appears in the row for each field in the query. In the Total row, click the field that you want to count and select Count from the resulting list.

What does count () do in SQL?

The SQL COUNT(), AVG() and SUM() Functions The COUNT() function returns the number of rows that matches a specified criterion.

How do I count orders in SQL?

The first step is to use the GROUP BY clause to create the groups (in our example, we group by the country column). Then, in the ORDER BY clause, you use the aggregate function COUNT, which counts the number of values in the column of your choice; in our example, we count distinct IDs with COUNT(id) .


2 Answers

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.

like image 84
Ryan Bigg Avatar answered Sep 21 '22 09:09

Ryan Bigg


My vision of Ryan's script (cleaned up a bit and wrapped in a matcher), hope it is still actual for someone:

I put this to spec/support/query_counter.rb

module ActiveRecord   class QueryCounter      attr_reader :query_count      def initialize       @query_count = 0     end      def to_proc       lambda(&method(:callback))     end      def callback(name, start, finish, message_id, values)       @query_count += 1 unless %w(CACHE SCHEMA).include?(values[:name])     end    end end 

and this to spec/support/matchers/exceed_query_limit.rb

RSpec::Matchers.define :exceed_query_limit do |expected|    match do |block|     query_count(&block) > expected   end    failure_message_for_should_not do |actual|     "Expected to run maximum #{expected} queries, got #{@counter.query_count}"   end    def query_count(&block)     @counter = ActiveRecord::QueryCounter.new     ActiveSupport::Notifications.subscribed(@counter.to_proc, 'sql.active_record', &block)     @counter.query_count   end  end 

Usage:

expect { MyModel.do_the_queries }.to_not exceed_query_limit(2) 
like image 20
Yuriy Kharchenko Avatar answered Sep 18 '22 09:09

Yuriy Kharchenko