scoped query generating superfluous SELECT COUNT(*)?

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.

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

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

Colin

@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

Fearless Fool 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,