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

[Anthony T. <ant...@gm...>]

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?

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
>
> ------------------------------------------------------------
> ------------------
> Check out the vibrant tech community on one of the world's most
> engaging tech sites, Slashdot.org! http://sdm.link/slashdot
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>