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

Reply via email to