[cx-oracle-users] Performance of objects
Brought to you by:
atuining
|
From: Walter <wa...@li...> - 2017-08-24 17:57:21
|
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
|