当前位置: 动力学知识库 > 问答 > 编程问答 >

query optimization - Trying to fix multiple SQL queries in Rails app

问题描述:

I have a page that loads 300+ jobs for a cabinetry builder/installer. I load all their active jobs onto one page, per factory, of which they have three-- Dallas, Arlington, and Aubrey. Each factory has 300 to 400 active (scheduled, unininstalled jobs).

So, when I load the Dallas page, I go over each day, and output the jobs in order for that day. The queries are repeating for about 50 days. I want to know if I can send this info to the cache somehow.

Here's a picture of what a few days look like...

For each day I make 2 SUM queries for the header, and another query to filter jobs for that given day, i.e.,

@jobs.where(calendar_date: '2016-04-06').each do

Here are the precise queries

# ALL jobs (in the controller)

@jobs = Job.where( active: true, scheduled: true,

manufacturing_location: session[:factory]).

includes(:installer).

order('calendar_date asc')

# Jobs for one day (in the view

@jobs.where(calendar_date: day).order(:calendar_order).each do

# The helper method for the 'daily tabulations' in the header

= daily_tabulation(@jobs.where(calendar_date: day))

def daily_tabulation(jobs)

total_boxes = jobs.sum :number_of_cabinets

complete = jobs.where(complete_in_shop: true).sum :number_of_cabinets

to_build = total_boxes - complete

"To Build: #{to_build}, Complete: #{complete}, Total Boxes: #{total_boxes}"

end

These pages take 4 to 7 seconds to load in the browser, even using redis fragment caching. Granted, each of these 300+ jobs get JS event handlers to drag-n-drop the jobs around on the schedule, as well as JS tooltips, but I'm hoping I can clean up these queries and shave a second or more from the response time. Thank you.

网友答案:

Since you are working with one collection in this case, you can once load your jobs, and then process it like array:

@job_by_day = @jobs.select { |j| j.calendar_date == day }

And use Enumerable#sort instead of AR#order.

分享给朋友:
您可能感兴趣的文章:
随机阅读: