When I first started my project, I thought long and hard about the exact
information I would need. Because my site works largely on statistical
analysis and virtual matchups can be done by any number of scenarios, it
makes it very difficult to come up with a common solution.
The simple facts are that at any type ‘any two’ teams can be compared
against one another. The data that is used for comparison is very
extensive. When I started with a large join, the return time was so
large that at times it would freeze up my development machine. So, I
carefully and systematically broke it down into several smaller joins to
ease the load.
This worked fairly well, considering the type of data I’m pulling. With
that said, the best time I can find when running this particular query
is 540 ms. As more and more work load hits the test server, hours later
this lone query can reach up to 14,000 ms (14 seconds is just too long).
So, I’m trying to find a way to speed up my query, optimize it, or
figure out what I can do to improve this result. Now the model method
I’m about to show you is in fact enormous, but if you take it a part
piece by piece you’ll see that I’m using selects for specific columns,
joins for specific tables and trying to do several smaller queries to
speed things up.
My question is what can I do to improve this?
def self.compareteams(teamone, teamtwo)
week_start_date
=Time.now.beginning_of_week.to_date.strftime(’%Y-%m-%d’)
week_end_date = Time.now.end_of_week.to_date.strftime(’%Y-%m-%d’)
compiled_on = week_start_date…week_end_date
tsrs = Team.find(teamone,teamtwo, :select => “teams.name,
teams.mascot, teams.formal_name, teams.image, tsrs_ratings.tsrs”, :joins
=> [:tsrs_ratings], :conditions => {:tsrs_ratings => {:compiled_on =>
compiled_on}}, :order => “teams.id”)
tsos_offense = Team.find(teamone,teamtwo, :select =>
“tsos_offenses.totoff, tsos_offenses.rushoff, tsos_offenses.passoff,
tsos_offenses.scoroff, tsos_offenses.rzonoff, tsos_offenses.fumlost,
tsos_offenses.passhint, tsos_offenses.tolost, tsos_offenses.sacksall,
tsos_offenses.tackflossall, tsos_offenses.passeff,
tsos_offenses.firdwns, tsos_offenses.thrdwncon, tsos_offenses.fthdwncon,
tsos_offenses.totals”, :joins => [:tsos_offenses], :conditions =>
{:tsos_offenses => {:compiled_on => compiled_on}}, :order => “teams.id”)
tsos_defense = Team.find(teamone,teamtwo, :select =>
“tsos_defenses.totdef, tsos_defenses.rushdef, tsos_defenses.passdef,
tsos_defenses.scordef, tsos_defenses.rzondef, tsos_defenses.fumgain,
tsos_defenses.passint, tsos_defenses.togain, tsos_defenses.sacks AS
allsacks, tsos_defenses.tackfloss, tsos_defenses.passeffdef,
tsos_defenses.firdwnsdef, tsos_defenses.thrdwncondef,
tsos_defenses.fthdwncon, tsos_defenses.totals”, :joins =>
[:tsos_defenses], :conditions => {:tsos_defenses => {:compiled_on =>
compiled_on}}, :order => “teams.id”)
tsos_steams = Team.find(teamone,teamtwo, :select =>
“tsos_steams.kickret, tsos_steams.puntret, tsos_steams.netpunt,
tsos_steams.kickretdef, tsos_steams.puntretdef, tsos_steams.totals”,
:joins => [:tsos_steams], :conditions => {:tsos_steams => {:compiled_on
=> compiled_on}}, :order => “teams.id”)
offense = Team.find(teamone,teamtwo, :select =>
“total_offenses.ydspgm AS offtotal, rushing_offenses.ydspg AS offrush,
passing_offenses.ydspgm AS offpass, scoring_offenses.avg AS offscoring,
red_zone_offenses.pct AS offrzone, fumbles_losts.fumbles_lost AS
fumlost, passes_had_intercepteds.passes_had_intercepted AS passhint,
sacks_alloweds.pg AS sacksall, tackles_for_loss_alloweds.pg AS
tflossall, passing_efficiencies.rating AS pasoffeff,
first_downs_offenses.pg AS firdwnoff,
third_down_conversion_percentages.pct AS thirdwnpctoff,
fourth_down_conversion_percentages.pct AS fourdwnpctoff”, :joins =>
[:total_offenses, :rushing_offenses, :passing_offenses,
:scoring_offenses, :red_zone_offenses, :fumbles_losts,
:passes_had_intercepteds, :sacks_alloweds, :tackles_for_loss_alloweds,
:passing_efficiencies, :first_downs_offenses,
:third_down_conversion_percentages,
:fourth_down_conversion_percentages], :conditions => {:total_offenses =>
{:compiled_on => compiled_on}, :rushing_offenses => {:compiled_on =>
compiled_on}, :passing_offenses => {:compiled_on => compiled_on},
:scoring_offenses => {:compiled_on => compiled_on}, :red_zone_offenses
=> {:compiled_on => compiled_on}, :fumbles_losts => {:compiled_on =>
compiled_on}, :passes_had_intercepteds => {:compiled_on => compiled_on},
:sacks_alloweds => {:compiled_on => compiled_on},
:tackles_for_loss_alloweds => {:compiled_on => compiled_on},
:passing_efficiencies => {:compiled_on => compiled_on},
:first_downs_offenses => {:compiled_on => compiled_on},
:third_down_conversion_percentages => {:compiled_on => compiled_on},
:fourth_down_conversion_percentages => {:compiled_on => compiled_on}},
:order => “teams.id”)
defense = Team.find(teamone,teamtwo, :select =>
“total_defenses.ydspgm AS deftotal, rushing_defenses.ydspg AS defrush,
pass_defenses.ydspgm AS defpass, scoring_defenses.ptspgm AS defscoring,
red_zone_defenses.pct AS defrzone, fumbles_recovereds.fumbles_recovered
AS fumrec, passes_intercepteds.passes_intercepted AS passint, sacks.pg
AS sacksdef, tackles_for_losses.pg AS tfloss,
pass_efficiency_defenses.rating AS pasdefeff, first_downs_defenses.pg AS
firdwndef, third_down_percentage_defenses.pct AS thirdwnpctdef,
fourth_down_percentage_defenses.pct AS fourdwnpctdef”, :joins =>
[:total_defenses, :rushing_defenses, :pass_defenses, :scoring_defenses,
:red_zone_defenses, :fumbles_recovereds, :passes_intercepteds, :sacks,
:tackles_for_losses, :pass_efficiency_defenses, :first_downs_defenses,
:third_down_percentage_defenses, :fourth_down_percentage_defenses],
:conditions => {:total_defenses => {:compiled_on => compiled_on},
:rushing_defenses => {:compiled_on => compiled_on}, :pass_defenses =>
{:compiled_on => compiled_on}, :scoring_defenses => {:compiled_on =>
compiled_on}, :red_zone_defenses => {:compiled_on => compiled_on},
:fumbles_recovereds => {:compiled_on => compiled_on},
:passes_intercepteds => {:compiled_on => compiled_on}, :sacks =>
{:compiled_on => compiled_on}, :tackles_for_losses => {:compiled_on =>
compiled_on}, :pass_efficiency_defenses => {:compiled_on =>
compiled_on}, :first_downs_defenses => {:compiled_on => compiled_on},
:third_down_percentage_defenses => {:compiled_on => compiled_on},
:fourth_down_percentage_defenses => {:compiled_on => compiled_on}},
:order => “teams.id”)
turnovers = Team.find(teamone,teamtwo, :select =>
“turnover_margins.margin AS tomargin, tsos_turnover_margins.margin AS
tomarginrating”, :joins => [:turnover_margins, :tsos_turnover_margins],
:conditions => {:turnover_margins => {:compiled_on => compiled_on},
:tsos_turnover_margins => {:compiled_on => compiled_on}}, :order =>
“teams.id”)
special_teams = Team.find(teamone,teamtwo, :select =>
“kickoff_returns.avg AS kickydsper,
kickoff_return_yardage_defenses.ydsperret AS kickydsperdef,
punt_returns.avg AS puntydsper, punt_return_yardage_defenses.ydsperret
AS puntydsperdef, net_puntings.net_avg AS netpunt”, :joins =>
[:kickoff_returns, :kickoff_return_yardage_defenses, :punt_returns,
:punt_return_yardage_defenses, :net_puntings], :conditions =>
{:kickoff_returns => {:compiled_on => compiled_on},
:kickoff_return_yardage_defenses => {:compiled_on => compiled_on},
:punt_returns => {:compiled_on => compiled_on},
:punt_return_yardage_defenses => {:compiled_on => compiled_on},
:net_puntings => {:compiled_on => compiled_on}}, :order => “teams.id”)
return tsrs, tsos_offense, tsos_defense, tsos_steams, offense,
defense, turnovers, special_teams
end
8 variables returned.
The bottleneck begins to occur on offense = … (around 30 ms) and then
deepens on defense = … (around 460 ms).
These are indeed large but everything listed here is absolutely required
for my app to work. What can I do to optimize this?
thanks.
