Passionate about data

Data and its implications on software design and development.

Ruby OCI 2.0 Array Binding

We have been doing some data moving lately using Ruby and Ruby-OCI. We started with Ruby OCI 1.0 and did use prepared statements with bind variables (since we are using oracle database and pulling data from an oracle database and pushing data to an oracle database). Later we found this really cool feature in Ruby-OCI8 2.0 where you can bind a whole array and just make one database trip for many database operations.

Lets say you want to insert 10 rows, using the insert one row at a time would be 10 trips to the database.

1
2
3
4
5
6
7
8
9
def save_accounts(accounts)
    stmt = $connection.parse "INSERT INTO account (accountid,name) values (:account_id,:name)"
      accounts.each do |account|
        stmt.bind_param(:account_id, account[0], Float)
        stmt.bind_param(:name, account[1], String)
        stmt.exec
      end
      $connection.commit
    end

Using the array bind feature, its actually just one trip to the database (off course depends on the array size you are going to bind, but you get the picture, it reduces database trips)

1
2
3
4
5
6
7
8
def save_accounts(account_ids, account_names)
      stmt = $connection.parse "INSERT INTO account (accountid,name) values (:account_id,:name)"
      stmt.max_array_size= account_ids.size
      stmt.bind_param_array(:account_id, account_ids)
      stmt.bind_param_array(:name, account_names)
      stmt.exec_array
      $connection.commit
    end

We saw a 100% improvement in performance by changing the way we bind the variables in just one place. Looks like a feature to look out for.