Hi all,
I am busy optimising my code and wonder what DBI does internally.
At the moment I have something like this
FUNCTION
my $lcsr = $db->prepare(<<EOS) || ataerr::dbprepare($db);
BEGIN
STORED_PROC.GET_SERVICE_BY_ID(:service_id, :last_modified,
:base_prod_id, :person_id, :operator_id);
END;
EOS
$lcsr->bind_param(":service_id", $service_id, 2);
$lcsr->bind_param_inout(":last_modified", \$last_modified, 12);
$lcsr->bind_param_inout(":base_prod_id", \$base_product_instance_id, 2);
$lcsr->bind_param_inout(":person_id", \$person_id, 2);
$lcsr->bind_param_inout(":operator_id", \$operator_id, 2);
$lcsr->execute() || ataerr::dbexecute($db);
$lcsr->finish;
END FUNCTION
I might be calling this thousands of times.
The questions is: will this piece of code prepare the stored procedure for
every call? Would it be quicker to prepare the cursor only once and then
call only the binds and execute? Or is DBI doing this for me?
So the code would be something like
INIT_FUNCTION
my $lcsr = $db->prepare(<<EOS) || ataerr::dbprepare($db);
BEGIN
STORED_PROC.GET_SERVICE_BY_ID(:service_id, :last_modified,
:base_prod_id, :person_id, :operator_id);
END;
EOS
END FUNCTION
EXEC FUNCTION
$lcsr->bind_param(":service_id", $service_id, 2);
$lcsr->bind_param_inout(":last_modified", \$last_modified, 12);
$lcsr->bind_param_inout(":base_prod_id", \$base_product_instance_id, 2);
$lcsr->bind_param_inout(":person_id", \$person_id, 2);
$lcsr->bind_param_inout(":operator_id", \$operator_id, 2);
$lcsr->execute() || ataerr::dbexecute($db);
END FUNCTION
TERM FUNCTION
$lcsr->finish;
END FUNCTION
I call INIT and TERM only once
If there is a speed improvement, would it be significant?
Thanks for any insight,
Jeroen