[cx-oracle-users] Performance of objects

[Walter <wa...@li...>]

Hello all!

Now that cx_Oracle supports custom Oracle object types I tried them and 
discovered strange performance fluctuations. I defined the following 
types and functions in the database:

---------------------------------------------

create or replace type ul4onevent as object
(
	ue_type varchar2(20),
	ue_backref integer,
	ue_int1 integer,
	ue_int2 integer,
	ue_int3 integer,
	ue_number number,
	ue_date date,
	ue_str varchar2(32000)
);
/

create or replace type ul4onevents as table of ul4onevent;
/

create or replace function test_events2
return ul4onevents
as
	v_events ul4onevents;

	procedure append(
		p_ue_type varchar2,
		p_ue_backref integer := null,
		p_ue_int1 integer := null,
		p_ue_int2 integer := null,
		p_ue_int3 integer := null,
		p_ue_number number := null,
		p_ue_date date := null,
		p_ue_str varchar2 := null
	)
	as
	begin
		v_events.extend;
		v_events(v_events.count) := ul4onevent(p_ue_type, p_ue_backref, 
p_ue_int1, p_ue_int2, p_ue_int3, p_ue_number, p_ue_date, p_ue_str);
	end;
begin
	v_events := ul4onevents();

	append('beginlist');
		for i in 1 .. 1300 loop
			append('begindict');
				append('str', p_ue_str=>'firstname');
				append('str', p_ue_str=>'Foo');
				append('str', p_ue_str=>'lastname');
				append('str', p_ue_str=>'Bar');
				append('str', p_ue_str=>'none');
				append('none');
				append('str', p_ue_str=>'bool');
				append('bool', p_ue_int1=>1);
				append('str', p_ue_str=>'int');
				append('int', p_ue_int1=>42);
				append('str', p_ue_str=>'float');
				append('number', p_ue_number=>42.5);
				append('str', p_ue_str=>'date');
				append('date', p_ue_date=>to_date('29.02.2000', 'DD.MM.YYYY'));
				append('str', p_ue_str=>'color');
				append('color', p_ue_int1=>845453000);
				append('str', p_ue_str=>'timedelta');
				append('timedelta', p_ue_int1=>0, p_ue_int2=>42, p_ue_int3=>0);
				append('str', p_ue_str=>'monthdelta');
				append('monthdelta', 17);
				append('str', p_ue_str=>'slice');
				append('slice', p_ue_int1=>17, p_ue_int2=>23);
			append('enddict');
		end loop;
	append('endlist');
	return v_events;
end;
/

---------------------------------------------

I used the following script to compare the performance of

	select * from table(test_events2)

and

	select test_events2 from dual

---------------------------------------------

import time
import cx_Oracle

db = cx_Oracle.connect("user/pwd@db")
c = db.cursor()

t1 = time.time()

e1 = []
c.execute("select * from table(test_events2)")
for r in c:
	e1.append(tuple(r))

t2 = time.time()

e2 = []
c.execute("select test_events2 from dual")

objs = c.fetchone()[0].aslist()

for obj in objs:
	e2.append((
		obj.UE_TYPE,
		obj.UE_BACKREF,
		obj.UE_INT1,
		obj.UE_INT2,
		obj.UE_INT3,
		obj.UE_NUMBER,
		obj.UE_DATE,
		obj.UE_STR,
	))

t3 = time.time()

print("table", t2-t1)
print("object", t3-t2)
print("equal", e1 == e2)

---------------------------------------------

The output is:

---------------------------------------------

table 0.4585990905761719
object 106.14051795005798
equal True

---------------------------------------------

i.e. the object version is 230 times slower than the table version. When 
I change the number of iterations from 1300 to 1200 the output is:

---------------------------------------------

table 0.4336049556732178
object 1.2956831455230713
equal True

---------------------------------------------

I wonder why that happens.

The database version is 11.2.0.1.0 and I'm using Python 3.6.2 with 
cx_Oracle 6.0.1 on Mac OS X 10.12.6.

Servus,
    Walter