Re: Performance of objects
Anthony Tuininga <[email protected]> Tue, 29 Aug 2017 17:09:03 -0600
| Newsgroups | gmane.comp.python.db.cx-oracle |
|---|---|
| Message-ID | <CAE1XR-71pXj=9Xw-=BNRZaFoimSTktv4jHBeQegAG4nB-Qiw8g@mail.gmail.com> |
--===============2207007277813296699== Content-Type: multipart/alternative; boundary="94eb2c0ef2205b30770557ec7f1b" --94eb2c0ef2205b30770557ec7f1b Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=C3=B6rwald <[email protected]= e> wrote: > Hello all! > > Now that cx_Oracle supports custom Oracle object types I tried them and > discovered strange performance fluctuations. I defined the following type= s > 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 :=3D null, > p_ue_int1 integer :=3D null, > p_ue_int2 integer :=3D null, > p_ue_int3 integer :=3D null, > p_ue_number number :=3D null, > p_ue_date date :=3D null, > p_ue_str varchar2 :=3D null > ) > as > begin > v_events.extend; > v_events(v_events.count) :=3D 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 :=3D ul4onevents(); > > append('beginlist'); > for i in 1 .. 1300 loop > append('begindict'); > append('str', p_ue_str=3D>'firstname'); > append('str', p_ue_str=3D>'Foo'); > append('str', p_ue_str=3D>'lastname'); > append('str', p_ue_str=3D>'Bar'); > append('str', p_ue_str=3D>'none'); > append('none'); > append('str', p_ue_str=3D>'bool'); > append('bool', p_ue_int1=3D>1); > append('str', p_ue_str=3D>'int'); > append('int', p_ue_int1=3D>42); > append('str', p_ue_str=3D>'float'); > append('number', p_ue_number=3D>42.5); > append('str', p_ue_str=3D>'date'); > append('date', > p_ue_date=3D>to_date('29.02.2000', 'DD.MM.YYYY')); > append('str', p_ue_str=3D>'color'); > append('color', p_ue_int1=3D>845453000); > append('str', p_ue_str=3D>'timedelta'); > append('timedelta', p_ue_int1=3D>0, > p_ue_int2=3D>42, p_ue_int3=3D>0); > append('str', p_ue_str=3D>'monthdelta'); > append('monthdelta', 17); > append('str', p_ue_str=3D>'slice'); > append('slice', p_ue_int1=3D>17, > p_ue_int2=3D>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 =3D cx_Oracle.connect("user/pwd@db") > c =3D db.cursor() > > t1 =3D time.time() > > e1 =3D [] > c.execute("select * from table(test_events2)") > for r in c: > e1.append(tuple(r)) > > t2 =3D time.time() > > e2 =3D [] > c.execute("select test_events2 from dual") > > objs =3D 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 =3D time.time() > > print("table", t2-t1) > print("object", t3-t2) > print("equal", e1 =3D=3D 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-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users > --94eb2c0ef2205b30770557ec7f1b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable <div dir=3D"ltr">Hi Walter,<div><br></div><div>I just tried this on Linux w= ith 12.2 Client and Database. I got these results:</div><div><br></div><div= ><div>table 1.1217586994171143</div><div>object 0.8592920303344727</div></d= iv><div><br></div><div>When I change the number of iterations from 1300 to = 1200 I get this:</div><div><br></div><div><div>table 0.996464729309082</div= ><div>object 0.747194766998291</div></div><div><br></div><div>So it seems c= onsistent. Are you able to try with a different database/client scenario to= see if the issues you are seeing are version specific?</div><div><br></div= ><div>Anthony</div></div><div class=3D"gmail_extra"><br><div class=3D"gmail= _quote">On Thu, Aug 24, 2017 at 11:28 AM, Walter D=C3=B6rwald <span dir=3D"= ltr"><<a href=3D"mailto:[email protected]" target=3D"_blank">walter@= livinglogic.de</a>></span> wrote:<br><blockquote class=3D"gmail_quote" s= tyle=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Hell= o all!<br> <br> Now that cx_Oracle supports custom Oracle object types I tried them and dis= covered strange performance fluctuations. I defined the following types and= functions in the database:<br> <br> ------------------------------<wbr>---------------<br> <br> create or replace type ul4onevent as object<br> (<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 ue_type varchar2(20),<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 ue_backref integer,<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 ue_int1 integer,<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 ue_int2 integer,<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 ue_int3 integer,<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 ue_number number,<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 ue_date date,<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 ue_str varchar2(32000)<br> );<br> /<br> <br> create or replace type ul4onevents as table of ul4onevent;<br> /<br> <br> create or replace function test_events2<br> return ul4onevents<br> as<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 v_events ul4onevents;<br> <br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 procedure append(<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 p_ue_type varchar2,= <br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 p_ue_backref intege= r :=3D null,<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 p_ue_int1 integer := =3D null,<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 p_ue_int2 integer := =3D null,<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 p_ue_int3 integer := =3D null,<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 p_ue_number number = :=3D null,<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 p_ue_date date :=3D= null,<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 p_ue_str varchar2 := =3D null<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 )<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 as<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 begin<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 v_events.extend;<br= > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 v_events(v_events.c= ount) :=3D ul4onevent(p_ue_type, p_ue_backref, p_ue_int1, p_ue_int2, p_ue_i= nt3, p_ue_number, p_ue_date, p_ue_str);<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 end;<br> begin<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 v_events :=3D ul4onevents();<br> <br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 append('beginlist');<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 for i in 1 .. 1300 = loop<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 append('begindict');<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 append('str', p_ue_str=3D>= ;'firstname');<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 append('str', p_ue_str=3D>= ;'Foo');<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 append('str', p_ue_str=3D>= ;'lastname');<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 append('str', p_ue_str=3D>= ;'Bar');<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 append('str', p_ue_str=3D>= ;'none');<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 append('none');<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 append('str', p_ue_str=3D>= ;'bool');<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 append('bool', p_ue_int1=3D&= gt;1);<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 append('str', p_ue_str=3D>= ;'int');<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 append('int', p_ue_int1=3D&g= t;42);<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 append('str', p_ue_str=3D>= ;'float');<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 append('number', p_ue_number= =3D>42.5);<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 append('str', p_ue_str=3D>= ;'date');<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 append('date', p_ue_date=3D&= gt;to_date('29.02.2000<wbr>', 'DD.MM.YYYY'));<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 append('str', p_ue_str=3D>= ;'color');<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 append('color', p_ue_int1=3D= >845453000);<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 append('str', p_ue_str=3D>= ;'timedelta');<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 append('timedelta', p_ue_int= 1=3D>0, p_ue_int2=3D>42, p_ue_int3=3D>0);<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 append('str', p_ue_str=3D>= ;'monthdelta');<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 append('monthdelta', 17);<br= > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 append('str', p_ue_str=3D>= ;'slice');<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 append('slice', p_ue_int1=3D= >17, p_ue_int2=3D>23);<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 append('enddict');<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 end loop;<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 append('endlist');<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 return v_events;<br> end;<br> /<br> <br> ------------------------------<wbr>---------------<br> <br> I used the following script to compare the performance of<br> <br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 select * from table(test_events2)<br> <br> and<br> <br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 select test_events2 from dual<br> <br> ------------------------------<wbr>---------------<br> <br> import time<br> import cx_Oracle<br> <br> db =3D cx_Oracle.connect("user/pwd@db<wbr>")<br> c =3D db.cursor()<br> <br> t1 =3D time.time()<br> <br> e1 =3D []<br> c.execute("select * from table(test_events2)")<br> for r in c:<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 e1.append(tuple(r))<br> <br> t2 =3D time.time()<br> <br> e2 =3D []<br> c.execute("select test_events2 from dual")<br> <br> objs =3D c.fetchone()[0].aslist()<br> <br> for obj in objs:<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 e2.append((<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 obj.UE_TYPE,<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 obj.UE_BACKREF,<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 obj.UE_INT1,<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 obj.UE_INT2,<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 obj.UE_INT3,<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 obj.UE_NUMBER,<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 obj.UE_DATE,<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 obj.UE_STR,<br> =C2=A0 =C2=A0 =C2=A0 =C2=A0 ))<br> <br> t3 =3D time.time()<br> <br> print("table", t2-t1)<br> print("object", t3-t2)<br> print("equal", e1 =3D=3D e2)<br> <br> ------------------------------<wbr>---------------<br> <br> The output is:<br> <br> ------------------------------<wbr>---------------<br> <br> table 0.4585990905761719<br> object 106.14051795005798<br> equal True<br> <br> ------------------------------<wbr>---------------<br> <br> 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:<br> <br> ------------------------------<wbr>---------------<br> <br> table 0.4336049556732178<br> object 1.2956831455230713<br> equal True<br> <br> ------------------------------<wbr>---------------<br> <br> I wonder why that happens.<br> <br> The database version is 11.2.0.1.0 and I'm using Python 3.6.2 with cx_O= racle 6.0.1 on Mac OS X 10.12.6.<br> <br> Servus,<br> =C2=A0 =C2=A0Walter<br> <br> ------------------------------<wbr>------------------------------<wbr>-----= -------------<br> Check out the vibrant tech community on one of the world's most<br> engaging tech sites, Slashdot.org! <a href=3D"http://sdm.link/slashdot" rel= =3D"noreferrer" target=3D"_blank">http://sdm.link/slashdot</a><br> ______________________________<wbr>_________________<br> cx-oracle-users mailing list<br> <a href=3D"mailto:cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org" target=3D"_blank">= [email protected]<wbr>rge.net</a><br> <a href=3D"https://lists.sourceforge.net/lists/listinfo/cx-oracle-users" re= l=3D"noreferrer" target=3D"_blank">https://lists.sourceforge.net/<wbr>lists= /listinfo/cx-oracle-users</a><br> </blockquote></div><br></div> --94eb2c0ef2205b30770557ec7f1b-- --===============2207007277813296699== Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Disposition: inline ------------------------------------------------------------------------------ Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot --===============2207007277813296699== Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Disposition: inline _______________________________________________ cx-oracle-users mailing list cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org https://lists.sourceforge.net/lists/listinfo/cx-oracle-users --===============2207007277813296699==--