byterock

In part 11 we had a look at a look at getting values back from SPs (stored procedures) now we are going to look a another variation on binding the array_execute.

One of Those Days

You know what its like. You have just been handed a requirement to store 200 individual selections from a web page. Well as a diligent programer with you now epic DBI skills you come up with this

my $sth = $dbh->prepare("INSERT INTO picks (user_id, pick_id) VALUES(:p_user_id, :p_pick_id)");

for (my $index=0;$index $sth->bind_param(':p_user_id', $user_id);
$sth->bind_param(':p_pick_id', $picks[$index]);
$sth->execute();
}

Well at least you know it works but you may be sucking up a good deal or resources with an execute at each iteration and bind as well any error along the line could cause all the data sent to the DB to be lost.

array_execute to the Rescue

Fortunately DBI has the bind_array which works along with the array_execute that could save your day as it allows you to execute an sql over a list of values , plus we can set things up so that any errors that may occur can be returned after execution rather that halting the process mid way.

So to use it we keep the prepare as it is;

my $sth = $dbh->prepare("INSERT INTO picks (user_id, pick_id) VALUES(:p_user_id, :p_pick_id)");

Now we bind with the 'bind_param_array' method like this;

$sth->bind_param_array(':p_user_id', $user_id);
$sth->bind_param_array(':p_pick_id', \@picks);

Note how I can use the bind_param_array with the scalar $user_id and DBI will treat it like an array of all the same value. Also note that I am passing a reference to the '@picks' not the array itself.

Next I make sure the error trapping is on

$dbh->{RaiseError} = 1;

and finally I do my execute array

$sth->execute_array( { ArrayTupleStatus => \my @tuple} );

In which I am using the 'ArrayTupleStatus' attribute so any results are going to be stored in my 'tuple' array.

So our code now looks like this;

my $sth = $dbh->prepare("INSERT INTO picks (user_id, pick_id) VALUES(:p_user_id, :p_pick_id)");
$sth->bind_param_array(':p_user_id', $user_id);
$sth->bind_param_array(':p_pick_id', \@picks);
$dbh->{RaiseError} = 1;
$sth->execute_array( { ArrayTupleStatus => \my @tuple} );

Now you can iterate over the '@tuple' and see what happened, and for a tuple that succeeded you should get a 'rowcount' or '-1' and for any that erred you will find an array ref which should contain the error, error string and sate. Of course what is returned is controlled by the driver so results may vary.

Now Some Sad News

Before you run out and convert all of your similar code to the pattern above you should check your DBD to see if it has other than a default DBI implementation. DBI's default is, well, really nothing more than code shorthand, it will at least save you the trouble of writing the for loop. It could be the case that the execute_array is in fact slower.

Now Some Good News

Some DBDs, DBD::Oracle for example, can give you blinding speed, sometimes even faster than native import modules and sometimes you may have extra attributes (ora_array_chunk) that can be used to tweak to get even more speed.

One test I did some time ago gave me results like this on a 2 million+ row insert.

SQL*Loader 2+ hours
DBD::Oracle without extended 'execute_array' support 2+ hours
DBD::Oracle with extended 'execute_array' support 25 mins
DBD::Oracle with extended 'execute_array' support tweaked with ora_array_chunk 17 mins

I will have to take a peek about someday and see which DBDs support this sort of thing??