On Tuesday, October 16, 2012 1:10:05 PM UTC-7, james croft wrote:
>
> Hi,
> I am seeing an extra query being executed when a sequel model is first
> loaded The model I am concerned with is built off a dataset with joins and
> aggregation, the particular query is slow to execute and so I'd like to
> stop it if possible.
>
> I have two models:
>
> class Track < Sequel::Model
> end
>
> class MostCharted < Sequel::Model
>
> set_dataset Track.
> select{[:tracks__id, :tracks__title, :tracks__artist_id,
> :tracks__slug, count(charts__track_id).as(times_charted)]}.
> from(:tracks).
> join(:charts, :track_id => :id).
> group(:tracks__id, :tracks__title, :tracks__artist_id,
> :tracks__slug).
> order(:times_charted.desc, :tracks__id.desc)
> end
>
> I am using these models in a Rails app and when I execute
> MostCharted.first from the console, I see the following two queries in
> the log.
>
> (8.952053s) SELECT "tracks"."id", "tracks"."title", "tracks"."artist_id",
> "tracks"."slug", count("charts"."track_id") AS "times_charted" FROM
> "tracks" INNER JOIN "charts" ON ("charts"."track_id" = "tracks"."id") GROUP
> BY "tracks"."id", "tracks"."title", "tracks"."artist_id", "tracks"."slug"
> LIMIT 1
>
> (10.458585s) SELECT "tracks"."id", "tracks"."title", "tracks"."artist_id",
> "tracks"."slug", count("charts"."track_id") AS "times_charted" FROM
> "tracks" INNER JOIN "charts" ON ("charts"."track_id" = "tracks"."id") GROUP
> BY "tracks"."id", "tracks"."title", "tracks"."artist_id", "tracks"."slug"
> ORDER BY "times_charted" DESC, "tracks"."id" DESC LIMIT 1
>
> It is the second query that is returning the data that I want. I don't
> know what is triggering the first query. Subsequent calls to the
> MostCharted model just execute the queries that I need:
>
The first query is performed when Sequel tries to get the columns for the
model. columns currently removes filers and orders. I'm not sure what
database you are using where removing an order makes a query slower. You
could overwrite Dataset#columns for the model's dataset before the
set_dataset call to not call unordered, that may fix the issue.
You could also just use a database view for the MostCharted model, that may
be easier than hacking Sequel:
DB.create_view :most_charted, Track.
select{[:tracks__id, :tracks__title, :tracks__artist_id,
:tracks__slug, count(charts__track_id).as(times_charted)]}.
from(:tracks).
join(:charts, :track_id => :id).
group(:tracks__id, :tracks__title, :tracks__artist_id,
:tracks__slug).
order(:times_charted.desc, :tracks__id.desc)
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sequel-talk/-/JXD6wrFYBC4J.
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/sequel-talk?hl=en.
