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

[Vladimir R. <gre...@gm...>]

Hello avinash,

Very weird workaround but it is really solution! It works 60% faster than
my variant. Thanks.

But it seems, in general, this way has some disadvantages:
- you must be able to select just inserted records fast (i.e. you must have
indexes) otherwise performance advantage will be lost;
- extra locks on table records;
- more complex logic of program.

Although this points may not be essential, this is actually a sort of trick
trick and I believe authors should somehow fix the library to make it usage
more straighforward.


2014-07-18 13:06 GMT+04:00 avinash nandakumar <avi...@or...
>:

> 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()*