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.firstfrom
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:
MostCharted.first
(10.042714s) 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
This is the case until I reload the model with reload!. After that, the
next time I call any query on MostCharted, the first query will be issued
again:
(8.876785s) 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
(11.237500s) 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
Is there a way I can avoid this? The query is long running and is causing
problems in Rails development where the models are frequently reloaded.
Thanks
James
--
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/-/39GZXgcp1_QJ.
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.
