[cx-oracle-users] CX_ORACLE vs JAVA OCI performance
Brought to you by:
atuining
Menu
▾
▴
[cx-oracle-users] CX_ORACLE vs JAVA OCI performance
|
From: kurtosis kurtosis.o. <kur...@ku...> - 2016-02-06 00:07:09
|
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"><head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/> </head><body><p>Hi Anthony,</p><p>First, let me thank you for the effort you have put into cx_Oracle.  It’s a great product and very useful.</p><p>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.</p><p>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.</p><p>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.</p><p>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:</p><table style="line-height: 1em; margin: 0.5em auto;" border="0" cellspacing="0" cellpadding="0" class="mce-item-table"><tbody><tr><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #aaaaaa; padding: 0.4em 1em; border-top-width: 1px; border-top-style: solid; border-top-color: #555555;" width="56"><p><strong>TABLE</strong></p></td><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #aaaaaa; padding: 0.4em 1em; border-top-width: 1px; border-top-style: solid; border-top-color: #555555;" width="73"><p><strong>PYTHON</strong></p></td><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #aaaaaa; padding: 0.4em 1em; border-top-width: 1px; border-top-style: solid; border-top-color: #555555;" width="97"><p><strong>JAVA</strong></p></td></tr><tr><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #aaaaaa; padding: 0.4em 1em;" width="56"><p><strong>tbl_n8</strong></p></td><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #aaaaaa; padding: 0.4em 1em;" width="73"><p>1.0513</p></td><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #aaaaaa; padding: 0.4em 1em;" width="97"><p>2.5052</p></td></tr><tr><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #aaaaaa; padding: 0.4em 1em;" width="56"><p><strong>tbl_n18</strong></p></td><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #aaaaaa; padding: 0.4em 1em;" width="73"><p>1.1196</p></td><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #aaaaaa; padding: 0.4em 1em;" width="97"><p>2.7348</p></td></tr><tr><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #555555; padding: 0.4em 1em;" width="56"><p><strong>tbl_bd</strong></p></td><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #555555; padding: 0.4em 1em;" width="73"><p>0.8423</p></td><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #555555; padding: 0.4em 1em;" width="97"><p>1.8668</p></td></tr></tbody></table><p>However when we go to NUMBER(*,38)  performance is much worse:</p><table style="line-height: 1em; margin: 0.5em auto;" border="0" cellspacing="0" cellpadding="0" class="mce-item-table"><tbody><tr><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #aaaaaa; padding: 0.4em 1em; border-top-width: 1px; border-top-style: solid; border-top-color: #555555;" width="73"><p><strong>TABLE</strong></p></td><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #aaaaaa; padding: 0.4em 1em; border-top-width: 1px; border-top-style: solid; border-top-color: #555555;" width="62"><p><strong>PYTHON</strong></p></td><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #aaaaaa; padding: 0.4em 1em; border-top-width: 1px; border-top-style: solid; border-top-color: #555555;" width="97"><p><strong>JAVA</strong></p></td></tr><tr><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #555555; padding: 0.4em 1em;" width="73"><p><strong>tbl_n38</strong></p></td><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #555555; padding: 0.4em 1em;" width="62"><p>8.5739</p></td><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #555555; padding: 0.4em 1em;" width="97"><p>2.8539</p></td></tr></tbody></table><p>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.</p><table style="line-height: 1em; margin: 0.5em auto;" border="0" cellspacing="0" cellpadding="0" class="mce-item-table"><tbody><tr><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #aaaaaa; padding: 0.4em 1em; border-top-width: 1px; border-top-style: solid; border-top-color: #555555;" width="61"><p><strong>VIEW</strong></p></td><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #aaaaaa; padding: 0.4em 1em; border-top-width: 1px; border-top-style: solid; border-top-color: #555555;" width="68"><p><strong>PYTHON</strong></p></td><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #aaaaaa; padding: 0.4em 1em; border-top-width: 1px; border-top-style: solid; border-top-color: #555555;" width="97"><p><strong>JAVA</strong></p></td></tr><tr><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #aaaaaa; padding: 0.4em 1em;" width="61"><p><strong>v_t18</strong></p></td><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #aaaaaa; padding: 0.4em 1em;" width="68"><p>9.2087</p></td><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #aaaaaa; padding: 0.4em 1em;" width="97"><p>3.3632</p></td></tr><tr><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #555555; padding: 0.4em 1em;" width="61"><p><strong>v_f18</strong></p></td><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #555555; padding: 0.4em 1em;" width="68"><p>9.8412</p></td><td style="border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: #555555; padding: 0.4em 1em;" width="97"><p>3.3678</p></td></tr></tbody></table><p>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.  </p><p>Thanks,</p><p>--Kurt</p></body></html> |
