Re: [cx-oracle-users] Performance of objects

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

On 30 Aug 2017, at 1:09, Anthony Tuininga wrote:

> Hi Walter,
>
> I just tried this on Linux with 12.2 Client and Database. I got these
> results:
>
> table 1.1217586994171143
> object 0.8592920303344727
>
> When I change the number of iterations from 1300 to 1200 I get this:
>
> table 0.996464729309082
> object 0.747194766998291
>
> So it seems consistent. Are you able to try with a different
> database/client scenario to see if the issues you are seeing are version
> specific?

I retried on Linux with the same database:

In [1] ▸ import cx_Oracle
In [2] ▸ cx_Oracle.clientversion()
Out[3] ▸ (11, 2, 0, 2, 0)
In [4] ▸ cx_Oracle.version
Out[4] ▸ '6.0.1'
In [5] ▸ db = cx_Oracle.connect("user/pwd@db")
In [6] ▸ db.version
Out[6] ▸ '11.2.0.1.0'

With 1200 iterations I get:

table 0.3604605197906494
object 0.8905477523803711
equal True

With 1300 iterations I get:

table 0.3804595470428467
object 42.898988246917725
equal True

The strange thing is that

   c.execute("select test_events2 from dual")

takes 0.2 seconds and

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

takes 0.1 seconds, but accessing the attributes takes 42 seconds.

I also tried the script on Linux against a 12.1.0.2.0 database. The output:

table 0.5058784484863281
object select 0.20530128479003906
object fetch 0.10213565826416016
object convert 110.43995141983032
object 110.74747037887573
equal True

So maybe this problem is specific to the client version?

Or it happens on all versions, but the threshold is higher?

> Anthony
>
> On Thu, Aug 24, 2017 at 11:28 AM, Walter Dörwald <wa...@li...>
> wrote:
>
>> 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

Servus,
   Walter