David Beroff (d4b) wrote,
David Beroff
d4b

Optimizing PHP / PDO / MySQL

The investment firm for whom I develop software closes their books monthly, and there are generally some tweaks that I need to perform just before each event, as the algorithms continue to evolve. I had to add a brand new screen with about 70 fields last month, and I was under the gun to get it out the door quickly, so I used this to upload the data from the screen to the MySQL database:
    $stmt = $db->prepare( "INSERT INTO Allocation (ParameterID, Rate) " . 
                          "VALUES (:ParameterID, :Rate);" );

    foreach ($screen as $key => $value) {
        $stmt->execute( array(  ":ParameterID" => $key, 
                                ":Rate"        => $value ) );
    }

It was simple and straight-forward, but horribly inefficient, taking about 20 seconds to upload the 70 key/value pairs. Worse, the Rackspace Cloud Sites hosting that we use automatically times out web pages after 30 seconds, meaning that this was coming dangerously close to simply dumping the data onto the floor. This month, I had more time to put some focused thought into it, and optimize the code as follows; it quietly gathers ten pairs at a time before uploading, and now (predictably) runs ten times as quickly:
function saveSome( $db, $some ) {
    try {
        if (count( $some ) == 0)
            return TRUE;

        $sql = "INSERT INTO Allocation (ParameterID, Rate) VALUES ";
        $np = array();
        $i = 0;

        foreach ($some as $key => $value) {
            if ($i > 0)
                $sql .= ", ";

            $sql .= "(:ParameterID$i, :Rate$i)";

            $np[ ":ParameterID$i" ] = $key;
            $np[ ":Rate$i"        ] = $value;

            $i++;
        }

        $sql .= ";";
        $stmt = $db->prepare( $sql );
        $stmt->execute( $np );
        return TRUE;

    } catch (PDOException $e) {
        fail( $e->getMessage() );
    }

    return FALSE;
}
    
function saveAll( $db, $screen ) {
    try {

        $some = array();
        foreach ($screen as $key => $value) {
            $some[ $key ] = $value;
            if (count( $some ) >= 10) {
                // Save a subset and start a new one:
                if (saveSome( $db, $some ) === FALSE)
                    return FALSE;

                $some = array();
            }
        }

        // Save final subset (if any):
        return saveSome( $db, $some );

    } catch (PDOException $e) {
        fail( $e->getMessage() );
    }

    return FALSE;
}
Tags: bic, software
Subscribe
  • Post a new comment

    Error

    default userpic

    Your reply will be screened

    Your IP address will be recorded 

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.
  • 0 comments