Bulk Inserts and the Performance Holy Grial (Part II)

Hi,

Back to our topic (see previous post) about bulk inserts using PHP and Oracle OCI library, the first approach I'll explain today is as follows:

Public method bulk_insert

public function bulk_insert($common, $data)
{
    try {
        $params  = $this->prepareBulkInsertParams($common, $data);
        return $this->ociBulkInsert($params);
    } catch (Exception $e) {
        $this->logger->log(ERROR, $e->getMessage());
    }
}

Neat, isn't it? At a first glance, a much clearer method is the result of a first refactor. Many of the code pieces have been extracted to a new method (Extract method, Refactoring, K. Beck) alongside with new ones to give support to the PL/SQL batch insert syntax.

Preparing the Parameters

This method is just for extracting the incoming data and transform it into a more manageable array.

private function prepareBulkInsertParams(array $common,
                                             array $data) : array
{
    extract($common);
    $params = [];
    $row    = 0;
    foreach ($data as $columns) {
        extract($columns);
        $params[$row]['txn']       = $txn;
        $params[$row]['service']   = $service;
        $params[$row]['code']      = ($customer) ? $usercode : 'N/A';
        $params[$row]['userid']    = $userid;
        $params[$row]['pricecode'] = $pricecode;
        $params[$row]['amount']    = $amount;
        $params[$row]['status']    = self::OPEN;
        $row++;
    }
    return $params;
}

Going a little bit futher we can re-write the method as below, since it's more straight forward:

private function prepareBulkInsertParams(array $common,
                                             array $data) : array
{
    if (empty($data) || empty($common)) {
        throw new Exception('Empty user data');
    }
    $params = [];
    foreach ($data as $row => $columns) {
        $columns['code'] = $this->workoutCode($columns);
        // equivalent to array_merge($common, $columns);
        $params[$row]           = $common + $columns;
        $params[$row]['status'] = self::OPEN;
    }
    return $params;
}

private function workoutCode(array &$columns) : string
{
    $code = $columns['customer'] === true ? $columns['usercode'] : 'N/A';
    unset($columns['customer']);
    unset($columns['usercode']);
    return $code;
}

Dealing with OCI

So now, a better organised parameter structure is available to work with, and, as a result, a new method is used to deal with the actual insertion:

private function ociBulkInsert(array $params)
{
    $r   = false;
    $sql = $this->prepareBulkInsertSQL($params);
    try {
        $stmt = $this->bindParams($sql, $params);
        oci_execute($stmt, OCI_NO_AUTO_COMMIT);
        $r    = oci_commit($this->db->connid);
    } catch (Exception $e) {
        $this->logger->log(ERROR, 'bulk_insert EXCEPTION: '.$e->getMessage());
        oci_rollback($this->db->connid);
    } finally {
        oci_free_statement($stmt);
    }
    return $r;
}

PL/SQL batch insert

The method above is in charge of doing all the closer-related-DB tasks. Firstly, we generate the SQL, see below:

private function prepareBulkInsertSQL(array $bindings)
{
    $sql    = 'INSERT ALL'."\n";
    $first  = array_shift(array_values($bindings));
    $header = array_filter(array_keys($first));
    $into   = implode(',',
                      array_map(function ($field) {
            return '"'.$field.'"';
        }, $header));
    foreach (array_keys($bindings) as $key) {
        $placeholders = implode(',',
                                array_map(function ($field) use ($key) {
                return ':'.$field.$key;
            }, $header));
        $sql .= 'INTO "'.$this->table_name.'" ('.$into.') '
            .'VALUES ('.$placeholders.')'."\n";
    }
    $sql .= 'SELECT 1 FROM dual'."\n";
    return $sql;
}

Apologies for this cryptic method, essentially, what it does is to generate a PL/SQL batch insert statement given the structured parameters:

INSERT ALL
INTO "usertxn" ("txn","service","code" ... ) VALUES (:txn0, :service0, :code0 ... )
INTO "usertxn" ("txn","service","code" ... ) VALUES (:txn1, :service1, :code1 ... )
INTO "usertxn" ("txn","service","code" ... ) VALUES (:txn2, :service2, :code2 ... )
...
SELECT 1 FROM DUAL

You get the idea.

Parameter binding

And finally the parameter binding:

private function bindParams($sql, array $params)
{
    try {
        $stmt = oci_parse($this->db->connid, $sql);
        foreach ($params as $row => $data) {
            foreach ($data as $field => $value) {
                ${$field.$row} = $value;
                oci_bind_by_name($stmt, ':'.$field.$row, ${$field.$row});
            }
        }
    } catch (Exception $e) {
        $this->logger->log(ERROR, 'BindParams Exception: '.$e->getMessage());
        return false;
    }
    return $stmt;
}