Rails - Dec 2010 - 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.

-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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

-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On 4 December 2010 14:36, Fearless Fool
<lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>
wrote:
> 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.
Do you need the limit(1) if you have .first?

Colin

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

@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

-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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,
-- 
Marnen Laibow-Koser
http://www.marnen.org
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org

Sent from my iPhone

-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.