Re: Performance of objects
Anthony Tuininga <[email protected]> Mon, 4 Sep 2017 15:21:48 -0600
| Newsgroups | gmane.comp.python.db.cx-oracle |
|---|---|
| Message-ID | <CAE1XR-5VcXhMhqrnQydPr44xr3_CjTnCqkq+cewF-_mAOYXSww@mail.gmail.com> |
--===============5260764237229605862== Content-Type: multipart/alternative; boundary="001a1144a742d5c38a055863b244" --001a1144a742d5c38a055863b244 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, Aug 30, 2017 at 3:40 AM, Walter D=C3=B6rwald <[email protected]= > 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 versio= n > > specific? > > I retried on Linux with the same database: > > In [1] =E2=96=B8 import cx_Oracle > In [2] =E2=96=B8 cx_Oracle.clientversion() > Out[3] =E2=96=B8 (11, 2, 0, 2, 0) > In [4] =E2=96=B8 cx_Oracle.version > Out[4] =E2=96=B8 '6.0.1' > In [5] =E2=96=B8 db =3D cx_Oracle.connect("user/pwd@db") > In [6] =E2=96=B8 db.version > Out[6] =E2=96=B8 '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 =3D 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 outpu= t: > > 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=C3=B6rwald <walter@livinglog= ic.de> > > wrote: > > > >> Hello all! > >> > >> Now that cx_Oracle supports custom Oracle object types I tried them an= d > >> 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 :=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 > > 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 > --001a1144a742d5c38a055863b244 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable <div dir=3D"ltr"><div class=3D"gmail_extra"><div class=3D"gmail_quote">On W= ed, Aug 30, 2017 at 3:40 AM, Walter D=C3=B6rwald <span dir=3D"ltr"><<a h= ref=3D"mailto:[email protected]" target=3D"_blank">[email protected]= e</a>></span> wrote:<br><blockquote class=3D"gmail_quote" style=3D"margi= n:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex= "><span class=3D"gmail-m_-5105463687000158238gmail-">On 30 Aug 2017, at 1:0= 9, Anthony Tuininga wrote:<br> <br> > Hi Walter,<br> ><br> > I just tried this on Linux with 12.2 Client and Database. I got these<= br> > results:<br> ><br> > table 1.1217586994171143<br> > object 0.8592920303344727<br> ><br> > When I change the number of iterations from 1300 to 1200 I get this:<b= r> ><br> > table 0.996464729309082<br> > object 0.747194766998291<br> ><br> > So it seems consistent. Are you able to try with a different<br> > database/client scenario to see if the issues you are seeing are versi= on<br> > specific?<br> <br> </span>I retried on Linux with the same database:<br> <br> In [1] =E2=96=B8 import cx_Oracle<br> In [2] =E2=96=B8 cx_Oracle.clientversion()<br> Out[3] =E2=96=B8 (11, 2, 0, 2, 0)<br> In [4] =E2=96=B8 cx_Oracle.version<br> Out[4] =E2=96=B8 '6.0.1'<br> In [5] =E2=96=B8 db =3D cx_Oracle.connect("user/pwd@db<wbr>")<br> In [6] =E2=96=B8 db.version<br> Out[6] =E2=96=B8 '11.2.0.1.0'<br> <br> With 1200 iterations I get:<br> <br> table 0.3604605197906494<br> object 0.8905477523803711<br> equal True<br> <br> With 1300 iterations I get:<br> <br> table 0.3804595470428467<br> object 42.898988246917725<br> equal True<br> <br> The strange thing is that<br> <span class=3D"gmail-m_-5105463687000158238gmail-"><br> =C2=A0 =C2=A0c.execute("select test_events2 from dual")<br> <br> </span>takes 0.2 seconds and<br> <span class=3D"gmail-m_-5105463687000158238gmail-"><br> =C2=A0 =C2=A0objs =3D c.fetchone()[0].aslist()<br> <br> </span>takes 0.1 seconds, but accessing the attributes takes 42 seconds.<br= > <br> I also tried the script on Linux against a 12.1.0.2.0 database. The output:= <br> <br> table 0.5058784484863281<br> object select 0.20530128479003906<br> object fetch 0.10213565826416016<br> object convert 110.43995141983032<br> object 110.74747037887573<br> equal True<br> <br> So maybe this problem is specific to the client version?<br> <br> Or it happens on all versions, but the threshold is higher?<br></blockquote= ><div><br></div><div>I just tried with 11.2 client and I get similar respon= ses to yours.</div><div><br></div><div>1200:</div><div><div>table 0.9072585= 105895996</div><div>object 0.710052490234375</div></div><div><br></div><div= >1300:</div><div><div>table 1.0106935501098633</div><div>object 116.8638014= 793396</div></div><div><br></div><div>With 12.1 client I also get similar n= umbers:</div><div><br></div><div>1200:</div><div><div>table 0.9160842895507= 812</div><div>object 0.7757439613342285</div></div><div><br></div><div>1300= :</div><div><div>table 1.0204412937164307</div><div>object 118.602068424224= 85</div></div><div><br></div><div>So apparently, this was a performance iss= ue resolved in 12.2 client. If you can upgrade, do it!</div><div><br></div>= <div>Anthony</div><div>=C2=A0</div><blockquote class=3D"gmail_quote" style= =3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding= -left:1ex"> <div class=3D"gmail-m_-5105463687000158238gmail-HOEnZb"><div class=3D"gmail= -m_-5105463687000158238gmail-h5"><br> > Anthony<br> ><br> > On Thu, Aug 24, 2017 at 11:28 AM, Walter D=C3=B6rwald <<a href=3D"m= ailto:[email protected]" target=3D"_blank">[email protected]</a>>= ;<br> > wrote:<br> ><br> >> Hello all!<br> >><br> >> Now that cx_Oracle supports custom Oracle object types I tried the= m and<br> >> discovered strange performance fluctuations. I defined the followi= ng types<br> >> 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 =C2=A0ue_type varchar2(20),<br> >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0ue_backref integer,<br> >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0ue_int1 integer,<br> >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0ue_int2 integer,<br> >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0ue_int3 integer,<br> >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0ue_number number,<br> >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0ue_date date,<br> >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0ue_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 =C2=A0v_events ul4onevents;<br> >><br> >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0procedure append(<br> >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0p_ue_= type varchar2,<br> >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0p_ue_= backref integer :=3D null,<br> >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0p_ue_= int1 integer :=3D null,<br> >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0p_ue_= int2 integer :=3D null,<br> >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0p_ue_= int3 integer :=3D null,<br> >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0p_ue_= number number :=3D null,<br> >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0p_ue_= date date :=3D null,<br> >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0p_ue_= str varchar2 :=3D null<br> >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0)<br> >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0as<br> >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0begin<br> >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0v_eve= nts.extend;<br> >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0v_eve= nts(v_events.count) :=3D ul4onevent(p_ue_type,<br> >> p_ue_backref, p_ue_int1, p_ue_int2, p_ue_int3, p_ue_number, p_ue_d= ate,<br> >> p_ue_str);<br> >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0end;<br> >> begin<br> >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0v_events :=3D ul4onevents();<br> >><br> >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0append('beginlist');<br> >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0for 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 =C2=A0append('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 =C2=A0append('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 =C2=A0append('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 =C2=A0append('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 =C2=A0append('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 =C2=A0append('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 =C2=A0append('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 =C2=A0append('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 =C2=A0append('bool', = p_ue_int1=3D>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 =C2=A0append('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 =C2=A0append('int', p= _ue_int1=3D>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 =C2=A0append('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 =C2=A0append('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 =C2=A0append('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 =C2=A0append('date',<= br> >> p_ue_date=3D>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 =C2=A0append('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 =C2=A0append('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 =C2=A0append('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 =C2=A0append('timedelta&#= 39;, p_ue_int1=3D>0,<br> >> 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 =C2=A0append('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 =C2=A0append('monthdelta&= #39;, 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 =C2=A0append('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 =C2=A0append('slice',= p_ue_int1=3D>17,<br> >> 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 =C2=A0append('enddict');<br> >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0end l= oop;<br> >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0append('endlist');<br> >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0return 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 =C2=A0select * from table(test_events2)= <br> >><br> >> and<br> >><br> >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0select 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 =C2=A0e1.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 =C2=A0e2.append((<br> >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0obj.U= E_TYPE,<br> >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0obj.U= E_BACKREF,<br> >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0obj.U= E_INT1,<br> >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0obj.U= E_INT2,<br> >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0obj.U= E_INT3,<br> >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0obj.U= E_NUMBER,<br> >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0obj.U= E_DATE,<br> >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0obj.U= E_STR,<br> >>=C2=A0 =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<br> >> change the number of iterations from 1300 to 1200 the output is:<b= r> >><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<br> >> cx_Oracle 6.0.1 on Mac OS X 10.12.6.<br> >><br> >> Servus,<br> >>=C2=A0 =C2=A0 Walter<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> </div></div></blockquote></div><br></div></div> --001a1144a742d5c38a055863b244-- --===============5260764237229605862== 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 --===============5260764237229605862== 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 --===============5260764237229605862==--