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

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

Hello Vladimir,

There is another way to insert data into the table which does not 
require you to call setvalue().
The way is first you insert an empty CLOB into the table. Then you fetch 
the CLOB variable for update and write data into the CLOB.
I can see better performance in this case.
I have demonstrated the usage below:-
/
//#create table  table_clob ( msg_id number, message CLOB)/

/import cx_Oracle//
//import time//
//con = cx_Oracle.connect("scott/tiger@inst1")//
//
//num_records = 100//
//cur = con.cursor()//
//text = '$'*2**20//
//rows =[]//
//
//start_time = time.time()//
//
//for id in range(num_records)://
//    rows.append([id+1])//
//
//cur.bindarraysize = num_records//
//cur.executemany("insert into table_clob (msg_id, message) values (:1, 
empty_clob())", rows)//
//
//selstmt = "select message from table_clob where msg_id between 1 AND 
:1 for update"//
//cur.execute(selstmt, [num_records])//
//for id in range(num_records)://
//    results = cur.fetchone()//
//    results[0].write(text)//
//
//con.commit()//
//
//end_time =  time.time()//
//print end_time -start_time//
//cur.close()//
//con.close()/

I hope this will help you solve your performance problem.

Best Regards,
Avinash
On 7/16/2014 2:30 AM, Vladimir Ryabtsev wrote:
>
> 2014-07-14 16:35 GMT+04:00 avinash nandakumar 
> <avi...@or... <mailto:avi...@or...>>:
>
>     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
>
>
> Hello Avinash,
>
> is it really necessary to call OCILobTemporary() in OCI level? Do you 
> consider implementation of cx_Oracle correct?
>
> If I write my code in Java (for example) it works much faster, that's 
> why I suppose the problem is in cx_Oracle. I see the difference in 
> performance only in case I work CLOBs, performance is comparable if I 
> use simple data types.
>
>
>
> ------------------------------------------------------------------------------
> Want fast and easy access to all the code in your enterprise? Index and
> search up to 200,000 lines of code with a free copy of Black Duck
> Code Sight - the same software that powers the world's largest code
> search on Ohloh, the Black Duck Open Hub! Try it now.
> http://p.sf.net/sfu/bds
>
>
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users