#13 OTL_ORA_TIMESTAMP slowness (fixed)

[John van der Pol]

Dear Sergei,

We have been using OTL for over 10 years now. Lately we had a performance degradatyion when using OTL_ORA_TIMESTAMP. Profiling the code I found thgat the time was consumed in OCIDescriptorAlloc.
We bulk load 500 rows at a time and this resulted in our case into 7*500 calls to this function, which is kinda slow.
OCI however has a solution for it: OCIArrayDescriptorAlloc.
After modifying the code, the performance was perfect again.
the same optiomization can also be used for blobs

if (defined(OTL_ORA8I)||defined(OTL_ORA9I))&&defined(OTL_ORA_TIMESTAMP)
}else if((ftype==otl_var_timestamp ||
ftype==otl_var_tz_timestamp ||
ftype==otl_var_ltz_timestamp) &&
!apl_tab_flag){
array_size=aarray_size;
elem_size=sizeof(OCIDateTime);
act_elem_size=elem_size;
timestamp=new OCIDateTime[array_size];
p_v=OTL_RCAST(ub1,timestamp);
p_ind=new sb2[array_size];
p_rlen=new ub2[array_size];
p_rcode=new ub2[array_size];
for(i=0;i<array_size;++i){ p_ind<span="">[i]=OTL_SCAST(short,elem_size); p_rlen[i]=OTL_SCAST(unsigned short,elem_size); p_rcode[i]=0; } if(connect!=nullptr){ otl_datetime dt; ub4 dtype=0; switch(ftype){ case otl_var_timestamp: dtype=OCI_DTYPE_TIMESTAMP; break; case otl_var_ltz_timestamp: dtype=OCI_DTYPE_TIMESTAMP_LTZ; break; case otl_var_tz_timestamp: dtype=OCI_DTYPE_TIMESTAMP_TZ; break; } OCIArrayDescriptorAlloc //much faster (OTL_RCAST(dvoid*,connect->envhp),
OTL_RCAST(dvoid,&timestamp[0]),
dtype,
array_size,
0,
nullptr);
for(i=0;i<array_size;++i){ *="" OCIDescriptorAlloc="" (OTL_RCAST(dvoid*,connect-="">envhp),
OTL_RCAST(dvoid,&timestamp[i]),
dtype,
0,
nullptr);/
write_dt(timestamp[i],&dt,1);
}
}else
timestamp=nullptr;

endif

if (defined(OTL_ORA8I)||defined(OTL_ORA9I))&&defined(OTL_ORA_TIMESTAMP)

if((ftype==otl_var_timestamp ||
ftype==otl_var_tz_timestamp ||
((ftype==otl_var_ltz_timestamp)&&
timestamp!=nullptr))){
ub4 dtype=0;
switch(ftype){
case otl_var_timestamp:
dtype=OCI_DTYPE_TIMESTAMP;
break;
case otl_var_ltz_timestamp:
dtype=OCI_DTYPE_TIMESTAMP_LTZ;
break;
case otl_var_tz_timestamp:
dtype=OCI_DTYPE_TIMESTAMP_TZ;
break;
}
OCIArrayDescriptorFree(OTL_RCAST(dvoid,&timestamp),dtype);//much faster
/ for(i=0;i<array_size;++i)
OCIDescriptorFree(OTL_RCAST(dvoid,timestamp[i]),dtype);
*/
}

endif

Hope this ticket/fix is helpfull for opthers too.

Greets John van der Pol