develooper Front page | perl.dbi.users | Postings from January 2021

DBD::Oracle insert speed

Thread Next
Peter Meszaros
January 10, 2021 00:23
DBD::Oracle insert speed
Message ID:
I have already asked this question at but I did not get
proper answer.

Module DBD::Oracle <> has
serious performance degradation at inserting rows. Except execute_array (or
bind_param_array), all other insert solutions are pretty slow (eg. bind by
name). DBD::Oracle use its own wrapper (dbimp.c, oci8.c) and seem to be
implemented in an ineffective way. Comparing to otlv4 ( which is a wrapper over Oracle's
OCI interface written in C++ the basic is much (~10 times) faster than
DBD::Oracle row based insertion. DBD::Oracle implements DATA_AT_EXEC mode
with OCIBindDynamic for row based insertion (not array) as can be seen
below and this block is repeated for each value.

dbd_bind_ph(1): bind :id <== '1774684594' (type 0 (DEFAULT (varchar)))
dbd_rebind_ph() (1): rebinding :id as '17746...' (not-utf8, ftype 1
(VARCHAR), csid 0, csform 0(0), inout 0)
dbd_rebind_ph_char() (1): bind :id <== '17746...' (size 10/12/0, ptype
3(VARCHAR), otype 1 )
Changing maxlen to 12
dbd_rebind_ph_char() (2): bind :id <== '17746...' (size 10/12, otype
1(VARCHAR), indp 0, at_exec 1)
      bind :id as ftype 1 (VARCHAR)
dbd_rebind_ph(): bind :id <== '17746...' (in, not-utf8, csid
873->0->873, ftype 1 (VARCHAR), csform 0(0)->0(0), maxlen 12,
maxdata_size 0)
    OCIAttrSet(163a970,OCI_HTYPE_BIND, 7ffda0f7f960,0,Attr=31,1614d20)=SUCCESS
[download] <;abspart=1;part=1;node_id=11126161>

Is this design intentional? OCI documentation said that OCIBindDynamic can
be useful at working with big data items
C++ code using otlv4 can process blob/clob data and it does not use
OCIBindDynamic at all. So, why DBD::Oracle implemented this way?

Thanks in advance

Thread Next Perl Programming lists via nntp and http.
Comments to Ask Bjørn Hansen at | Group listing | About