[cx-oracle-users] CLOB performance issue
Brought to you by:
atuining
|
From: Vladimir R. <gre...@gm...> - 2014-06-25 13:58:27
|
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.
|