Re: [cx-oracle-users] CX_ORACLE vs JAVA OCI performance

[Shai B. <sh...@pl...>]

Hi Kurt and Anthony,

When I first saw this, I thought it might be related to an issue I've touched 
on in the past -- the translation to numbers through strings when there is not 
enough info to know in advance that a C integral type would suffice. In 
particular, this fits in with the report of math operations causing slow-downs, 
as Oracle tends not to report limits on precision of calculated columns.

However, the report is still odd -- NUMBER (*,8), NUMBER(*,18) and 
NUMBER(*,38) all have precision 38, they only differ on scale. The report would 
make perfect sense to me if the types were actually NUMBER (8) [fits within 32-
bit int, although cx-Oracle treats it the same as...] NUMBER (18) [fits within 
64-bit int] and NUMBER(38) [requires a Python Long or Decimal].

Shai.

On Saturday 06 February 2016 04:31:38 Anthony Tuininga wrote:
> Hi Kurt,
> 
> That is quite surprising but I'd love to get to the bottom of why that
> behavior is the way it is!  Please do send me the DDL and the Python code
> you used to produce those results. If you have the Java code as well, that
> would be helpful -- so I can replicate the situation on my own machine.
> Thanks!
> 
> Anthony
> 
> On Fri, Feb 5, 2016 at 4:49 PM, kurtosis kurtosis.org
> <kur...@ku...
> 
> > wrote:
> > 
> > Hi Anthony,
> > 
> > First, let me thank you for the effort you have put into cx_Oracle.  It’s
> > a great product and very useful.
> > 
> > I have been doing some performance testing lately and   I’ve noticed some
> > interesting differences in the way cx_Oracle performs versus other
> > platforms (Java,  C#)  and wanted to let you know about them.
> > 
> > First the good news – cx_Oracle is very fast in certain situations, more
> > than twice as fast than Java OCI.  In other situations, it performs much
> > worse.
> > 
> > To do my testing, I created a table of numbers in different
> > configurations.   Each table was 500 columns with 10,000 rows.  I created
> > tables with different types:  NUMBER(*,8), NUMBER(*,18), NUMBER(*,38),
> > and BINARY_DOUBLE.
> > 
> > For NUMBER (*,8), NUMBER(*,18), and NUMBER(*,38) performance is great.  I
> > did 250 select * from the tables, below is the average time in seconds it
> > takes to get results back:
> > 
> > *TABLE*
> > 
> > *PYTHON*
> > 
> > *JAVA*
> > 
> > *tbl_n8*
> > 
> > 1.0513
> > 
> > 2.5052
> > 
> > *tbl_n18*
> > 
> > 1.1196
> > 
> > 2.7348
> > 
> > *tbl_bd*
> > 
> > 0.8423
> > 
> > 1.8668
> > 
> > However when we go to NUMBER(*,38)  performance is much worse:
> > 
> > *TABLE*
> > 
> > *PYTHON*
> > 
> > *JAVA*
> > 
> > *tbl_n38*
> > 
> > 8.5739
> > 
> > 2.8539
> > 
> > I then tested some views in various configurations.  Notably I found that
> > any time one does a math operation in a sql statement cx_Oracle slows
> > down.   Using TRUNC(col, 18) to downcast precision, same result.
> > 
> > *VIEW*
> > 
> > *PYTHON*
> > 
> > *JAVA*
> > 
> > *v_t18*
> > 
> > 9.2087
> > 
> > 3.3632
> > 
> > *v_f18*
> > 
> > 9.8412
> > 
> > 3.3678
> > 
> > I may be doing something wrong, but from my perspective, I’m just using
> > the library in the usual way.   If you are interested, I’m happy to send
> > along the ddl and py code I used to produce these results.
> > 
> > Thanks,
> > 
> > --Kurt
> > 
> > 
> > -------------------------------------------------------------------------
> > ----- Site24x7 APM Insight: Get Deep Visibility into Application
> > Performance APM + Mobile APM + RUM: Monitor 3 App instances at just
> > $35/Month Monitor end-to-end web transactions and take corrective
> > actions now Troubleshoot faster and improve end-user experience. Signup
> > Now! http://pubads.g.doubleclick.net/gampad/clk?id=272487151&iu=/4140
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users