patternphpMinor

Improve Speed of RPI Calculation

Submitted by: @import:stackexchange-codereview·Mar 10, 2026·

Viewed 0 times

improvecalculationspeedrpi

Problem

It currently takes about 10 minutes to process ~16k teams and ~81k games. I could soon have ~17k teams with ~160k, and multiple sports. I run this as a cron job overnight and store the results in a serialized gzipped array which is processed upon each pageload to sort teams by states for displaying and actually calculate the RPI (add WP, OWP and OOWP together) which works well for speed for displaying purposes. I'm only concerned with trying to speed up this calculation:

If you need more information about what the RPI (Ratings Percentage Index) is, this is a good primer. Please note, I have modified from this formula to include a weighting factor (function reduction_factor() ) so it is slightly different that that page states.

Here is my base function:

``


// *****
// function to calculate RPI given a sportid and year, optional cutoff date
// *****
function rpi($sportid, $year, $date = FALSE, $my_db, $sport_array){
    //figure out if this is a year-spanning sport
    $year_display = $year;
    $date_start = $year.$sport_array[$sportid]['start_date'];
    $date_end = $year.$sport_array[$sportid]['end_date'];

    if ($sport_array[$sportid]['end_year_plus']){
        $year_display = $year.'-'.($year+1);
        $date_end = ($year+1).$sport_array[$sportid]['end_date'];
    }

    if ($date) $date_end = $date;
    elseif ($date_end > date("Y-m-d")) $date_end = date("Y-m-d");

    $rpiteamarray = array();

    $results = $my_db->query("SELECT

teamid FROM team WHERE jv = '0' AND teamid` > 0;");

while ($row = $results->fetchRow(MDB2_FETCHMODE_ASSOC)){
$teamid = $row['teamid'];

if (!array_key_exists($teamid, $rpiteamarray)) $rpiteamarray[$teamid] = new rpiteam($teamid, $sportid, $year, $my_db, $dat

Solution

Not quite a lot that can be done performance-wise...you have a lot of data to pour over (16,000 81,000 = 1,296,000,000 records and with the new data 17,000 160,000 = 2,720,000,000 records). You could possibly think of moving over to a faster database (Redis? Cassandra?).

Anyways, I made a few minor changes to your code - added empty() checks rather than empty string checks, shortened a few if statements, etc.

 $today){
        $date_end = $today;
    }

    $results = $my_db->query("SELECT `teamid` FROM `team` WHERE `jv` = '0' AND `teamid` > 0;");

    while ($row = $results->fetchRow(MDB2_FETCHMODE_ASSOC)){
        $teamid = $row['teamid'];

        if(!array_key_exists($teamid, $rpiteamarray)){
            $rpiteamarray[$teamid] = new rpiteam($teamid, $sportid, $year, $my_db, $date_start, $date_end);
        }
        if($rpiteamarray[$teamid]->total_games() == 0){
            continue; 
        }
        $rpi[$teamid]['numgames'] = $rpiteamarray[$teamid]->total_games();
    }
    unset($results, $row);

    foreach($rpi as $teamid => $value){
        $rpi[$teamid]['wp'] = $rpiteamarray[$teamid]->wp();
        $rpi[$teamid]['owp'] = $rpiteamarray[$teamid]->owp($rpiteamarray);
        $rpi[$teamid]['oowp'] = $rpiteamarray[$teamid]->oowp($rpiteamarray);
    }

    return $rpi;
}
?>

and the class:

``


db = $db;
        $this->sportid = $sportid;
        $this->teamid = $teamid;
        $this->year = $year;
        $this->date_start = $date_start;
        $this->date_end = $date_end;

        $row = $this->db->query("SELECT

levelid,levelid_state FROM team_class WHERE teamid = $teamid AND sportid = $sportid AND year

 = $year;")->fetchRow(MDB2_FETCHMODE_ASSOC);
        $this->levelid = $row['levelid'];
        $this->levelid_state = $row['levelid_state'];
        $this->opponentsarray = array();
    }

    public function wins(){
        if (!empty($this->wins)){
            return $this->wins;
        }

        $this->wins = $this->db->query("SELECT COUNT(

winner) FROM game WHERE sportid = $this->sportid AND winner = $this->teamid AND date

 BETWEEN '$this->date_start' AND '$this->date_end';")->fetchOne();
        return $this->wins;
    }

    public function loss(){
        if (!empty($this->loss)){
            return $this->loss;
        }
        $this->loss = ($this->total_games())-($this->wins());
        return $this->loss;
    }

    public function total_games(){
        if (!empty($this->total)){
            return $this->total;
        }
        $this->total = $this->db->query("SELECT COUNT(

winner) FROM game WHERE sportid = $this->sportid AND winner IS NOT NULL AND (hometeam = $this->teamid OR awayteam = $this->teamid) AND date

 BETWEEN '$this->date_start' AND '$this->date_end';")->fetchOne();
        return $this->total;
    }

    public function get_opponentsarray(){
        if (!empty($this->opponentsarray)){
            return $this->opponentsarray;
        }

        $result = $this->db->query("SELECT

hometeam,awayteam,winner FROM game WHERE sportid = $this->sportid AND winner IS NOT NULL AND (hometeam = $this->teamid OR awayteam = $this->teamid) AND date BETWEEN '$this->date_start' AND '$this->date_end' ORDER BY date` ASC;");
while ($row = $result->fetchRow(MDB2_FETCHMODE_ASSOC)){
if ($row['hometeam'] == $this->teamid){
if ($row['winner'] == $this->teamid) $this->opponentsarray[$row['awayteam']] = 'W';
elseif ($row['winner'] == 0) $this->opponentsarray[$row['hometeam']] = 'T';
else $this->opponentsarray[$row['awayteam']] = 'L';
} elseif ($row['awayteam'] == $this->teamid){
if ($row['winner'] == $this->teamid) $this->opponentsarray[$row['hometeam']] = 'W';
elseif ($row['winner'] == 0) $this->opponentsarray[$row['hometeam']] = 'T';
else $this->opponentsarray[$row['hometeam']] = 'L';
}
}
return $this->opponentsarray;
}

public static function reduction_factor($levelid){
switch ($levelid){
case 66: return 1.00;
case 55: return 1.00;
case 44: return 0.90;
case 33: return 0.75;
case 22: return 0.60;
case 11: return 0.45;
case 9: return 0.40;
case 8: return 0.35;
case 7: return 0.30;
case 6: return 0.15;
default: return 1.00;
}
}

/*
* Calculate WP (winning percentage)
*/
public function wp(){
if (!empty($this->wp)){
return $this->wp;
}

if ($this->total_games() == 0) {
$this->wp = 0;
return $this->wp;
}

$reduce = $this->reduction_factor($this->levelid_state);

$this->wp = round($this->wins() / $this->total_games() * $reduce, 4);
return $this->wp;
}

/*
* Calculate WP (winning percentage) wit

Code Snippets

<?php

/**
 * Function to calculate RPI given a sportid and year, optional cutoff date
 */

function rpi($sportid, $year, $date = FALSE, $my_db, $sport_array){
    # figure out if this is a year-spanning sport
    $year_display = $year;
    $date_start = $year . $sport_array[$sportid]['start_date'];
    $date_end = $year . $sport_array[$sportid]['end_date'];
    $today = date("Y-m-d");
    $rpiteamarray = array();

    if($sport_array[$sportid]['end_year_plus']){
        $year_display = $year . '-' . ( $year + 1 );
        $date_end = ( $year + 1 ) . $sport_array[$sportid]['end_date'];
    }

    if($date){
        $date_end = $date;
    } else if ($date_end > $today){
        $date_end = $today;
    }

    $results = $my_db->query("SELECT `teamid` FROM `team` WHERE `jv` = '0' AND `teamid` > 0;");

    while ($row = $results->fetchRow(MDB2_FETCHMODE_ASSOC)){
        $teamid = $row['teamid'];

        if(!array_key_exists($teamid, $rpiteamarray)){
            $rpiteamarray[$teamid] = new rpiteam($teamid, $sportid, $year, $my_db, $date_start, $date_end);
        }
        if($rpiteamarray[$teamid]->total_games() == 0){
            continue; 
        }
        $rpi[$teamid]['numgames'] = $rpiteamarray[$teamid]->total_games();
    }
    unset($results, $row);

    foreach($rpi as $teamid => $value){
        $rpi[$teamid]['wp'] = $rpiteamarray[$teamid]->wp();
        $rpi[$teamid]['owp'] = $rpiteamarray[$teamid]->owp($rpiteamarray);
        $rpi[$teamid]['oowp'] = $rpiteamarray[$teamid]->oowp($rpiteamarray);
    }

    return $rpi;
}
?>

<?php

class rpiteam {
    protected $sportid, $levelid, $levelid_state, $year,
              $wins, $loss, $tie, $total, $wp, $db,
              $opponentsarray, $date_start, $date_end;

    function __construct($teamid, $sportid, $year, $db, $date_start, $date_end){
        # You had three If statements here...just replace it with one
        if (empty($teamid) || empty($year) || empty($sportid)){
            return null;
        }

        $this->db = $db;
        $this->sportid = $sportid;
        $this->teamid = $teamid;
        $this->year = $year;
        $this->date_start = $date_start;
        $this->date_end = $date_end;

        $row = $this->db->query("SELECT `levelid`,`levelid_state` FROM `team_class` WHERE `teamid` = $teamid AND `sportid` = $sportid AND `year` = $year;")->fetchRow(MDB2_FETCHMODE_ASSOC);
        $this->levelid = $row['levelid'];
        $this->levelid_state = $row['levelid_state'];
        $this->opponentsarray = array();
    }

    public function wins(){
        if (!empty($this->wins)){
            return $this->wins;
        }

        $this->wins = $this->db->query("SELECT COUNT(`winner`) FROM `game` WHERE `sportid` = $this->sportid AND `winner` = $this->teamid AND `date` BETWEEN '$this->date_start' AND '$this->date_end';")->fetchOne();
        return $this->wins;
    }

    public function loss(){
        if (!empty($this->loss)){
            return $this->loss;
        }
        $this->loss = ($this->total_games())-($this->wins());
        return $this->loss;
    }

    public function total_games(){
        if (!empty($this->total)){
            return $this->total;
        }
        $this->total = $this->db->query("SELECT COUNT(`winner`) FROM `game` WHERE `sportid` = $this->sportid AND `winner` IS NOT NULL AND (`hometeam` = $this->teamid OR `awayteam` = $this->teamid) AND `date` BETWEEN '$this->date_start' AND '$this->date_end';")->fetchOne();
        return $this->total;
    }

    public function get_opponentsarray(){
        if (!empty($this->opponentsarray)){
            return $this->opponentsarray;
        }

        $result = $this->db->query("SELECT `hometeam`,`awayteam`,`winner` FROM `game` WHERE `sportid` = $this->sportid AND `winner` IS NOT NULL AND (`hometeam` = $this->teamid OR `awayteam` = $this->teamid) AND `date` BETWEEN '$this->date_start' AND '$this->date_end' ORDER BY `date` ASC;");
        while ($row = $result->fetchRow(MDB2_FETCHMODE_ASSOC)){
            if ($row['hometeam'] == $this->teamid){
                if ($row['winner'] == $this->teamid) $this->opponentsarray[$row['awayteam']] = 'W';
                elseif ($row['winner'] == 0) $this->opponentsarray[$row['hometeam']] = 'T';
                else $this->opponentsarray[$row['awayteam']] = 'L';
            } elseif ($row['awayteam'] == $this->teamid){
                if ($row['winner'] == $this->teamid) $this->opponentsarray[$row['hometeam']] = 'W';
                elseif ($row['winner'] == 0) $this->opponentsarray[$row['hometeam']]

Context

StackExchange Code Review Q#20282, answer score: 3

Revisions (0)

No revisions yet.