Scoped query generating superfluous SELECT COUNT(*)?

Rails
1

I’m using an ActiveRecord scope as a discriminator then passing it to
another function for the actual query. It’s generating what I consider
to be an extra SQL transaction, and since this is in an inner loop, I’d
like to optimize it. First the schema:

create_table “thermal_models”, :force => true do |t|
t.integer “natural_resource_id”
t.integer “premise_attribute_name_id”
t.integer “premise_group_name_id”
t.integer “table_type”
t.float “x”
t.float “y”
t.float “m”
t.timestamps
end

and the scope (e.g):

myscope = ThermalModel.scoped.where(:natural_resource_id => 1,
:premise_attribute_name_id => 5,
:premise_group_name_id => 1,
:table_type => 7)

Then, this query:

segments = myscope.where("x < 18.7").order("x DESC").limit(1)

generates the following TWO transactions. I guess the first transaction
is simply generating a count of the result – the subquery is not sorted
and its outer query simply counts how many 1’s the subquery emitted –
but I don’t see its utility.

Am I misunderstanding how scopes are meant to be used? Is there
something I can do (short of writing direct SQL) that will get this down
to one transaction?

SQL (2.5ms) SELECT COUNT() AS count_id FROM (SELECT 1 FROM
thermal_models WHERE (thermal_models.natural_resource_id = 1) AND
(thermal_models.premise_attribute_name_id = 5) AND
(thermal_models.premise_group_name_id = 1) AND
(thermal_models.table_type = 7) AND (x < 18.7) LIMIT 1) AS subquery
ThermalModel Load (1.4ms) SELECT thermal_models. FROM
thermal_models WHERE (thermal_models.natural_resource_id = 1) AND
(thermal_models.premise_attribute_name_id = 5) AND
(thermal_models.premise_group_name_id = 1) AND
(thermal_models.table_type = 7) AND (x < 18.7) ORDER BY x DESC LIMIT
1

  • ff

P.S.: Despite the above, I think scopes are the bees knees. They let
you
define modules that act on specific columns of a table while leaving the
actual selection of rows to another piece of code. It essentially
allows you to do duck typing for SQL.

2

Here ff goes again, answering his own questions:

The “superflous” SELECT COUNT(*) is probably just counting the # of
elements that will be returned so Rails can allocate an array in which
to receive it.

In this particular case, the optimization is easy. Changing

segments = myscope.where("x < 18.7").order("x DESC").limit(1)

to

segment = myscope.where("x < 18.7").order("x DESC").limit(1).first

tells the system that we’re only returning a single element (which is
all I wanted anyway), so it doesn’t need to allocate an array, so it
doesn’t make the extra SELECT COUNT(*) call.

  • ff
3

On 4 December 2010 14:36, Fearless F. [email protected] wrote:

to

segment = myscope.where(“x < 18.7”).order(“x DESC”).limit(1).first

tells the system that we’re only returning a single element (which is
all I wanted anyway), so it doesn’t need to allocate an array, so it
doesn’t make the extra SELECT COUNT(*) call.

Do you need the limit(1) if you have .first?

Colin

4

@colin:

You are correct:

segment = myscope.where("x < 18.7").order("x DESC").first

generates the same code as:

segment = myscope.where("x < 18.7").order("x DESC").limit(1).first

[My only excuse is that I have not fully weaned myself from writing raw
SQL, and the limit(1) is comforting! :)]

thanks for the tip!

  • ff
5

Fearless F. wrote in post #966171:

Here ff goes again, answering his own questions:

The “superflous” SELECT COUNT(*) is probably just counting the # of
elements that will be returned so Rails can allocate an array in which
to receive it.

I don’t buy it. Arrays in Ruby are dynamically allocated, and there’s
no reason that the count can’t be fetched along with the results.

In this particular case, the optimization is easy. Changing

segments = myscope.where("x < 18.7").order("x DESC").limit(1)

to

segment = myscope.where("x < 18.7").order("x DESC").limit(1).first

tells the system that we’re only returning a single element (which is
all I wanted anyway), so it doesn’t need to allocate an array, so it
doesn’t make the extra SELECT COUNT(*) call.

Bizarre. But then, I haven’t really played with Arel yet.

  • ff

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

Sent from my iPhone