[Zope-DB] DCOracle2 cursor.execute is getting incredible slow
wlang at wu-wien.ac.at
Wed Oct 27 12:50:04 EDT 2004
Hi Matt, Chris and all!
I'm using DCOracle2-1.3b with Oracle-8.1.7.
I found that under certain circumstances, the DCOracle2 cursor.execute
call gets slower and slower (to incredible slow).
What I did (schematically):
keys = get_all_primary_keys_from_table() # returns a few 1000 keys
dbc = DCOracle2.connect(...)
cursor = dbc.cursor()
for n, key in enumerate(keys):
t1 = time.time()
cursor.execute("select * from SAMPLE_TABLE where key = :1", key)
t2 = time.time()
logfile.write("%d %f\n" % (n, t2-t1))
row = cursor.fetchone() # primary keys return exactly one row
If I use gnuplot to display the logged data, i get the following graph:
The execution time starts with about 1ms and ends with 30ms (30 times
I tried to track down the time sink and found following path:
Each time Cursor_execute is called, this calls Cursor_getdesc, which
in turn calls OCIParamGet. And OCIParamGet gets slower on each call.
And now ...... my questions:
* Any ideas what causes OCIParamGet to get slower?
* Is it really necessary to call Cursor_getdesc for each
Cursor_execute call? The description for the cursor shouldn't
change, as long as there is no new Cursor_prepare call, isnt it?
Any help is very much appreciated!
PS: 2 more observation:
- if the table has many colums, the slope is higher. The following table
has 288 columns (and doubles the execution time after 150 executes):
- closing and reopening the cursor after every 100 executes helps:
Willi.Langenberger at wu-wien.ac.at Fax: +43/1/31336/9207
Zentrum fuer Informatikdienste, Wirtschaftsuniversitaet Wien, Austria
More information about the Zope-DB