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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
