I have a query that involves a couple of left joins and some
subqueries. Searching around on how to do this, I came across a method
that paginated from an arbitrary query:
def paginate_from_sql(model, sql, count_sql, per_page)
plural_model_name = "@#{model.name.underscore.pluralize}".to_sym
paginator_name = "@#{model.name.underscore}_pages".to_sym
self.instance_variable_set(paginator_name, Paginator.new(self,
model.count_by_sql(count_sql), per_page, @params[''page'']))
self.instance_variable_set(plural_model_name,
model.find_by_sql(sql + " LIMIT #{per_page}" + " OFFSET
#{self.instance_variable_get(paginator_name).current.to_sql[1]}"))
end
I then applied my query:
Plain english: "Select all subjects with <subject conditions> who do
not have any calls with a call_back date scheduled and have not
received 6 calls or more".
query = "SELECT subjects.* FROM (" +
"SELECT subjects.*,count(subjects.id) AS callsum FROM (" +
"SELECT subjects.* FROM subjects LEFT JOIN calls ON
calls.subject_id=subjects.id AND calls.call_back_date IS NOT NULL WHERE
calls.id IS NULL AND subjects.agreed_to_participate IS NULL AND
subjects.valid_number = ''T'') AS subjects " +
"LEFT JOIN calls ON calls.subject_id=subjects.id GROUP BY
subjects.id) as subjects"+
" WHERE callsum < 6 AND
date_format(subjects.updated_on,''%Y-%m-%d'') <
DATE_SUB(CURDATE(),
INTERVAL 1 DAY)"
count_by_sql = "SELECT COUNT(*) FROM (query)"
paginate_from_sql(Subject, query, count_by_sql, 15)
This all works fine and ouputs the correct data paginated and all.
But (there''s always a but), the performance is horrendous compared to
the previous php implementation of the same functionality. There''s
noticeable lag on each pagination or page load. Just doing a loose
count, it takes about 12s to fetch and display the data compared to
about 1 or 2s for php.
Questions:
1) Is there a more friendly way to fetch and paginate queries like
this?
2) Am I missing something here that could dramatically increase
performance as far as page rendering?
--~--~---------~--~----~------------~-------~--~----~
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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---