Re: [cx-oracle-users] Performance of objects
Brought to you by:
atuining
Menu
▾
▴
Re: [cx-oracle-users] Performance of objects
|
From: Anthony T. <ant...@gm...> - 2017-09-04 21:21:56
|
On Wed, Aug 30, 2017 at 3:40 AM, Walter Dörwald <wa...@li...>
wrote:
> 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?
>
I just tried with 11.2 client and I get similar responses to yours.
1200:
table 0.9072585105895996
object 0.710052490234375
1300:
table 1.0106935501098633
object 116.8638014793396
With 12.1 client I also get similar numbers:
1200:
table 0.9160842895507812
object 0.7757439613342285
1300:
table 1.0204412937164307
object 118.60206842422485
So apparently, this was a performance issue resolved in 12.2 client. If you
can upgrade, do it!
Anthony
>
> > 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
>
> ------------------------------------------------------------
> ------------------
> 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
>
|
