Re: [cx-oracle-users] : CLOB performance issue

[avinash n. <avi...@or...>]

Hello Vladimir,

It looks like in the current code the variable.setvalue() function for 
LOBs uses OCILobTemporary() which is a call to the server.
100 MB would be transferred to the server during this call. Hence the 
call appears to take more time at the time of filling.
Subsequent execute() will just be adjusting the descriptors in the table 
in the db.
End to end there should not be any additional processing.

Best Regards,
Avinash

-------- Original Message --------
> Subject: 	[cx-oracle-users] CLOB performance issue
> Date: 	Wed, 25 Jun 2014 17:57:58 +0400
> From: 	Vladimir Ryabtsev <gre...@gm...>
> Reply-To: 	cx-...@li...
> To: 	cx-...@li...
>
>
>
> Hi.
>
> I have encountered a performance problem when inserting values into a 
> column of CLOB datatype. For example I have a table:
>
> create table table_clob (msg_id number, message clob)
>
> Here is my code to reproduce the issue:
>
> -----
> import time
> import cx_Oracle
> import sys
>
> print('Python', sys.version, 'on', sys.platform)
> print('cx_Oracle.version:', cx_Oracle.version)
> num_records = 100
> con = cx_Oracle.connect('user/password@sid')
> print('DB version:', con.version)
> cur = con.cursor()
> cur.prepare("insert into table_clob (msg_id, message) values (:msg_id, 
> :msg)")
> cur.bindarraysize = num_records
> msg_arr = cur.var(cx_Oracle.CLOB, arraysize=num_records)
> text = '$'*2**20 Â  Â # 1 MB of text
> rows = []
>
> # Prepare data.
> start_time = time.perf_counter()
> for id in range(num_records):
> Â  Â  msg_arr.setvalue(id, text)
> Â  Â  rows.append( (id, msg_arr) )
>
> # Insert data.
> print('{} records prepared, {:.3f} s'
> Â  Â  Â  .format(num_records, time.perf_counter() - start_time))
> start_time = time.perf_counter()
> cur.executemany(None, rows)
> con.commit()
> print('{} records inserted, {:.3f} s'
> Â  Â  Â  .format(num_records, time.perf_counter() - start_time))
>
> cur.close()
> con.close()
> -----
>
> which yields the results:
>
> -----
> Python 3.3.2 (v3.3.2:d047928ae3f6, May 16 2013, 00:06:53) [MSC v.1600 
> 64 bit (AMD64)] on win32
> cx_Oracle.version: 5.1.3
> DB version: 11.2.0.1.0
> 100 records prepared, 5.384 s
> 100 records inserted, 4.536 s
> -----
>
> Versions of my DB and my client are the same.
>
> What's worrying me is the time of preparation of msg_arr structure. 5 
> seconds is abnormally much for copying of 100 MB in memory. 4.5 
> seconds seems OK for sending it over the network.
> The problematic step is msg_arr.setvalue(id, text). If I comment it, 
> script takes just milliseconds to complete (inserting null into CLOB 
> column of course).
> I also noticed a persistent disk activity during the "prepare" stage. 
> Perhaps cx_Oracle library stores some data in temporary files on disk? 
> This could be the reason of the problem...
>
> I wonder if there is a way to improve performance of "preparation" 
> part of my code.
>
> Thanks in advance for your help.
>
>