Thread: [cx-oracle-users] cx_Oracle execute() Performance

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

I am having an odd performance issue when executing an update statement
through cursor.execute() and could really use some insight.  The update
statement is very basic and the WHERE clause matches the one index on
the table so the execution time should be sub-second.  In fact, when I
run the update statement through Oracle SQL Developer on the same server
it completes in 0.001 seconds.  However, when I run the same statement
through cursor.execute() it consistently takes over 2 seconds.

I moved the update statement to a procedure and executed it using
cursor.callproc() and still experience the 2 second delay.  As before,
executing the procedure through Oracle SQL Developer is sub-second.

I also tried batching a set of 249 updates and using
cursor.executemany() yet still encounter the same delay.  The batch
update takes about 10 minutes using cursor.executemany(), but only two
seconds through Oracle SQL Developer.

One interesting thing is that inserting a record into the same table
using cursor.execute() and cursor.callproc() does not experience the 2
second delay.

Oracle Version:  11.2.0.1.0
Python Version: 2.6
Cx_Oracle Version: 5.0.4
OS: Windows Server Enterprise 2003 SP2

------------- Sample Code ------------------
import cx_Oracle
import time

con = cx_Oracle.connect("XXX", "XXX", "XXX")
con.autocommit = 1
print time.asctime( time.localtime(time.time()) ), "Oracle Version: ",
con.version
print time.asctime( time.localtime(time.time()) ), "Autocommit: " +
str(con.autocommit)

curRoute = con.cursor()

print time.asctime( time.localtime(time.time()) ), "Update Started."
curRoute.callproc("updateNeighbor", ('20913', 'US', 1, '89898', 'US',
12, 8))
print time.asctime( time.localtime(time.time()) ), "Update Complete"

print time.asctime( time.localtime(time.time()) ), "Insert Started."
curRoute.callproc("insertNeighbor", ('20913', 'US', 301, '78787', 'US',
12, 8))
print time.asctime( time.localtime(time.time()) ), "Insert Complete"

curRoute.close()
con.close()

print time.asctime( time.localtime(time.time()) ), "Processing Complete"

------------- Output From Sample Code ------------------
Wed Feb 16 13:38:23 2011 Oracle Version:  11.2.0.1.0
Wed Feb 16 13:38:23 2011 Autocommit: 1
Wed Feb 16 13:38:23 2011 Update Started.
Wed Feb 16 13:38:26 2011 Update Complete
Wed Feb 16 13:38:26 2011 Insert Started.
Wed Feb 16 13:38:26 2011 Insert Complete
Wed Feb 16 13:38:26 2011 Processing Complete