cx-oracle-users Mailing List for cx_Oracle (Page 9)
Brought to you by:
atuining
Menu
▾
▴
cx-oracle-users — Primary discussion list for users (and developers) of cx_Oracle
You can subscribe to this list here.
| 2003 |
Jan
|
Feb
|
Mar
|
Apr
|
May
|
Jun
|
Jul
(5) |
Aug
(9) |
Sep
(8) |
Oct
(12) |
Nov
(4) |
Dec
(8) |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2004 |
Jan
(15) |
Feb
(12) |
Mar
(11) |
Apr
(5) |
May
(7) |
Jun
(8) |
Jul
(12) |
Aug
(2) |
Sep
(14) |
Oct
(17) |
Nov
(20) |
Dec
(3) |
| 2005 |
Jan
(16) |
Feb
(9) |
Mar
(22) |
Apr
(21) |
May
(73) |
Jun
(16) |
Jul
(15) |
Aug
(10) |
Sep
(32) |
Oct
(35) |
Nov
(22) |
Dec
(13) |
| 2006 |
Jan
(42) |
Feb
(36) |
Mar
(13) |
Apr
(18) |
May
(8) |
Jun
(17) |
Jul
(24) |
Aug
(30) |
Sep
(35) |
Oct
(33) |
Nov
(33) |
Dec
(11) |
| 2007 |
Jan
(35) |
Feb
(31) |
Mar
(35) |
Apr
(64) |
May
(38) |
Jun
(12) |
Jul
(18) |
Aug
(34) |
Sep
(75) |
Oct
(29) |
Nov
(51) |
Dec
(11) |
| 2008 |
Jan
(27) |
Feb
(46) |
Mar
(48) |
Apr
(36) |
May
(59) |
Jun
(42) |
Jul
(25) |
Aug
(34) |
Sep
(57) |
Oct
(97) |
Nov
(59) |
Dec
(57) |
| 2009 |
Jan
(48) |
Feb
(48) |
Mar
(45) |
Apr
(24) |
May
(46) |
Jun
(52) |
Jul
(52) |
Aug
(37) |
Sep
(27) |
Oct
(40) |
Nov
(37) |
Dec
(13) |
| 2010 |
Jan
(16) |
Feb
(9) |
Mar
(24) |
Apr
(6) |
May
(27) |
Jun
(28) |
Jul
(60) |
Aug
(16) |
Sep
(33) |
Oct
(20) |
Nov
(39) |
Dec
(30) |
| 2011 |
Jan
(23) |
Feb
(43) |
Mar
(16) |
Apr
(29) |
May
(23) |
Jun
(16) |
Jul
(10) |
Aug
(8) |
Sep
(18) |
Oct
(42) |
Nov
(26) |
Dec
(20) |
| 2012 |
Jan
(17) |
Feb
(27) |
Mar
|
Apr
(20) |
May
(18) |
Jun
(7) |
Jul
(24) |
Aug
(21) |
Sep
(23) |
Oct
(18) |
Nov
(12) |
Dec
(5) |
| 2013 |
Jan
(14) |
Feb
(10) |
Mar
(20) |
Apr
(65) |
May
(3) |
Jun
(8) |
Jul
(6) |
Aug
(3) |
Sep
|
Oct
(3) |
Nov
(28) |
Dec
(3) |
| 2014 |
Jan
(3) |
Feb
(9) |
Mar
(4) |
Apr
(7) |
May
(20) |
Jun
(2) |
Jul
(20) |
Aug
(7) |
Sep
(11) |
Oct
(8) |
Nov
(6) |
Dec
(12) |
| 2015 |
Jan
(16) |
Feb
(10) |
Mar
(14) |
Apr
(8) |
May
|
Jun
(8) |
Jul
(15) |
Aug
(7) |
Sep
(1) |
Oct
(33) |
Nov
(8) |
Dec
(5) |
| 2016 |
Jan
(18) |
Feb
(12) |
Mar
(6) |
Apr
(14) |
May
(5) |
Jun
(3) |
Jul
|
Aug
(21) |
Sep
|
Oct
(15) |
Nov
(8) |
Dec
|
| 2017 |
Jan
|
Feb
(14) |
Mar
(21) |
Apr
(9) |
May
(6) |
Jun
(11) |
Jul
(23) |
Aug
(6) |
Sep
(5) |
Oct
(7) |
Nov
(1) |
Dec
(1) |
| 2018 |
Jan
|
Feb
|
Mar
(16) |
Apr
(2) |
May
(1) |
Jun
|
Jul
(2) |
Aug
|
Sep
(2) |
Oct
|
Nov
|
Dec
|
| 2019 |
Jan
(2) |
Feb
(3) |
Mar
(1) |
Apr
(1) |
May
|
Jun
|
Jul
(2) |
Aug
(1) |
Sep
(2) |
Oct
|
Nov
|
Dec
(1) |
| 2020 |
Jan
|
Feb
(4) |
Mar
|
Apr
|
May
(2) |
Jun
(1) |
Jul
(4) |
Aug
|
Sep
(1) |
Oct
|
Nov
|
Dec
(3) |
| 2021 |
Jan
|
Feb
(5) |
Mar
|
Apr
(7) |
May
(6) |
Jun
(1) |
Jul
|
Aug
|
Sep
|
Oct
|
Nov
(1) |
Dec
|
| 2022 |
Jan
|
Feb
|
Mar
|
Apr
|
May
(1) |
Jun
|
Jul
|
Aug
|
Sep
|
Oct
|
Nov
|
Dec
|
| 2023 |
Jan
|
Feb
(1) |
Mar
|
Apr
|
May
|
Jun
|
Jul
|
Aug
|
Sep
|
Oct
|
Nov
|
Dec
|
|
From: Anthony T. <ant...@gm...> - 2016-08-19 14:58:40
|
Hi, The only files provided are found here: https://pypi.python.org/pypi/cx_Oracle/ I don't recall the errors that others (and now you) were experiencing with installation of cx_Oracle under Python 3.5 on Windows 10. Can you enlighten me? Note that if the installation succeeds but puts the file in the wrong place, you can always copy the file to the right place afterwards. Note as well that the executable installer file is an executable with a zip file appended to it so you can always just open the file with an unzip tool and extract the cx_Oracle.pyd directly. Hope that helps! Anthony On Fri, Aug 19, 2016 at 7:50 AM, <rob...@sb...> wrote: > Hello, I have been looking through old emails on this list because I've > encountered an error that others have with installing cx_Oracle to Windows > 10 for python 3.5. I was looking for a place on the cx_Oracle site where I > could just download the compiled binary library for python 3.5 windows > 64-bit. Is there a copy available somewhere without having to use the > installer? > > Thanks > Rob > > ------------------------------------------------------------ > ------------------ > > _______________________________________________ > cx-oracle-users mailing list > cx-...@li... > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users > > |
|
From: <rob...@sb...> - 2016-08-19 14:04:43
|
Hello, I have been looking through old emails on this list because I've encountered an error that others have with installing cx_Oracle to Windows 10 for python 3.5. I was looking for a place on the cx_Oracle site where I could just download the compiled binary library for python 3.5 windows 64-bit. Is there a copy available somewhere without having to use the installer? ThanksRob |
|
From: Moritz L. <mor...@go...> - 2016-08-17 19:16:37
|
I have used the reading of geometry directly in python.
For example with a sql query I fetched my geomeetries and then I build my
JSON:
for id, geometry, classname in cursor:
result.append({
"building_nr": id, "geometry": {
"type": "polygon",
"coordinates": zip(*[iter(geometry.SDO_ORDINATES.aslist())] * 3
),
}, "polygon_typ": classname,
})
return json.dumps(result)
But it took a longt time in my case so I wanna try new ways to fetch the
coordinates...
2016-08-17 13:58 GMT+02:00 Jani Tiainen <re...@gm...>:
> Hi,
>
> Is there a reason you use GETVERTICES-utility instead of reading geometry
> directly in Python?
> cx_Oracle have had that feature for a good while.
>
>
> On 16.08.2016 13:33, Moritz Liebelt wrote:
>
> Hi,
>
> I want to fetch coordinates from my Oracle database (11g Locator). I will
> try it with the intern function SDO_UTIL.GETVERTICES().
>
> *My sql query looks like:*
> *"""SELECT a.id <http://a.id> , t.x, t.y, t.z, d.Classname FROM building
> a, THEMATIC_SURFACE b, SURFACE_GEOMETRY c,
> TABLE(SDO_UTIL.GETVERTICES(c.geometry))t, OBJECTCLASS d WHERE
> a.grid_id_500 = 2728 AND a.id <http://a.id> = b.BUILDING_ID AND
> b.LOD2_MULTI_SURFACE_ID = c.ROOT_ID AND c.GEOMETRY IS NOT NULL AND
> b.OBJECTCLASS_ID = d.ID""")*
>
> Afterwards I create the json:
>
> * for id,x,y,z, classname in cursor: result.append({
> "building_nr":id,"geometry": { "type":"polygon","coordinates":[x,y,z],},
> "polygon_typ":classname,}) *
>
> * But of course I get only one Point:*
>
> [{'building_nr': 1314867, 'geometry': {'type': 'polygon', 'coordinates':
> [(3500936.67, 5394350.72, 439.17400000000004)]}, 'polygon_typ':
> 'BuildingWallSurface'}, ...
>
>
>
> The problem is I get only one point geometry.
>
> But for example there exists 5 points for this wall surface. I need all
> points with the same building_nr (id)!
>
> Can I iterate over x,y,z ?
>
>
>
> --
> *Moritz Liebelt, B. Sc.*
>
> Murgstraße 19
> 76437 Rastatt
> E-Mail: mor...@go...
>
>
> ------------------------------------------------------------------------------
>
>
>
> _______________________________________________
> cx-oracle-users mailing lis...@li...https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
> --
> Jani Tiainen
>
>
> ------------------------------------------------------------
> ------------------
>
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
--
*Moritz Liebelt, B. Sc.*
Murgstraße 19
76437 Rastatt
E-Mail: mor...@go...
|
|
From: Jani T. <re...@gm...> - 2016-08-17 11:58:16
|
Hi, Is there a reason you use GETVERTICES-utility instead of reading geometry directly in Python? cx_Oracle have had that feature for a good while. On 16.08.2016 13:33, Moritz Liebelt wrote: > Hi, > > I want to fetch coordinates from my Oracle database (11g Locator). I > will try it with the intern function SDO_UTIL.GETVERTICES(). > > *My sql query looks like:* > /"""SELECT a.id <http://a.id> , t.x, t.y, t.z, d.Classname FROM > building a, THEMATIC_SURFACE b, SURFACE_GEOMETRY c, > TABLE(SDO_UTIL.GETVERTICES(c.geometry))t, OBJECTCLASS d WHERE > a.grid_id_500 = 2728 AND a.id <http://a.id> = b.BUILDING_ID AND > b.LOD2_MULTI_SURFACE_ID = c.ROOT_ID AND c.GEOMETRY IS NOT NULL AND > b.OBJECTCLASS_ID = d.ID""")/ > / > / > Afterwards I create the json: > > / > for id,x,y,z, classname in cursor: > result.append({ > "building_nr":id,"geometry": { > "type":"polygon","coordinates":[x,y,z],}, "polygon_typ":classname,}) > / > / > / > *//But of course I get only one Point:* > > [{'building_nr': 1314867, 'geometry': {'type': 'polygon', > 'coordinates': [(3500936.67, 5394350.72, 439.17400000000004)]}, > 'polygon_typ': 'BuildingWallSurface'}, ... > > The problem is I get only one point geometry. > > But for example there exists 5 points for this wall surface. I need > all points with the same building_nr (id)! > > Can I iterate over x,y,z ? > > > > -- > *Moritz Liebelt, B. Sc.* > > Murgstraße 19 > 76437 Rastatt > E-Mail: mor...@go... > <mailto:mor...@go...> > > > ------------------------------------------------------------------------------ > > > _______________________________________________ > cx-oracle-users mailing list > cx-...@li... > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users -- Jani Tiainen |
|
From: Moritz L. <mor...@go...> - 2016-08-16 10:33:37
|
Hi, I want to fetch coordinates from my Oracle database (11g Locator). I will try it with the intern function SDO_UTIL.GETVERTICES(). *My sql query looks like:* *"""SELECT a.id <http://a.id> , t.x, t.y, t.z, d.Classname FROM building a, THEMATIC_SURFACE b, SURFACE_GEOMETRY c, TABLE(SDO_UTIL.GETVERTICES(c.geometry))t, OBJECTCLASS d WHERE a.grid_id_500 = 2728 AND a.id <http://a.id> = b.BUILDING_ID AND b.LOD2_MULTI_SURFACE_ID = c.ROOT_ID AND c.GEOMETRY IS NOT NULL AND b.OBJECTCLASS_ID = d.ID""")* Afterwards I create the json: * for id,x,y,z, classname in cursor: result.append({ "building_nr":id,"geometry": { "type":"polygon","coordinates":[x,y,z],}, "polygon_typ":classname,})* * But of course I get only one Point:* [{'building_nr': 1314867, 'geometry': {'type': 'polygon', 'coordinates': [(3500936.67, 5394350.72, 439.17400000000004)]}, 'polygon_typ': 'BuildingWallSurface'}, ... The problem is I get only one point geometry. But for example there exists 5 points for this wall surface. I need all points with the same building_nr (id)! Can I iterate over x,y,z ? -- *Moritz Liebelt, B. Sc.* Murgstraße 19 76437 Rastatt E-Mail: mor...@go... |
|
From: Anurag C. <anu...@gm...> - 2016-08-13 20:14:37
|
Thanks Shai, That was the case indeed. I am now trying with cx_Oracle version 5.1.2 and with better results. Regards, Anurag On Sun, Aug 14, 2016 at 12:24 AM, Shai Berger <sh...@pl...> wrote: > Hi Anurag, > > I believe Oracle 10 is not supported by cx_Oracle 5.2. This does not > necessarily mean you cannot use it with your existing server, but you > probably > need to get newer Oracle client libraries. > > HTH, > Shai. > > On Saturday 13 August 2016 16:49:09 Anurag Chourasia wrote: > > Dear All, > > > > Could someone please help me with the error I am getting upon installing > > cx_Oracle on AIX5.3 > > > > a776q > > /app/appadm/.Mim/cx_Oracle-5.2.1>/app/appadm/.Mim/python2.7/bin/python > > *Python 2.7.12* (default, Aug 13 2016, 09:14:12) [C] on aix5 > > Type "help", "copyright", "credits" or "license" for more information. > > > > >>> exit() > > > > a776q > > /app/appadm/.Mim/cx_Oracle-5.2.1>/app/appadm/.Mim/python2.7/bin/python > > setup.py install > > running install > > running build > > running build_ext > > building 'cx_Oracle' extension > > creating build > > creating build/temp.aix-5.3-2.7-10g > > /usr/vac/bin/xlc_r -DNDEBUG -O -I/app/appadm/.Mim/ncurses/include > > -I/app/appadm/.Mim/ncurses/include/ncurses > > -I/home/oracle10/OraHome_1/rdbms/demo > > -I/home/oracle10/OraHome_1/rdbms/public > > -I/app/appadm/.Mim/python2.7/include/python2.7 -c cx_Oracle.c -o > > build/temp.aix-5.3-2.7-10g/cx_Oracle.o -DBUILD_VERSION=5.2.1 -DAIX5 > > "LobVar.c", line 24.59: 1506-046 (S) Syntax error. > > "ExternalLobVar.c", line 168.5: 1506-046 (S) Syntax error. > > "ExternalLobVar.c", line 172.5: 1506-045 (S) Undeclared identifier > oraub8. > > "ExternalLobVar.c", line 177.9: 1506-045 (S) Undeclared identifier > > lengthInBytes. > > "ExternalLobVar.c", line 178.26: 1506-045 (S) Undeclared identifier > length. > > "ExternalLobVar.c", line 178.9: 1506-045 (S) Undeclared identifier > > lengthInChars. > > "ExternalLobVar.c", line 201.45: 1506-045 (S) Undeclared identifier > offset. > > "ExternalLobVar.c", line 201.61: 1506-045 (S) Undeclared identifier > > bufferSize. > > "ExternalLobVar.c", line 239.5: 1506-046 (S) Syntax error. > > "ExternalLobVar.c", line 246.42: 1506-045 (S) Undeclared identifier > length. > > "ExternalLobVar.c", line 262.5: 1506-046 (S) Syntax error. > > "ExternalLobVar.c", line 265.5: 1506-045 (S) Undeclared identifier > oraub8. > > "ExternalLobVar.c", line 270.9: 1506-045 (S) Undeclared identifier > amount. > > "ExternalLobVar.c", line 273.23: 1506-045 (S) Undeclared identifier > offset. > > "ExternalLobVar.c", line 277.5: 1506-045 (S) Undeclared identifier > length. > > "ExternalLobVar.c", line 279.9: 1506-045 (S) Undeclared identifier > > bufferSize. > > "ExternalLobVar.c", line 316.12: 1506-275 (S) Unexpected text length > > encountered. > > "ExternalLobVar.c", line 316.5: 1506-045 (S) Undeclared identifier > oraub8. > > "ExternalLobVar.c", line 320.43: 1506-045 (S) Undeclared identifier > length. > > "ExternalLobVar.c", line 386.12: 1506-275 (S) Unexpected text offset > > encountered. > > "ExternalLobVar.c", line 386.5: 1506-045 (S) Undeclared identifier > oraub8. > > "ExternalLobVar.c", line 389.5: 1506-045 (S) Undeclared identifier > offset. > > "ExternalLobVar.c", line 390.5: 1506-045 (S) Undeclared identifier > amount. > > "ExternalLobVar.c", line 424.12: 1506-275 (S) Unexpected text amount > > encountered. > > "ExternalLobVar.c", line 424.20: 1506-045 (S) Undeclared identifier > offset. > > "ExternalLobVar.c", line 436.63: 1506-045 (S) Undeclared identifier > amount. > > "ExternalLobVar.c", line 454.12: 1506-275 (S) Unexpected text newSize > > encountered. > > "ExternalLobVar.c", line 458.5: 1506-045 (S) Undeclared identifier > newSize. > > "LobVar.c", line 317.5: 1506-046 (S) Syntax error. > > "LobVar.c", line 331.10: 1506-045 (S) Undeclared identifier amount. > > "LobVar.c", line 358.13: 1506-045 (S) Undeclared identifier offset. > > "LobVar.c", line 392.12: 1506-275 (S) Unexpected text amount encountered. > > "LobVar.c", line 427.51: 1506-045 (S) Undeclared identifier amount. > > error: command '/usr/vac/bin/xlc_r' failed with exit status 1 > > > > Regards, > > Anurag > > ------------------------------------------------------------ > ------------------ > What NetFlow Analyzer can do for you? Monitors network bandwidth and > traffic > patterns at an interface-level. Reveals which users, apps, and protocols > are > consuming the most bandwidth. Provides multi-vendor support for NetFlow, > J-Flow, sFlow and other flows. Make informed decisions using capacity > planning reports. http://sdm.link/zohodev2dev > _______________________________________________ > cx-oracle-users mailing list > cx-...@li... > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users > |
|
From: Shai B. <sh...@pl...> - 2016-08-13 19:11:44
|
Hi Anurag, I believe Oracle 10 is not supported by cx_Oracle 5.2. This does not necessarily mean you cannot use it with your existing server, but you probably need to get newer Oracle client libraries. HTH, Shai. On Saturday 13 August 2016 16:49:09 Anurag Chourasia wrote: > Dear All, > > Could someone please help me with the error I am getting upon installing > cx_Oracle on AIX5.3 > > a776q > /app/appadm/.Mim/cx_Oracle-5.2.1>/app/appadm/.Mim/python2.7/bin/python > *Python 2.7.12* (default, Aug 13 2016, 09:14:12) [C] on aix5 > Type "help", "copyright", "credits" or "license" for more information. > > >>> exit() > > a776q > /app/appadm/.Mim/cx_Oracle-5.2.1>/app/appadm/.Mim/python2.7/bin/python > setup.py install > running install > running build > running build_ext > building 'cx_Oracle' extension > creating build > creating build/temp.aix-5.3-2.7-10g > /usr/vac/bin/xlc_r -DNDEBUG -O -I/app/appadm/.Mim/ncurses/include > -I/app/appadm/.Mim/ncurses/include/ncurses > -I/home/oracle10/OraHome_1/rdbms/demo > -I/home/oracle10/OraHome_1/rdbms/public > -I/app/appadm/.Mim/python2.7/include/python2.7 -c cx_Oracle.c -o > build/temp.aix-5.3-2.7-10g/cx_Oracle.o -DBUILD_VERSION=5.2.1 -DAIX5 > "LobVar.c", line 24.59: 1506-046 (S) Syntax error. > "ExternalLobVar.c", line 168.5: 1506-046 (S) Syntax error. > "ExternalLobVar.c", line 172.5: 1506-045 (S) Undeclared identifier oraub8. > "ExternalLobVar.c", line 177.9: 1506-045 (S) Undeclared identifier > lengthInBytes. > "ExternalLobVar.c", line 178.26: 1506-045 (S) Undeclared identifier length. > "ExternalLobVar.c", line 178.9: 1506-045 (S) Undeclared identifier > lengthInChars. > "ExternalLobVar.c", line 201.45: 1506-045 (S) Undeclared identifier offset. > "ExternalLobVar.c", line 201.61: 1506-045 (S) Undeclared identifier > bufferSize. > "ExternalLobVar.c", line 239.5: 1506-046 (S) Syntax error. > "ExternalLobVar.c", line 246.42: 1506-045 (S) Undeclared identifier length. > "ExternalLobVar.c", line 262.5: 1506-046 (S) Syntax error. > "ExternalLobVar.c", line 265.5: 1506-045 (S) Undeclared identifier oraub8. > "ExternalLobVar.c", line 270.9: 1506-045 (S) Undeclared identifier amount. > "ExternalLobVar.c", line 273.23: 1506-045 (S) Undeclared identifier offset. > "ExternalLobVar.c", line 277.5: 1506-045 (S) Undeclared identifier length. > "ExternalLobVar.c", line 279.9: 1506-045 (S) Undeclared identifier > bufferSize. > "ExternalLobVar.c", line 316.12: 1506-275 (S) Unexpected text length > encountered. > "ExternalLobVar.c", line 316.5: 1506-045 (S) Undeclared identifier oraub8. > "ExternalLobVar.c", line 320.43: 1506-045 (S) Undeclared identifier length. > "ExternalLobVar.c", line 386.12: 1506-275 (S) Unexpected text offset > encountered. > "ExternalLobVar.c", line 386.5: 1506-045 (S) Undeclared identifier oraub8. > "ExternalLobVar.c", line 389.5: 1506-045 (S) Undeclared identifier offset. > "ExternalLobVar.c", line 390.5: 1506-045 (S) Undeclared identifier amount. > "ExternalLobVar.c", line 424.12: 1506-275 (S) Unexpected text amount > encountered. > "ExternalLobVar.c", line 424.20: 1506-045 (S) Undeclared identifier offset. > "ExternalLobVar.c", line 436.63: 1506-045 (S) Undeclared identifier amount. > "ExternalLobVar.c", line 454.12: 1506-275 (S) Unexpected text newSize > encountered. > "ExternalLobVar.c", line 458.5: 1506-045 (S) Undeclared identifier newSize. > "LobVar.c", line 317.5: 1506-046 (S) Syntax error. > "LobVar.c", line 331.10: 1506-045 (S) Undeclared identifier amount. > "LobVar.c", line 358.13: 1506-045 (S) Undeclared identifier offset. > "LobVar.c", line 392.12: 1506-275 (S) Unexpected text amount encountered. > "LobVar.c", line 427.51: 1506-045 (S) Undeclared identifier amount. > error: command '/usr/vac/bin/xlc_r' failed with exit status 1 > > Regards, > Anurag |
|
From: Anurag C. <anu...@gm...> - 2016-08-13 13:49:57
|
Dear All, Could someone please help me with the error I am getting upon installing cx_Oracle on AIX5.3 a776q /app/appadm/.Mim/cx_Oracle-5.2.1>/app/appadm/.Mim/python2.7/bin/python *Python 2.7.12* (default, Aug 13 2016, 09:14:12) [C] on aix5 Type "help", "copyright", "credits" or "license" for more information. >>> exit() a776q /app/appadm/.Mim/cx_Oracle-5.2.1>/app/appadm/.Mim/python2.7/bin/python setup.py install running install running build running build_ext building 'cx_Oracle' extension creating build creating build/temp.aix-5.3-2.7-10g /usr/vac/bin/xlc_r -DNDEBUG -O -I/app/appadm/.Mim/ncurses/include -I/app/appadm/.Mim/ncurses/include/ncurses -I/home/oracle10/OraHome_1/rdbms/demo -I/home/oracle10/OraHome_1/rdbms/public -I/app/appadm/.Mim/python2.7/include/python2.7 -c cx_Oracle.c -o build/temp.aix-5.3-2.7-10g/cx_Oracle.o -DBUILD_VERSION=5.2.1 -DAIX5 "LobVar.c", line 24.59: 1506-046 (S) Syntax error. "ExternalLobVar.c", line 168.5: 1506-046 (S) Syntax error. "ExternalLobVar.c", line 172.5: 1506-045 (S) Undeclared identifier oraub8. "ExternalLobVar.c", line 177.9: 1506-045 (S) Undeclared identifier lengthInBytes. "ExternalLobVar.c", line 178.26: 1506-045 (S) Undeclared identifier length. "ExternalLobVar.c", line 178.9: 1506-045 (S) Undeclared identifier lengthInChars. "ExternalLobVar.c", line 201.45: 1506-045 (S) Undeclared identifier offset. "ExternalLobVar.c", line 201.61: 1506-045 (S) Undeclared identifier bufferSize. "ExternalLobVar.c", line 239.5: 1506-046 (S) Syntax error. "ExternalLobVar.c", line 246.42: 1506-045 (S) Undeclared identifier length. "ExternalLobVar.c", line 262.5: 1506-046 (S) Syntax error. "ExternalLobVar.c", line 265.5: 1506-045 (S) Undeclared identifier oraub8. "ExternalLobVar.c", line 270.9: 1506-045 (S) Undeclared identifier amount. "ExternalLobVar.c", line 273.23: 1506-045 (S) Undeclared identifier offset. "ExternalLobVar.c", line 277.5: 1506-045 (S) Undeclared identifier length. "ExternalLobVar.c", line 279.9: 1506-045 (S) Undeclared identifier bufferSize. "ExternalLobVar.c", line 316.12: 1506-275 (S) Unexpected text length encountered. "ExternalLobVar.c", line 316.5: 1506-045 (S) Undeclared identifier oraub8. "ExternalLobVar.c", line 320.43: 1506-045 (S) Undeclared identifier length. "ExternalLobVar.c", line 386.12: 1506-275 (S) Unexpected text offset encountered. "ExternalLobVar.c", line 386.5: 1506-045 (S) Undeclared identifier oraub8. "ExternalLobVar.c", line 389.5: 1506-045 (S) Undeclared identifier offset. "ExternalLobVar.c", line 390.5: 1506-045 (S) Undeclared identifier amount. "ExternalLobVar.c", line 424.12: 1506-275 (S) Unexpected text amount encountered. "ExternalLobVar.c", line 424.20: 1506-045 (S) Undeclared identifier offset. "ExternalLobVar.c", line 436.63: 1506-045 (S) Undeclared identifier amount. "ExternalLobVar.c", line 454.12: 1506-275 (S) Unexpected text newSize encountered. "ExternalLobVar.c", line 458.5: 1506-045 (S) Undeclared identifier newSize. "LobVar.c", line 317.5: 1506-046 (S) Syntax error. "LobVar.c", line 331.10: 1506-045 (S) Undeclared identifier amount. "LobVar.c", line 358.13: 1506-045 (S) Undeclared identifier offset. "LobVar.c", line 392.12: 1506-275 (S) Unexpected text amount encountered. "LobVar.c", line 427.51: 1506-045 (S) Undeclared identifier amount. error: command '/usr/vac/bin/xlc_r' failed with exit status 1 Regards, Anurag |
|
From: Moritz L. <mor...@go...> - 2016-08-04 10:16:06
|
I can do another way but I do not know if this make sense. I do not have to build the json in the Webserver. I can already build the json in the Oracle DB. Than I can execute a query to get this json. But I think this doesnt make so much sense. Because this json is in Oracle a clob Type and this I have to read out in cx_Oracle like the sdo_Geometry (cx_Oracle.Object). Does anyone has experience to fetch a big amount of 3d geometries and build a json from this with cx_Oracle??? Am 03.08.2016 11:47 vorm. schrieb "Moritz Liebelt" < mor...@go...>: > Hi Anthony, > thanks for your help. > > 1) the size of the geometries are not the same in both tiles. I have in > 400m tile 4137 number of output rows with all in all 23.707 coordinates. In > the 500m tile I have 6241 number of output rows with all in all 35.727 > coordinates. So it is less than half of the 400m tile. But the time to > fetch the data out of the database is increasing from 0,26s to 14 seconds. > I cannot understand this time difference. > > 2.) putting the output from attribute.aslist() in a local variable and > then processing it instead doesn´t make a difference the time is nearly the > same. > > 3) I created a copy of a test-database with my tables and geometries but > it is really big... I did some manipulations so the best for me is to > duplicate it and so you can test on real conditions! > Also I added the python script to fetch the data ( web.py + cx_Oracle) > > See the links: > 1.) Webserver: https://drive.google.com/open?id=0B5AUL4- > roeC4dmM2VVZjaGpoUVBEeGlZRXBGT2U1WVAxSXNV > 2.) Database: https://drive.google.com/open?id=0B5AUL4- > roeC4MDNsTnpOWkJFVms > > > 2016-08-03 0:54 GMT+02:00 Anthony Tuininga <ant...@gm...>: > >> Hi Moritz, >> >> Just a few notes that may be helpful in this regard. >> >> 1) Are the size of the geometries the same in both the 400m and 500m >> tiles? The output seems to suggest that is not the case but it would be >> good to confirm. >> >> 2) Every access to an attribute will fetch it from Oracle so if you plan >> to access an expensive attribute more than once, store it in a local >> variable. I don't believe this is an issue in your case but you can verify >> by simply putting the output from attribute.aslist() in a local variable >> and then processing it instead. >> >> 3) Can you provide a test case? The best would be a PL/SQL script that >> creates a table and generates the geometries and a Python script that >> retrieves them -- without the integration with the web client. If you can >> provide this I can see if there is something going on in the backend that I >> can fix or if that is "just the way it is". >> >> Anthony >> >> On Tue, Aug 2, 2016 at 2:20 AM, Moritz Liebelt < >> mor...@go...> wrote: >> >>> Hi, >>> >>> >>> I have some questions about cx_Oracle. >>> I work on a project for my Masterthesis. And a part will be to create a >>> webserver and to fetch the data from a database and send it back to client. >>> My Institute is using Oracle db. Because of that I am using the module >>> cx_Oracle to create an interface to my database (unreleased version). >>> >>> Inside my database I have 3d data (buildings) and I structures these >>> data in tiles. >>> So now I will compare the query of two tiles for example all the data in >>> 400m tile and all the data in 500m tile. As a result I get a very high >>> difference. >>> >>> 400m (4136rows) -> 0,26s >>> 500m (6200 rows) -> 14s >>> >>> Of course it twill need more time because of the higher number of output >>> rows. But this seems to me incorrect. >>> Do you have an idea what the problem could be? >>> Does it depends on the arraysize of the cursor? >>> >>> If you have some tips how to increase the performance it would be very >>> nice! >>> >>> Here you can see the code >>> >>> >>> 1. import cx_Oracle >>> 2. import json >>> 3. import web >>> 4. >>> 5. urls = ( >>> 6. "/", "index", >>> 7. "/grid", "grid", >>> 8. ) >>> 9. app = web.application(urls, globals(),web.profiler ) >>> 10. web.config.debug = True >>> 11. >>> 12. >>> 13. connection = cx_Oracle.Connection("TEST_3D/limo1013@10.40.33.160:1521/sdetest") >>> 14. typeObj = connection.gettype("MDSYS.SDO_GEOMETRY") >>> 15. >>> 16. class index: >>> 17. def GET(self): >>> 18. return "hallo moritz " >>> 19. >>> 20. class grid: >>> 21. >>> 22. def GET(self): >>> 23. web.header('Access-Control-Allow-Origin', '*') >>> 24. web.header('Access-Control-Allow-Credentials', 'true') >>> 25. web.header('Content-Type', 'application/json') >>> 26. >>> 27. cursor = connection.cursor() >>> 28. cursor.arraysize = 6300 # default = 50 >>> 29. cursor.execute("""SELECT a.id , c.geometry, d.Classname FROM building a, THEMATIC_SURFACE b, SURFACE_GEOMETRY c, OBJECTCLASS d WHERE a.grid_id_500 = 2728 AND a.id = b.BUILDING_ID AND b.LOD2_MULTI_SURFACE_ID = c.ROOT_ID AND c.GEOMETRY IS NOT NULL AND b.OBJECTCLASS_ID = d.ID""") >>> 30. >>> 31. def geometry_to_points(obj): >>> 32. return zip(*[iter(obj.SDO_ORDINATES.aslist())]*3) >>> 33. >>> 34. result = [] >>> 35. for id, geometry, classname in cursor: >>> 36. result.append({ >>> 37. "building_nr": id, "geometry": { >>> 38. "type": "polygon", >>> 39. "coordinates": [geometry_to_points(geometry)], >>> 40. }, "polygon_typ": classname, >>> 41. }) >>> 42. return json.dumps(result) >>> 43. >>> 44. # Aufruf der App >>> 45. if __name__ == "__main__": >>> 46. app.run(web.profiler) >>> >>> >>> *500m tile:* >>> >>> *took 14.1860001087 seconds* >>> 6880 function calls (6871 primitive calls) in 47.042 seconds >>> >>> Ordered by: internal time, call count >>> List reduced from 47 to 40 due to restriction <40> >>> >>> ncalls tottime percall cumtime percall filename:lineno(function) >>> 6241 45.735 0.007 45.735 0.007 template.py:31(geometry_to_points) >>> 1 0.735 0.735 46.894 46.894 template.py:22(GET) >>> 1 0.403 0.403 0.403 0.403 encoder.py:212(iterencode) >>> 360 0.074 0.000 0.107 0.000 application.py:663(check) >>> 1 0.038 0.038 46.932 46.932 application.py:389(handle_class) >>> 182 0.033 0.000 0.033 0.000 genericpath.py:23(exists) >>> 1 0.013 0.013 0.416 0.416 encoder.py:186(encode) >>> 1 0.008 0.008 0.424 0.424 __init__.py:193(dumps) >>> 1 0.001 0.001 0.109 0.109 application.py:659(__call__) >>> 1 0.000 0.000 0.000 0.000 application.py:338(load) >>> 13 0.000 0.000 0.000 0.000 utf_8.py:15(decode) >>> 1 0.000 0.000 47.042 47.042 application.py:269(wsgi) >>> 1 0.000 0.000 0.000 0.000 httpserver.py:308(log) >>> 2 0.000 0.000 0.000 0.000 utils.py:521(re_subm) >>> 1 0.000 0.000 0.000 0.000 basehttpserver.py:475(log_date_time_string) >>> 1 0.000 0.000 0.000 0.000 utils.py:1210(clear_all) >>> 5/1 0.000 0.000 47.041 47.041 application.py:233(process) >>> 2 0.000 0.000 0.000 0.000 utils.py:487(__call__) >>> 1 0.000 0.000 47.041 47.041 application.py:579(processor) >>> 8 0.000 0.000 0.000 0.000 utils.py:356(safestr) >>> 1 0.000 0.000 0.000 0.000 application.py:426(_match) >>> 3 0.000 0.000 0.000 0.000 utils.py:536(group) >>> 1 0.000 0.000 0.000 0.000 __init__.py:2035(start_response) >>> 1 0.000 0.000 46.932 46.932 application.py:388(_delegate) >>> 3/1 0.000 0.000 47.041 47.041 application.py:564(processor) >>> 1 0.000 0.000 0.000 0.000 application.py:75(reload_mapping) >>> 13 0.000 0.000 0.000 0.000 utils.py:1223(__setitem__) >>> 1 0.000 0.000 0.000 0.000 application.py:114(init_mapping) >>> 7 0.000 0.000 0.000 0.000 utils.py:545(take) >>> 3 0.000 0.000 0.000 0.000 webapi.py:260(header) >>> 1 0.000 0.000 0.000 0.000 application.py:97(_unload) >>> 1 0.000 0.000 46.932 46.932 application.py:228(handle) >>> 1 0.000 0.000 0.000 0.000 utils.py:1201(__init__) >>> 1 0.000 0.000 0.000 0.000 httpserver.py:301(xstart_response) >>> 4/1 0.000 0.000 47.041 47.041 application.py:237(<lambda>) >>> 1 0.000 0.000 0.000 0.000 application.py:109(_cleanup) >>> 1 0.000 0.000 47.041 47.041 application.py:232(handle_with_processors) >>> 1 0.000 0.000 0.000 0.000 os.py:446(get) >>> 2 0.000 0.000 0.000 0.000 utils.py:1234(clear) >>> 1 0.000 0.000 0.000 0.000 utils.py:1246(iteritems) >>> >>> >>> *400m tile:* >>> >>> *took 0.258000135422 seconds* >>> 4774 function calls (4765 primitive calls) in 0.859 seconds >>> >>> Ordered by: internal time, call count >>> List reduced from 45 to 40 due to restriction <40> >>> >>> ncalls tottime percall cumtime percall filename:lineno(function) >>> 1 0.348 0.348 0.737 0.737 template.py:22(GET) >>> 1 0.223 0.223 0.223 0.223 encoder.py:212(iterencode) >>> 4137 0.153 0.000 0.153 0.000 template.py:31(geometry_to_points) >>> 360 0.064 0.000 0.093 0.000 application.py:663(check) >>> 182 0.029 0.000 0.029 0.000 genericpath.py:23(exists) >>> 1 0.026 0.026 0.763 0.763 application.py:389(handle_class) >>> 1 0.009 0.009 0.232 0.232 encoder.py:186(encode) >>> 1 0.004 0.004 0.236 0.236 __init__.py:193(dumps) >>> 1 0.001 0.001 0.094 0.094 application.py:659(__call__) >>> 1 0.000 0.000 0.000 0.000 application.py:338(load) >>> 13 0.000 0.000 0.000 0.000 utf_8.py:15(decode) >>> 2 0.000 0.000 0.000 0.000 utils.py:521(re_subm) >>> 1 0.000 0.000 0.859 0.859 application.py:269(wsgi) >>> 1 0.000 0.000 0.000 0.000 httpserver.py:308(log) >>> 1 0.000 0.000 0.000 0.000 basehttpserver.py:475(log_date_time_string) >>> 5/1 0.000 0.000 0.858 0.858 application.py:233(process) >>> 1 0.000 0.000 0.858 0.858 application.py:579(processor) >>> 3 0.000 0.000 0.000 0.000 utils.py:536(group) >>> 8 0.000 0.000 0.000 0.000 utils.py:356(safestr) >>> 1 0.000 0.000 0.000 0.000 utils.py:1210(clear_all) >>> 1 0.000 0.000 0.000 0.000 application.py:75(reload_mapping) >>> 2 0.000 0.000 0.000 0.000 utils.py:487(__call__) >>> 1 0.000 0.000 0.000 0.000 application.py:426(_match) >>> 3/1 0.000 0.000 0.858 0.858 application.py:564(processor) >>> 1 0.000 0.000 0.763 0.763 application.py:388(_delegate) >>> 1 0.000 0.000 0.000 0.000 __init__.py:2035(start_response) >>> 1 0.000 0.000 0.000 0.000 application.py:114(init_mapping) >>> 13 0.000 0.000 0.000 0.000 utils.py:1223(__setitem__) >>> 1 0.000 0.000 0.763 0.763 application.py:228(handle) >>> 7 0.000 0.000 0.000 0.000 utils.py:545(take) >>> 3 0.000 0.000 0.000 0.000 webapi.py:260(header) >>> 1 0.000 0.000 0.000 0.000 application.py:97(_unload) >>> 1 0.000 0.000 0.000 0.000 application.py:109(_cleanup) >>> 4/1 0.000 0.000 0.858 0.858 application.py:237(<lambda>) >>> 1 0.000 0.000 0.000 0.000 httpserver.py:301(xstart_response) >>> 1 0.000 0.000 0.858 0.858 application.py:232(handle_with_processors) >>> 2 0.000 0.000 0.000 0.000 utils.py:1234(clear) >>> 1 0.000 0.000 0.000 0.000 os.py:446(get) >>> 1 0.000 0.000 0.000 0.000 application.py:398(is_class) >>> 1 0.000 0.000 0.000 0.000 utils.py:1246(iteritems) >>> >>> >>> Why is the run time of the query for the 500m tile in compariosn to the 400m tile so high? >>> >>> Are there possibilities to increase the runtime? >>> >>> >>> -- >>> *Moritz Liebelt, B. Sc.* >>> >>> Murgstraße 19 >>> 76437 Rastatt >>> E-Mail: mor...@go... >>> >>> ------------------------------------------------------------ >>> ------------------ >>> >>> _______________________________________________ >>> cx-oracle-users mailing list >>> cx-...@li... >>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users >>> >>> >> >> ------------------------------------------------------------ >> ------------------ >> >> _______________________________________________ >> cx-oracle-users mailing list >> cx-...@li... >> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users >> >> > > > -- > *Moritz Liebelt, B. Sc.* > > Murgstraße 19 > 76437 Rastatt > E-Mail: mor...@go... > |
|
From: Moritz L. <mor...@go...> - 2016-08-03 09:47:23
|
Hi Anthony, thanks for your help. 1) the size of the geometries are not the same in both tiles. I have in 400m tile 4137 number of output rows with all in all 23.707 coordinates. In the 500m tile I have 6241 number of output rows with all in all 35.727 coordinates. So it is less than half of the 400m tile. But the time to fetch the data out of the database is increasing from 0,26s to 14 seconds. I cannot understand this time difference. 2.) putting the output from attribute.aslist() in a local variable and then processing it instead doesn´t make a difference the time is nearly the same. 3) I created a copy of a test-database with my tables and geometries but it is really big... I did some manipulations so the best for me is to duplicate it and so you can test on real conditions! Also I added the python script to fetch the data ( web.py + cx_Oracle) See the links: 1.) Webserver: https://drive.google.com/open?id=0B5AUL4-roeC4dmM2VVZjaGpoUVBEeGlZRXBGT2U1WVAxSXNV 2.) Database: https://drive.google.com/open?id=0B5AUL4-roeC4MDNsTnpOWkJFVms 2016-08-03 0:54 GMT+02:00 Anthony Tuininga <ant...@gm...>: > Hi Moritz, > > Just a few notes that may be helpful in this regard. > > 1) Are the size of the geometries the same in both the 400m and 500m > tiles? The output seems to suggest that is not the case but it would be > good to confirm. > > 2) Every access to an attribute will fetch it from Oracle so if you plan > to access an expensive attribute more than once, store it in a local > variable. I don't believe this is an issue in your case but you can verify > by simply putting the output from attribute.aslist() in a local variable > and then processing it instead. > > 3) Can you provide a test case? The best would be a PL/SQL script that > creates a table and generates the geometries and a Python script that > retrieves them -- without the integration with the web client. If you can > provide this I can see if there is something going on in the backend that I > can fix or if that is "just the way it is". > > Anthony > > On Tue, Aug 2, 2016 at 2:20 AM, Moritz Liebelt < > mor...@go...> wrote: > >> Hi, >> >> >> I have some questions about cx_Oracle. >> I work on a project for my Masterthesis. And a part will be to create a >> webserver and to fetch the data from a database and send it back to client. >> My Institute is using Oracle db. Because of that I am using the module >> cx_Oracle to create an interface to my database (unreleased version). >> >> Inside my database I have 3d data (buildings) and I structures these data >> in tiles. >> So now I will compare the query of two tiles for example all the data in >> 400m tile and all the data in 500m tile. As a result I get a very high >> difference. >> >> 400m (4136rows) -> 0,26s >> 500m (6200 rows) -> 14s >> >> Of course it twill need more time because of the higher number of output >> rows. But this seems to me incorrect. >> Do you have an idea what the problem could be? >> Does it depends on the arraysize of the cursor? >> >> If you have some tips how to increase the performance it would be very >> nice! >> >> Here you can see the code >> >> >> 1. import cx_Oracle >> 2. import json >> 3. import web >> 4. >> 5. urls = ( >> 6. "/", "index", >> 7. "/grid", "grid", >> 8. ) >> 9. app = web.application(urls, globals(),web.profiler ) >> 10. web.config.debug = True >> 11. >> 12. >> 13. connection = cx_Oracle.Connection("TEST_3D/limo1013@10.40.33.160:1521/sdetest") >> 14. typeObj = connection.gettype("MDSYS.SDO_GEOMETRY") >> 15. >> 16. class index: >> 17. def GET(self): >> 18. return "hallo moritz " >> 19. >> 20. class grid: >> 21. >> 22. def GET(self): >> 23. web.header('Access-Control-Allow-Origin', '*') >> 24. web.header('Access-Control-Allow-Credentials', 'true') >> 25. web.header('Content-Type', 'application/json') >> 26. >> 27. cursor = connection.cursor() >> 28. cursor.arraysize = 6300 # default = 50 >> 29. cursor.execute("""SELECT a.id , c.geometry, d.Classname FROM building a, THEMATIC_SURFACE b, SURFACE_GEOMETRY c, OBJECTCLASS d WHERE a.grid_id_500 = 2728 AND a.id = b.BUILDING_ID AND b.LOD2_MULTI_SURFACE_ID = c.ROOT_ID AND c.GEOMETRY IS NOT NULL AND b.OBJECTCLASS_ID = d.ID""") >> 30. >> 31. def geometry_to_points(obj): >> 32. return zip(*[iter(obj.SDO_ORDINATES.aslist())]*3) >> 33. >> 34. result = [] >> 35. for id, geometry, classname in cursor: >> 36. result.append({ >> 37. "building_nr": id, "geometry": { >> 38. "type": "polygon", >> 39. "coordinates": [geometry_to_points(geometry)], >> 40. }, "polygon_typ": classname, >> 41. }) >> 42. return json.dumps(result) >> 43. >> 44. # Aufruf der App >> 45. if __name__ == "__main__": >> 46. app.run(web.profiler) >> >> >> *500m tile:* >> >> *took 14.1860001087 seconds* >> 6880 function calls (6871 primitive calls) in 47.042 seconds >> >> Ordered by: internal time, call count >> List reduced from 47 to 40 due to restriction <40> >> >> ncalls tottime percall cumtime percall filename:lineno(function) >> 6241 45.735 0.007 45.735 0.007 template.py:31(geometry_to_points) >> 1 0.735 0.735 46.894 46.894 template.py:22(GET) >> 1 0.403 0.403 0.403 0.403 encoder.py:212(iterencode) >> 360 0.074 0.000 0.107 0.000 application.py:663(check) >> 1 0.038 0.038 46.932 46.932 application.py:389(handle_class) >> 182 0.033 0.000 0.033 0.000 genericpath.py:23(exists) >> 1 0.013 0.013 0.416 0.416 encoder.py:186(encode) >> 1 0.008 0.008 0.424 0.424 __init__.py:193(dumps) >> 1 0.001 0.001 0.109 0.109 application.py:659(__call__) >> 1 0.000 0.000 0.000 0.000 application.py:338(load) >> 13 0.000 0.000 0.000 0.000 utf_8.py:15(decode) >> 1 0.000 0.000 47.042 47.042 application.py:269(wsgi) >> 1 0.000 0.000 0.000 0.000 httpserver.py:308(log) >> 2 0.000 0.000 0.000 0.000 utils.py:521(re_subm) >> 1 0.000 0.000 0.000 0.000 basehttpserver.py:475(log_date_time_string) >> 1 0.000 0.000 0.000 0.000 utils.py:1210(clear_all) >> 5/1 0.000 0.000 47.041 47.041 application.py:233(process) >> 2 0.000 0.000 0.000 0.000 utils.py:487(__call__) >> 1 0.000 0.000 47.041 47.041 application.py:579(processor) >> 8 0.000 0.000 0.000 0.000 utils.py:356(safestr) >> 1 0.000 0.000 0.000 0.000 application.py:426(_match) >> 3 0.000 0.000 0.000 0.000 utils.py:536(group) >> 1 0.000 0.000 0.000 0.000 __init__.py:2035(start_response) >> 1 0.000 0.000 46.932 46.932 application.py:388(_delegate) >> 3/1 0.000 0.000 47.041 47.041 application.py:564(processor) >> 1 0.000 0.000 0.000 0.000 application.py:75(reload_mapping) >> 13 0.000 0.000 0.000 0.000 utils.py:1223(__setitem__) >> 1 0.000 0.000 0.000 0.000 application.py:114(init_mapping) >> 7 0.000 0.000 0.000 0.000 utils.py:545(take) >> 3 0.000 0.000 0.000 0.000 webapi.py:260(header) >> 1 0.000 0.000 0.000 0.000 application.py:97(_unload) >> 1 0.000 0.000 46.932 46.932 application.py:228(handle) >> 1 0.000 0.000 0.000 0.000 utils.py:1201(__init__) >> 1 0.000 0.000 0.000 0.000 httpserver.py:301(xstart_response) >> 4/1 0.000 0.000 47.041 47.041 application.py:237(<lambda>) >> 1 0.000 0.000 0.000 0.000 application.py:109(_cleanup) >> 1 0.000 0.000 47.041 47.041 application.py:232(handle_with_processors) >> 1 0.000 0.000 0.000 0.000 os.py:446(get) >> 2 0.000 0.000 0.000 0.000 utils.py:1234(clear) >> 1 0.000 0.000 0.000 0.000 utils.py:1246(iteritems) >> >> >> *400m tile:* >> >> *took 0.258000135422 seconds* >> 4774 function calls (4765 primitive calls) in 0.859 seconds >> >> Ordered by: internal time, call count >> List reduced from 45 to 40 due to restriction <40> >> >> ncalls tottime percall cumtime percall filename:lineno(function) >> 1 0.348 0.348 0.737 0.737 template.py:22(GET) >> 1 0.223 0.223 0.223 0.223 encoder.py:212(iterencode) >> 4137 0.153 0.000 0.153 0.000 template.py:31(geometry_to_points) >> 360 0.064 0.000 0.093 0.000 application.py:663(check) >> 182 0.029 0.000 0.029 0.000 genericpath.py:23(exists) >> 1 0.026 0.026 0.763 0.763 application.py:389(handle_class) >> 1 0.009 0.009 0.232 0.232 encoder.py:186(encode) >> 1 0.004 0.004 0.236 0.236 __init__.py:193(dumps) >> 1 0.001 0.001 0.094 0.094 application.py:659(__call__) >> 1 0.000 0.000 0.000 0.000 application.py:338(load) >> 13 0.000 0.000 0.000 0.000 utf_8.py:15(decode) >> 2 0.000 0.000 0.000 0.000 utils.py:521(re_subm) >> 1 0.000 0.000 0.859 0.859 application.py:269(wsgi) >> 1 0.000 0.000 0.000 0.000 httpserver.py:308(log) >> 1 0.000 0.000 0.000 0.000 basehttpserver.py:475(log_date_time_string) >> 5/1 0.000 0.000 0.858 0.858 application.py:233(process) >> 1 0.000 0.000 0.858 0.858 application.py:579(processor) >> 3 0.000 0.000 0.000 0.000 utils.py:536(group) >> 8 0.000 0.000 0.000 0.000 utils.py:356(safestr) >> 1 0.000 0.000 0.000 0.000 utils.py:1210(clear_all) >> 1 0.000 0.000 0.000 0.000 application.py:75(reload_mapping) >> 2 0.000 0.000 0.000 0.000 utils.py:487(__call__) >> 1 0.000 0.000 0.000 0.000 application.py:426(_match) >> 3/1 0.000 0.000 0.858 0.858 application.py:564(processor) >> 1 0.000 0.000 0.763 0.763 application.py:388(_delegate) >> 1 0.000 0.000 0.000 0.000 __init__.py:2035(start_response) >> 1 0.000 0.000 0.000 0.000 application.py:114(init_mapping) >> 13 0.000 0.000 0.000 0.000 utils.py:1223(__setitem__) >> 1 0.000 0.000 0.763 0.763 application.py:228(handle) >> 7 0.000 0.000 0.000 0.000 utils.py:545(take) >> 3 0.000 0.000 0.000 0.000 webapi.py:260(header) >> 1 0.000 0.000 0.000 0.000 application.py:97(_unload) >> 1 0.000 0.000 0.000 0.000 application.py:109(_cleanup) >> 4/1 0.000 0.000 0.858 0.858 application.py:237(<lambda>) >> 1 0.000 0.000 0.000 0.000 httpserver.py:301(xstart_response) >> 1 0.000 0.000 0.858 0.858 application.py:232(handle_with_processors) >> 2 0.000 0.000 0.000 0.000 utils.py:1234(clear) >> 1 0.000 0.000 0.000 0.000 os.py:446(get) >> 1 0.000 0.000 0.000 0.000 application.py:398(is_class) >> 1 0.000 0.000 0.000 0.000 utils.py:1246(iteritems) >> >> >> Why is the run time of the query for the 500m tile in compariosn to the 400m tile so high? >> >> Are there possibilities to increase the runtime? >> >> >> -- >> *Moritz Liebelt, B. Sc.* >> >> Murgstraße 19 >> 76437 Rastatt >> E-Mail: mor...@go... >> >> >> ------------------------------------------------------------------------------ >> >> _______________________________________________ >> cx-oracle-users mailing list >> cx-...@li... >> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users >> >> > > > ------------------------------------------------------------------------------ > > _______________________________________________ > cx-oracle-users mailing list > cx-...@li... > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users > > -- *Moritz Liebelt, B. Sc.* Murgstraße 19 76437 Rastatt E-Mail: mor...@go... |
|
From: Moritz L. <mor...@go...> - 2016-08-03 09:26:17
|
import cx_Oracle
import json
import web
urls = (
"/", "index",
"/grid", "grid",
)
app = web.application(urls, globals())
web.config.debug = True
connection = cx_Oracle.Connection("TEST_3D/limo1013@10.40.33.160:1521/sdetest")
typeObj = connection.gettype("MDSYS.SDO_GEOMETRY")
class index:
def GET(self):
return "hallo moritz "
class grid:
def GET(self):
web.header('Access-Control-Allow-Origin', '*')
web.header('Access-Control-Allow-Credentials', 'true')
web.header('Content-Type', 'application/json')
cursor = connection.cursor()
cursor.arraysize = 100000 # default = 50
cursor.execute(
#"""SELECT a.id , c.geometry, d.Classname FROM building a, THEMATIC_SURFACE b, SURFACE_GEOMETRY c, OBJECTCLASS d WHERE a.grid_id_500 = 4158 AND a.id = b.BUILDING_ID AND b.LOD2_MULTI_SURFACE_ID = c.ROOT_ID AND c.GEOMETRY IS NOT NULL AND b.OBJECTCLASS_ID = d.ID""")# 400m
"""SELECT a.id , c.geometry, d.Classname FROM building a, THEMATIC_SURFACE b, SURFACE_GEOMETRY c, OBJECTCLASS d WHERE a.grid_id_500 = 2728 AND a.id = b.BUILDING_ID AND b.LOD2_MULTI_SURFACE_ID = c.ROOT_ID AND c.GEOMETRY IS NOT NULL AND b.OBJECTCLASS_ID = d.ID""")# 500m
def geometry_to_points(obj):
return zip(*[iter(obj.SDO_ORDINATES.aslist())] * 3)
result = []
for id, geometry, classname in cursor:
mo = geometry.SDO_ORDINATES.aslist()
result.append({
"building_nr":id,"geometry": {
"type":"polygon","coordinates":zip(*[iter(mo)] * 3),}, "polygon_typ":classname,})
return json.dumps(result)
if __name__ == "__main__":
app.run(web.profiler)
|
|
From: Anthony T. <ant...@gm...> - 2016-08-02 22:54:27
|
Hi Moritz,
Just a few notes that may be helpful in this regard.
1) Are the size of the geometries the same in both the 400m and 500m tiles?
The output seems to suggest that is not the case but it would be good to
confirm.
2) Every access to an attribute will fetch it from Oracle so if you plan to
access an expensive attribute more than once, store it in a local variable.
I don't believe this is an issue in your case but you can verify by simply
putting the output from attribute.aslist() in a local variable and then
processing it instead.
3) Can you provide a test case? The best would be a PL/SQL script that
creates a table and generates the geometries and a Python script that
retrieves them -- without the integration with the web client. If you can
provide this I can see if there is something going on in the backend that I
can fix or if that is "just the way it is".
Anthony
On Tue, Aug 2, 2016 at 2:20 AM, Moritz Liebelt <
mor...@go...> wrote:
> Hi,
>
>
> I have some questions about cx_Oracle.
> I work on a project for my Masterthesis. And a part will be to create a
> webserver and to fetch the data from a database and send it back to client.
> My Institute is using Oracle db. Because of that I am using the module
> cx_Oracle to create an interface to my database (unreleased version).
>
> Inside my database I have 3d data (buildings) and I structures these data
> in tiles.
> So now I will compare the query of two tiles for example all the data in
> 400m tile and all the data in 500m tile. As a result I get a very high
> difference.
>
> 400m (4136rows) -> 0,26s
> 500m (6200 rows) -> 14s
>
> Of course it twill need more time because of the higher number of output
> rows. But this seems to me incorrect.
> Do you have an idea what the problem could be?
> Does it depends on the arraysize of the cursor?
>
> If you have some tips how to increase the performance it would be very
> nice!
>
> Here you can see the code
>
>
> 1. import cx_Oracle
> 2. import json
> 3. import web
> 4.
> 5. urls = (
> 6. "/", "index",
> 7. "/grid", "grid",
> 8. )
> 9. app = web.application(urls, globals(),web.profiler )
> 10. web.config.debug = True
> 11.
> 12.
> 13. connection = cx_Oracle.Connection("TEST_3D/limo1013@10.40.33.160:1521/sdetest")
> 14. typeObj = connection.gettype("MDSYS.SDO_GEOMETRY")
> 15.
> 16. class index:
> 17. def GET(self):
> 18. return "hallo moritz "
> 19.
> 20. class grid:
> 21.
> 22. def GET(self):
> 23. web.header('Access-Control-Allow-Origin', '*')
> 24. web.header('Access-Control-Allow-Credentials', 'true')
> 25. web.header('Content-Type', 'application/json')
> 26.
> 27. cursor = connection.cursor()
> 28. cursor.arraysize = 6300 # default = 50
> 29. cursor.execute("""SELECT a.id , c.geometry, d.Classname FROM building a, THEMATIC_SURFACE b, SURFACE_GEOMETRY c, OBJECTCLASS d WHERE a.grid_id_500 = 2728 AND a.id = b.BUILDING_ID AND b.LOD2_MULTI_SURFACE_ID = c.ROOT_ID AND c.GEOMETRY IS NOT NULL AND b.OBJECTCLASS_ID = d.ID""")
> 30.
> 31. def geometry_to_points(obj):
> 32. return zip(*[iter(obj.SDO_ORDINATES.aslist())]*3)
> 33.
> 34. result = []
> 35. for id, geometry, classname in cursor:
> 36. result.append({
> 37. "building_nr": id, "geometry": {
> 38. "type": "polygon",
> 39. "coordinates": [geometry_to_points(geometry)],
> 40. }, "polygon_typ": classname,
> 41. })
> 42. return json.dumps(result)
> 43.
> 44. # Aufruf der App
> 45. if __name__ == "__main__":
> 46. app.run(web.profiler)
>
>
> *500m tile:*
>
> *took 14.1860001087 seconds*
> 6880 function calls (6871 primitive calls) in 47.042 seconds
>
> Ordered by: internal time, call count
> List reduced from 47 to 40 due to restriction <40>
>
> ncalls tottime percall cumtime percall filename:lineno(function)
> 6241 45.735 0.007 45.735 0.007 template.py:31(geometry_to_points)
> 1 0.735 0.735 46.894 46.894 template.py:22(GET)
> 1 0.403 0.403 0.403 0.403 encoder.py:212(iterencode)
> 360 0.074 0.000 0.107 0.000 application.py:663(check)
> 1 0.038 0.038 46.932 46.932 application.py:389(handle_class)
> 182 0.033 0.000 0.033 0.000 genericpath.py:23(exists)
> 1 0.013 0.013 0.416 0.416 encoder.py:186(encode)
> 1 0.008 0.008 0.424 0.424 __init__.py:193(dumps)
> 1 0.001 0.001 0.109 0.109 application.py:659(__call__)
> 1 0.000 0.000 0.000 0.000 application.py:338(load)
> 13 0.000 0.000 0.000 0.000 utf_8.py:15(decode)
> 1 0.000 0.000 47.042 47.042 application.py:269(wsgi)
> 1 0.000 0.000 0.000 0.000 httpserver.py:308(log)
> 2 0.000 0.000 0.000 0.000 utils.py:521(re_subm)
> 1 0.000 0.000 0.000 0.000 basehttpserver.py:475(log_date_time_string)
> 1 0.000 0.000 0.000 0.000 utils.py:1210(clear_all)
> 5/1 0.000 0.000 47.041 47.041 application.py:233(process)
> 2 0.000 0.000 0.000 0.000 utils.py:487(__call__)
> 1 0.000 0.000 47.041 47.041 application.py:579(processor)
> 8 0.000 0.000 0.000 0.000 utils.py:356(safestr)
> 1 0.000 0.000 0.000 0.000 application.py:426(_match)
> 3 0.000 0.000 0.000 0.000 utils.py:536(group)
> 1 0.000 0.000 0.000 0.000 __init__.py:2035(start_response)
> 1 0.000 0.000 46.932 46.932 application.py:388(_delegate)
> 3/1 0.000 0.000 47.041 47.041 application.py:564(processor)
> 1 0.000 0.000 0.000 0.000 application.py:75(reload_mapping)
> 13 0.000 0.000 0.000 0.000 utils.py:1223(__setitem__)
> 1 0.000 0.000 0.000 0.000 application.py:114(init_mapping)
> 7 0.000 0.000 0.000 0.000 utils.py:545(take)
> 3 0.000 0.000 0.000 0.000 webapi.py:260(header)
> 1 0.000 0.000 0.000 0.000 application.py:97(_unload)
> 1 0.000 0.000 46.932 46.932 application.py:228(handle)
> 1 0.000 0.000 0.000 0.000 utils.py:1201(__init__)
> 1 0.000 0.000 0.000 0.000 httpserver.py:301(xstart_response)
> 4/1 0.000 0.000 47.041 47.041 application.py:237(<lambda>)
> 1 0.000 0.000 0.000 0.000 application.py:109(_cleanup)
> 1 0.000 0.000 47.041 47.041 application.py:232(handle_with_processors)
> 1 0.000 0.000 0.000 0.000 os.py:446(get)
> 2 0.000 0.000 0.000 0.000 utils.py:1234(clear)
> 1 0.000 0.000 0.000 0.000 utils.py:1246(iteritems)
>
>
> *400m tile:*
>
> *took 0.258000135422 seconds*
> 4774 function calls (4765 primitive calls) in 0.859 seconds
>
> Ordered by: internal time, call count
> List reduced from 45 to 40 due to restriction <40>
>
> ncalls tottime percall cumtime percall filename:lineno(function)
> 1 0.348 0.348 0.737 0.737 template.py:22(GET)
> 1 0.223 0.223 0.223 0.223 encoder.py:212(iterencode)
> 4137 0.153 0.000 0.153 0.000 template.py:31(geometry_to_points)
> 360 0.064 0.000 0.093 0.000 application.py:663(check)
> 182 0.029 0.000 0.029 0.000 genericpath.py:23(exists)
> 1 0.026 0.026 0.763 0.763 application.py:389(handle_class)
> 1 0.009 0.009 0.232 0.232 encoder.py:186(encode)
> 1 0.004 0.004 0.236 0.236 __init__.py:193(dumps)
> 1 0.001 0.001 0.094 0.094 application.py:659(__call__)
> 1 0.000 0.000 0.000 0.000 application.py:338(load)
> 13 0.000 0.000 0.000 0.000 utf_8.py:15(decode)
> 2 0.000 0.000 0.000 0.000 utils.py:521(re_subm)
> 1 0.000 0.000 0.859 0.859 application.py:269(wsgi)
> 1 0.000 0.000 0.000 0.000 httpserver.py:308(log)
> 1 0.000 0.000 0.000 0.000 basehttpserver.py:475(log_date_time_string)
> 5/1 0.000 0.000 0.858 0.858 application.py:233(process)
> 1 0.000 0.000 0.858 0.858 application.py:579(processor)
> 3 0.000 0.000 0.000 0.000 utils.py:536(group)
> 8 0.000 0.000 0.000 0.000 utils.py:356(safestr)
> 1 0.000 0.000 0.000 0.000 utils.py:1210(clear_all)
> 1 0.000 0.000 0.000 0.000 application.py:75(reload_mapping)
> 2 0.000 0.000 0.000 0.000 utils.py:487(__call__)
> 1 0.000 0.000 0.000 0.000 application.py:426(_match)
> 3/1 0.000 0.000 0.858 0.858 application.py:564(processor)
> 1 0.000 0.000 0.763 0.763 application.py:388(_delegate)
> 1 0.000 0.000 0.000 0.000 __init__.py:2035(start_response)
> 1 0.000 0.000 0.000 0.000 application.py:114(init_mapping)
> 13 0.000 0.000 0.000 0.000 utils.py:1223(__setitem__)
> 1 0.000 0.000 0.763 0.763 application.py:228(handle)
> 7 0.000 0.000 0.000 0.000 utils.py:545(take)
> 3 0.000 0.000 0.000 0.000 webapi.py:260(header)
> 1 0.000 0.000 0.000 0.000 application.py:97(_unload)
> 1 0.000 0.000 0.000 0.000 application.py:109(_cleanup)
> 4/1 0.000 0.000 0.858 0.858 application.py:237(<lambda>)
> 1 0.000 0.000 0.000 0.000 httpserver.py:301(xstart_response)
> 1 0.000 0.000 0.858 0.858 application.py:232(handle_with_processors)
> 2 0.000 0.000 0.000 0.000 utils.py:1234(clear)
> 1 0.000 0.000 0.000 0.000 os.py:446(get)
> 1 0.000 0.000 0.000 0.000 application.py:398(is_class)
> 1 0.000 0.000 0.000 0.000 utils.py:1246(iteritems)
>
>
> Why is the run time of the query for the 500m tile in compariosn to the 400m tile so high?
>
> Are there possibilities to increase the runtime?
>
>
> --
> *Moritz Liebelt, B. Sc.*
>
> Murgstraße 19
> 76437 Rastatt
> E-Mail: mor...@go...
>
>
> ------------------------------------------------------------------------------
>
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
|
|
From: Moritz L. <mor...@go...> - 2016-08-02 12:14:25
|
sadly that doesn´t change anything. For both options the runtime is the
same...
2016-08-02 12:02 GMT+02:00 Jani Tiainen <re...@gm...>:
> Hi,
>
> I was having my part of fun when working with Django GIS backend.
>
> For a solution I pulled in coordinates as a tuple - it seemed to make
> handling even big geometries (more than 10k points) way faster than
> directly iterating .as_list()
>
> so for example I did this:
>
> self._SDO_ELEM_INFO = sdo_geometry.SDO_ELEM_INFO and tuple([int(v) for v in sdo_geometry.SDO_ELEM_INFO.aslist()]) or None
> self._SDO_ORDINATES = sdo_geometry.SDO_ORDINATES and tuple(sdo_geometry.SDO_ORDINATES.aslist()) or None
>
> Really don't know why that was faster, maybe there happens typecasts
> behind the scenes.
> On 02.08.2016 11:20, Moritz Liebelt wrote:
>
> Hi,
>
>
> I have some questions about cx_Oracle.
> I work on a project for my Masterthesis. And a part will be to create a
> webserver and to fetch the data from a database and send it back to client.
> My Institute is using Oracle db. Because of that I am using the module
> cx_Oracle to create an interface to my database (unreleased version).
>
> Inside my database I have 3d data (buildings) and I structures these data
> in tiles.
> So now I will compare the query of two tiles for example all the data in
> 400m tile and all the data in 500m tile. As a result I get a very high
> difference.
>
> 400m (4136rows) -> 0,26s
> 500m (6200 rows) -> 14s
>
> Of course it twill need more time because of the higher number of output
> rows. But this seems to me incorrect.
> Do you have an idea what the problem could be?
> Does it depends on the arraysize of the cursor?
>
> If you have some tips how to increase the performance it would be very
> nice!
>
> Here you can see the code
>
>
> 1. import cx_Oracle
> 2. import json
> 3. import web
> 4.
> 5. urls = (
> 6. "/", "index",
> 7. "/grid", "grid",
> 8. )
> 9. app = web.application(urls, globals(),web.profiler )
> 10. web.config.debug = True
> 11.
> 12.
> 13. connection = cx_Oracle.Connection("TEST_3D/limo1013@10.40.33.160:1521/sdetest")
> 14. typeObj = connection.gettype("MDSYS.SDO_GEOMETRY")
> 15.
> 16. class index:
> 17. def GET(self):
> 18. return "hallo moritz "
> 19.
> 20. class grid:
> 21.
> 22. def GET(self):
> 23. web.header('Access-Control-Allow-Origin', '*')
> 24. web.header('Access-Control-Allow-Credentials', 'true')
> 25. web.header('Content-Type', 'application/json')
> 26.
> 27. cursor = connection.cursor()
> 28. cursor.arraysize = 6300 # default = 50
> 29. cursor.execute("""SELECT a.id , c.geometry, d.Classname FROM building a, THEMATIC_SURFACE b, SURFACE_GEOMETRY c, OBJECTCLASS d WHERE a.grid_id_500 = 2728 AND a.id = b.BUILDING_ID AND b.LOD2_MULTI_SURFACE_ID = c.ROOT_ID AND c.GEOMETRY IS NOT NULL AND b.OBJECTCLASS_ID = d.ID""")
> 30.
> 31. def geometry_to_points(obj):
> 32. return zip(*[iter(obj.SDO_ORDINATES.aslist())]*3)
> 33.
> 34. result = []
> 35. for id, geometry, classname in cursor:
> 36. result.append({
> 37. "building_nr": id, "geometry": {
> 38. "type": "polygon",
> 39. "coordinates": [geometry_to_points(geometry)],
> 40. }, "polygon_typ": classname,
> 41. })
> 42. return json.dumps(result)
> 43.
> 44. # Aufruf der App
> 45. if __name__ == "__main__":
> 46. app.run(web.profiler)
>
>
> *500m tile:*
>
> *took 14.1860001087 seconds*
> 6880 function calls (6871 primitive calls) in 47.042 seconds
>
> Ordered by: internal time, call count
> List reduced from 47 to 40 due to restriction <40>
>
> ncalls tottime percall cumtime percall filename:lineno(function)
> 6241 45.735 0.007 45.735 0.007 template.py:31(geometry_to_points)
> 1 0.735 0.735 46.894 46.894 template.py:22(GET)
> 1 0.403 0.403 0.403 0.403 encoder.py:212(iterencode)
> 360 0.074 0.000 0.107 0.000 application.py:663(check)
> 1 0.038 0.038 46.932 46.932 application.py:389(handle_class)
> 182 0.033 0.000 0.033 0.000 genericpath.py:23(exists)
> 1 0.013 0.013 0.416 0.416 encoder.py:186(encode)
> 1 0.008 0.008 0.424 0.424 __init__.py:193(dumps)
> 1 0.001 0.001 0.109 0.109 application.py:659(__call__)
> 1 0.000 0.000 0.000 0.000 application.py:338(load)
> 13 0.000 0.000 0.000 0.000 utf_8.py:15(decode)
> 1 0.000 0.000 47.042 47.042 application.py:269(wsgi)
> 1 0.000 0.000 0.000 0.000 httpserver.py:308(log)
> 2 0.000 0.000 0.000 0.000 utils.py:521(re_subm)
> 1 0.000 0.000 0.000 0.000 basehttpserver.py:475(log_date_time_string)
> 1 0.000 0.000 0.000 0.000 utils.py:1210(clear_all)
> 5/1 0.000 0.000 47.041 47.041 application.py:233(process)
> 2 0.000 0.000 0.000 0.000 utils.py:487(__call__)
> 1 0.000 0.000 47.041 47.041 application.py:579(processor)
> 8 0.000 0.000 0.000 0.000 utils.py:356(safestr)
> 1 0.000 0.000 0.000 0.000 application.py:426(_match)
> 3 0.000 0.000 0.000 0.000 utils.py:536(group)
> 1 0.000 0.000 0.000 0.000 __init__.py:2035(start_response)
> 1 0.000 0.000 46.932 46.932 application.py:388(_delegate)
> 3/1 0.000 0.000 47.041 47.041 application.py:564(processor)
> 1 0.000 0.000 0.000 0.000 application.py:75(reload_mapping)
> 13 0.000 0.000 0.000 0.000 utils.py:1223(__setitem__)
> 1 0.000 0.000 0.000 0.000 application.py:114(init_mapping)
> 7 0.000 0.000 0.000 0.000 utils.py:545(take)
> 3 0.000 0.000 0.000 0.000 webapi.py:260(header)
> 1 0.000 0.000 0.000 0.000 application.py:97(_unload)
> 1 0.000 0.000 46.932 46.932 application.py:228(handle)
> 1 0.000 0.000 0.000 0.000 utils.py:1201(__init__)
> 1 0.000 0.000 0.000 0.000 httpserver.py:301(xstart_response)
> 4/1 0.000 0.000 47.041 47.041 application.py:237(<lambda>)
> 1 0.000 0.000 0.000 0.000 application.py:109(_cleanup)
> 1 0.000 0.000 47.041 47.041 application.py:232(handle_with_processors)
> 1 0.000 0.000 0.000 0.000 os.py:446(get)
> 2 0.000 0.000 0.000 0.000 utils.py:1234(clear)
> 1 0.000 0.000 0.000 0.000 utils.py:1246(iteritems)
>
>
> *400m tile:*
>
> *took 0.258000135422 seconds*
> 4774 function calls (4765 primitive calls) in 0.859 seconds
>
> Ordered by: internal time, call count
> List reduced from 45 to 40 due to restriction <40>
>
> ncalls tottime percall cumtime percall filename:lineno(function)
> 1 0.348 0.348 0.737 0.737 template.py:22(GET)
> 1 0.223 0.223 0.223 0.223 encoder.py:212(iterencode)
> 4137 0.153 0.000 0.153 0.000 template.py:31(geometry_to_points)
> 360 0.064 0.000 0.093 0.000 application.py:663(check)
> 182 0.029 0.000 0.029 0.000 genericpath.py:23(exists)
> 1 0.026 0.026 0.763 0.763 application.py:389(handle_class)
> 1 0.009 0.009 0.232 0.232 encoder.py:186(encode)
> 1 0.004 0.004 0.236 0.236 __init__.py:193(dumps)
> 1 0.001 0.001 0.094 0.094 application.py:659(__call__)
> 1 0.000 0.000 0.000 0.000 application.py:338(load)
> 13 0.000 0.000 0.000 0.000 utf_8.py:15(decode)
> 2 0.000 0.000 0.000 0.000 utils.py:521(re_subm)
> 1 0.000 0.000 0.859 0.859 application.py:269(wsgi)
> 1 0.000 0.000 0.000 0.000 httpserver.py:308(log)
> 1 0.000 0.000 0.000 0.000 basehttpserver.py:475(log_date_time_string)
> 5/1 0.000 0.000 0.858 0.858 application.py:233(process)
> 1 0.000 0.000 0.858 0.858 application.py:579(processor)
> 3 0.000 0.000 0.000 0.000 utils.py:536(group)
> 8 0.000 0.000 0.000 0.000 utils.py:356(safestr)
> 1 0.000 0.000 0.000 0.000 utils.py:1210(clear_all)
> 1 0.000 0.000 0.000 0.000 application.py:75(reload_mapping)
> 2 0.000 0.000 0.000 0.000 utils.py:487(__call__)
> 1 0.000 0.000 0.000 0.000 application.py:426(_match)
> 3/1 0.000 0.000 0.858 0.858 application.py:564(processor)
> 1 0.000 0.000 0.763 0.763 application.py:388(_delegate)
> 1 0.000 0.000 0.000 0.000 __init__.py:2035(start_response)
> 1 0.000 0.000 0.000 0.000 application.py:114(init_mapping)
> 13 0.000 0.000 0.000 0.000 utils.py:1223(__setitem__)
> 1 0.000 0.000 0.763 0.763 application.py:228(handle)
> 7 0.000 0.000 0.000 0.000 utils.py:545(take)
> 3 0.000 0.000 0.000 0.000 webapi.py:260(header)
> 1 0.000 0.000 0.000 0.000 application.py:97(_unload)
> 1 0.000 0.000 0.000 0.000 application.py:109(_cleanup)
> 4/1 0.000 0.000 0.858 0.858 application.py:237(<lambda>)
> 1 0.000 0.000 0.000 0.000 httpserver.py:301(xstart_response)
> 1 0.000 0.000 0.858 0.858 application.py:232(handle_with_processors)
> 2 0.000 0.000 0.000 0.000 utils.py:1234(clear)
> 1 0.000 0.000 0.000 0.000 os.py:446(get)
> 1 0.000 0.000 0.000 0.000 application.py:398(is_class)
> 1 0.000 0.000 0.000 0.000 utils.py:1246(iteritems)
>
> Why is the run time of the query for the 500m tile in compariosn to the 400m tile so high?
>
> Are there possibilities to increase the runtime?
>
>
> --
> *Moritz Liebelt, B. Sc.*
>
> Murgstraße 19
> 76437 Rastatt
> E-Mail: mor...@go...
>
>
> ------------------------------------------------------------------------------
>
>
>
> _______________________________________________
> cx-oracle-users mailing lis...@li...https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
>
>
> ------------------------------------------------------------------------------
>
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
--
*Moritz Liebelt, B. Sc.*
Murgstraße 19
76437 Rastatt
E-Mail: mor...@go...
|
|
From: Jani T. <re...@gm...> - 2016-08-02 10:02:19
|
Hi,
I was having my part of fun when working with Django GIS backend.
For a solution I pulled in coordinates as a tuple - it seemed to make
handling even big geometries (more than 10k points) way faster than
directly iterating .as_list()
so for example I did this:
self._SDO_ELEM_INFO = sdo_geometry.SDO_ELEM_INFO and tuple([int(v) for v in sdo_geometry.SDO_ELEM_INFO.aslist()]) or None
self._SDO_ORDINATES = sdo_geometry.SDO_ORDINATES and tuple(sdo_geometry.SDO_ORDINATES.aslist()) or None
Really don't know why that was faster, maybe there happens typecasts
behind the scenes.
On 02.08.2016 11:20, Moritz Liebelt wrote:
> Hi,
>
>
> I have some questions about cx_Oracle.
> I work on a project for my Masterthesis. And a part will be to create
> a webserver and to fetch the data from a database and send it back to
> client.
> My Institute is using Oracle db. Because of that I am using the module
> cx_Oracle to create an interface to my database (unreleased version).
>
> Inside my database I have 3d data (buildings) and I structures these
> data in tiles.
> So now I will compare the query of two tiles for example all the data
> in 400m tile and all the data in 500m tile. As a result I get a very
> high difference.
>
> 400m (4136rows) -> 0,26s
> 500m (6200 rows) -> 14s
>
> Of course it twill need more time because of the higher number of
> output rows. But this seems to me incorrect.
> Do you have an idea what the problem could be?
> Does it depends on the arraysize of the cursor?
>
> If you have some tips how to increase the performance it would be very
> nice!
>
> Here you can see the code
>
> 1. import cx_Oracle
> 2. import json
> 3. import web
> 4.
> 5. urls = (
> 6. "/", "index",
> 7. "/grid", "grid",
> 8. )
> 9. app = web.application(urls, globals(),web.profiler )
> 10. web.config.debug = True
>11.
>12.
> 13. connection =
> cx_Oracle.Connection("TEST_3D/limo1013@10.40.33.160:1521/sdetest
> <http://limo1013@10.40.33.160:1521/sdetest>")
> 14. typeObj = connection.gettype("MDSYS.SDO_GEOMETRY")
>15.
> 16. class index:
> 17. def GET(self):
> 18. return "hallo moritz "
> 19. **
> 20. class grid:
>21.
> 22. def GET(self):
> 23. web.header('Access-Control-Allow-Origin', '*')
> 24. web.header('Access-Control-Allow-Credentials', 'true')
> 25. web.header('Content-Type', 'application/json')
>26.
> 27. cursor = connection.cursor()
> 28. cursor.arraysize = 6300 # default = 50
> 29. cursor.execute("""SELECT a.id <http://a.id/> , c.geometry,
> d.Classname FROM building a, THEMATIC_SURFACE b, SURFACE_GEOMETRY
> c, OBJECTCLASS d WHERE a.grid_id_500 = 2728 AND a.id
> <http://a.id/> = b.BUILDING_ID AND b.LOD2_MULTI_SURFACE_ID =
> c.ROOT_ID AND c.GEOMETRY IS NOT NULL AND b.OBJECTCLASS_ID = d.ID""")
>30.
> 31. def geometry_to_points(obj):
> 32. return zip(*[iter(obj.SDO_ORDINATES.aslist())]*3)
>33.
> 34. result = []
> 35. for id, geometry, classname in cursor:
> 36. result.append({
> 37. "building_nr": id, "geometry": {
> 38. "type": "polygon",
> 39. "coordinates": [geometry_to_points(geometry)],
> 40. }, "polygon_typ": classname,
> 41. })
> 42. return json.dumps(result)
>43.
> 44. # Aufruf der App
> 45. if __name__ == "__main__":
> 46. app.run(web.profiler)
>
> *500m tile:*
>
> *took 14.1860001087 seconds*
> 6880 function calls (6871 primitive calls) in 47.042 seconds
>
> Ordered by: internal time, call count
> List reduced from 47 to 40 due to restriction <40>
>
> ncalls tottime percall cumtime percall filename:lineno(function)
> 6241 45.735 0.007 45.735 0.007 template.py:31(geometry_to_points)
> 1 0.735 0.735 46.894 46.894 template.py:22(GET)
> 1 0.403 0.403 0.403 0.403 encoder.py:212(iterencode)
> 360 0.074 0.000 0.107 0.000 application.py:663(check)
> 1 0.038 0.038 46.932 46.932 application.py:389(handle_class)
> 182 0.033 0.000 0.033 0.000 genericpath.py:23(exists)
> 1 0.013 0.013 0.416 0.416 encoder.py:186(encode)
> 1 0.008 0.008 0.424 0.424 __init__.py:193(dumps)
> 1 0.001 0.001 0.109 0.109 application.py:659(__call__)
> 1 0.000 0.000 0.000 0.000 application.py:338(load)
> 13 0.000 0.000 0.000 0.000 utf_8.py:15(decode)
> 1 0.000 0.000 47.042 47.042 application.py:269(wsgi)
> 1 0.000 0.000 0.000 0.000 httpserver.py:308(log)
> 2 0.000 0.000 0.000 0.000 utils.py:521(re_subm)
> 1 0.000 0.000 0.000 0.000 basehttpserver.py:475(log_date_time_string)
> 1 0.000 0.000 0.000 0.000 utils.py:1210(clear_all)
> 5/1 0.000 0.000 47.041 47.041 application.py:233(process)
> 2 0.000 0.000 0.000 0.000 utils.py:487(__call__)
> 1 0.000 0.000 47.041 47.041 application.py:579(processor)
> 8 0.000 0.000 0.000 0.000 utils.py:356(safestr)
> 1 0.000 0.000 0.000 0.000 application.py:426(_match)
> 3 0.000 0.000 0.000 0.000 utils.py:536(group)
> 1 0.000 0.000 0.000 0.000 __init__.py:2035(start_response)
> 1 0.000 0.000 46.932 46.932 application.py:388(_delegate)
> 3/1 0.000 0.000 47.041 47.041 application.py:564(processor)
> 1 0.000 0.000 0.000 0.000 application.py:75(reload_mapping)
> 13 0.000 0.000 0.000 0.000 utils.py:1223(__setitem__)
> 1 0.000 0.000 0.000 0.000 application.py:114(init_mapping)
> 7 0.000 0.000 0.000 0.000 utils.py:545(take)
> 3 0.000 0.000 0.000 0.000 webapi.py:260(header)
> 1 0.000 0.000 0.000 0.000 application.py:97(_unload)
> 1 0.000 0.000 46.932 46.932 application.py:228(handle)
> 1 0.000 0.000 0.000 0.000 utils.py:1201(__init__)
> 1 0.000 0.000 0.000 0.000 httpserver.py:301(xstart_response)
> 4/1 0.000 0.000 47.041 47.041 application.py:237(<lambda>)
> 1 0.000 0.000 0.000 0.000 application.py:109(_cleanup)
> 1 0.000 0.000 47.041 47.041 application.py:232(handle_with_processors)
> 1 0.000 0.000 0.000 0.000 os.py:446(get)
> 2 0.000 0.000 0.000 0.000 utils.py:1234(clear)
> 1 0.000 0.000 0.000 0.000 utils.py:1246(iteritems)
>
> *400m tile:*
> *took 0.258000135422 seconds*
> 4774 function calls (4765 primitive calls) in 0.859 seconds
>
> Ordered by: internal time, call count
> List reduced from 45 to 40 due to restriction <40>
>
> ncalls tottime percall cumtime percall filename:lineno(function)
> 1 0.348 0.348 0.737 0.737 template.py:22(GET)
> 1 0.223 0.223 0.223 0.223 encoder.py:212(iterencode)
> 4137 0.153 0.000 0.153 0.000 template.py:31(geometry_to_points)
> 360 0.064 0.000 0.093 0.000 application.py:663(check)
> 182 0.029 0.000 0.029 0.000 genericpath.py:23(exists)
> 1 0.026 0.026 0.763 0.763 application.py:389(handle_class)
> 1 0.009 0.009 0.232 0.232 encoder.py:186(encode)
> 1 0.004 0.004 0.236 0.236 __init__.py:193(dumps)
> 1 0.001 0.001 0.094 0.094 application.py:659(__call__)
> 1 0.000 0.000 0.000 0.000 application.py:338(load)
> 13 0.000 0.000 0.000 0.000 utf_8.py:15(decode)
> 2 0.000 0.000 0.000 0.000 utils.py:521(re_subm)
> 1 0.000 0.000 0.859 0.859 application.py:269(wsgi)
> 1 0.000 0.000 0.000 0.000 httpserver.py:308(log)
> 1 0.000 0.000 0.000 0.000 basehttpserver.py:475(log_date_time_string)
> 5/1 0.000 0.000 0.858 0.858 application.py:233(process)
> 1 0.000 0.000 0.858 0.858 application.py:579(processor)
> 3 0.000 0.000 0.000 0.000 utils.py:536(group)
> 8 0.000 0.000 0.000 0.000 utils.py:356(safestr)
> 1 0.000 0.000 0.000 0.000 utils.py:1210(clear_all)
> 1 0.000 0.000 0.000 0.000 application.py:75(reload_mapping)
> 2 0.000 0.000 0.000 0.000 utils.py:487(__call__)
> 1 0.000 0.000 0.000 0.000 application.py:426(_match)
> 3/1 0.000 0.000 0.858 0.858 application.py:564(processor)
> 1 0.000 0.000 0.763 0.763 application.py:388(_delegate)
> 1 0.000 0.000 0.000 0.000 __init__.py:2035(start_response)
> 1 0.000 0.000 0.000 0.000 application.py:114(init_mapping)
> 13 0.000 0.000 0.000 0.000 utils.py:1223(__setitem__)
> 1 0.000 0.000 0.763 0.763 application.py:228(handle)
> 7 0.000 0.000 0.000 0.000 utils.py:545(take)
> 3 0.000 0.000 0.000 0.000 webapi.py:260(header)
> 1 0.000 0.000 0.000 0.000 application.py:97(_unload)
> 1 0.000 0.000 0.000 0.000 application.py:109(_cleanup)
> 4/1 0.000 0.000 0.858 0.858 application.py:237(<lambda>)
> 1 0.000 0.000 0.000 0.000 httpserver.py:301(xstart_response)
> 1 0.000 0.000 0.858 0.858 application.py:232(handle_with_processors)
> 2 0.000 0.000 0.000 0.000 utils.py:1234(clear)
> 1 0.000 0.000 0.000 0.000 os.py:446(get)
> 1 0.000 0.000 0.000 0.000 application.py:398(is_class)
> 1 0.000 0.000 0.000 0.000 utils.py:1246(iteritems)
> Why is the run time of the query for the 500m tile in compariosn to the 400m tile so high?
> Are there possibilities to increase the runtime?
>
> --
> *Moritz Liebelt, B. Sc.*
>
> Murgstraße 19
> 76437 Rastatt
> E-Mail: mor...@go...
> <mailto:mor...@go...>
>
>
> ------------------------------------------------------------------------------
>
>
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
|
|
From: Moritz L. <mor...@go...> - 2016-08-02 08:20:41
|
Hi,
I have some questions about cx_Oracle.
I work on a project for my Masterthesis. And a part will be to create a
webserver and to fetch the data from a database and send it back to client.
My Institute is using Oracle db. Because of that I am using the module
cx_Oracle to create an interface to my database (unreleased version).
Inside my database I have 3d data (buildings) and I structures these data
in tiles.
So now I will compare the query of two tiles for example all the data in
400m tile and all the data in 500m tile. As a result I get a very high
difference.
400m (4136rows) -> 0,26s
500m (6200 rows) -> 14s
Of course it twill need more time because of the higher number of output
rows. But this seems to me incorrect.
Do you have an idea what the problem could be?
Does it depends on the arraysize of the cursor?
If you have some tips how to increase the performance it would be very nice!
Here you can see the code
1. import cx_Oracle
2. import json
3. import web
4.
5. urls = (
6. "/", "index",
7. "/grid", "grid",
8. )
9. app = web.application(urls, globals(),web.profiler )
10. web.config.debug = True
11.
12.
13. connection =
cx_Oracle.Connection("TEST_3D/limo1013@10.40.33.160:1521/sdetest")
14. typeObj = connection.gettype("MDSYS.SDO_GEOMETRY")
15.
16. class index:
17. def GET(self):
18. return "hallo moritz "
19.
20. class grid:
21.
22. def GET(self):
23. web.header('Access-Control-Allow-Origin', '*')
24. web.header('Access-Control-Allow-Credentials', 'true')
25. web.header('Content-Type', 'application/json')
26.
27. cursor = connection.cursor()
28. cursor.arraysize = 6300 # default = 50
29. cursor.execute("""SELECT a.id , c.geometry, d.Classname
FROM building a, THEMATIC_SURFACE b, SURFACE_GEOMETRY c, OBJECTCLASS
d WHERE a.grid_id_500 = 2728 AND a.id = b.BUILDING_ID AND
b.LOD2_MULTI_SURFACE_ID = c.ROOT_ID AND c.GEOMETRY IS NOT NULL AND
b.OBJECTCLASS_ID = d.ID""")
30.
31. def geometry_to_points(obj):
32. return zip(*[iter(obj.SDO_ORDINATES.aslist())]*3)
33.
34. result = []
35. for id, geometry, classname in cursor:
36. result.append({
37. "building_nr": id, "geometry": {
38. "type": "polygon",
39. "coordinates": [geometry_to_points(geometry)],
40. }, "polygon_typ": classname,
41. })
42. return json.dumps(result)
43.
44. # Aufruf der App
45. if __name__ == "__main__":
46. app.run(web.profiler)
*500m tile:*
*took 14.1860001087 seconds*
6880 function calls (6871 primitive calls) in 47.042 seconds
Ordered by: internal time, call count
List reduced from 47 to 40 due to restriction <40>
ncalls tottime percall cumtime percall filename:lineno(function)
6241 45.735 0.007 45.735 0.007 template.py:31(geometry_to_points)
1 0.735 0.735 46.894 46.894 template.py:22(GET)
1 0.403 0.403 0.403 0.403 encoder.py:212(iterencode)
360 0.074 0.000 0.107 0.000 application.py:663(check)
1 0.038 0.038 46.932 46.932 application.py:389(handle_class)
182 0.033 0.000 0.033 0.000 genericpath.py:23(exists)
1 0.013 0.013 0.416 0.416 encoder.py:186(encode)
1 0.008 0.008 0.424 0.424 __init__.py:193(dumps)
1 0.001 0.001 0.109 0.109 application.py:659(__call__)
1 0.000 0.000 0.000 0.000 application.py:338(load)
13 0.000 0.000 0.000 0.000 utf_8.py:15(decode)
1 0.000 0.000 47.042 47.042 application.py:269(wsgi)
1 0.000 0.000 0.000 0.000 httpserver.py:308(log)
2 0.000 0.000 0.000 0.000 utils.py:521(re_subm)
1 0.000 0.000 0.000 0.000
basehttpserver.py:475(log_date_time_string)
1 0.000 0.000 0.000 0.000 utils.py:1210(clear_all)
5/1 0.000 0.000 47.041 47.041 application.py:233(process)
2 0.000 0.000 0.000 0.000 utils.py:487(__call__)
1 0.000 0.000 47.041 47.041 application.py:579(processor)
8 0.000 0.000 0.000 0.000 utils.py:356(safestr)
1 0.000 0.000 0.000 0.000 application.py:426(_match)
3 0.000 0.000 0.000 0.000 utils.py:536(group)
1 0.000 0.000 0.000 0.000 __init__.py:2035(start_response)
1 0.000 0.000 46.932 46.932 application.py:388(_delegate)
3/1 0.000 0.000 47.041 47.041 application.py:564(processor)
1 0.000 0.000 0.000 0.000 application.py:75(reload_mapping)
13 0.000 0.000 0.000 0.000 utils.py:1223(__setitem__)
1 0.000 0.000 0.000 0.000 application.py:114(init_mapping)
7 0.000 0.000 0.000 0.000 utils.py:545(take)
3 0.000 0.000 0.000 0.000 webapi.py:260(header)
1 0.000 0.000 0.000 0.000 application.py:97(_unload)
1 0.000 0.000 46.932 46.932 application.py:228(handle)
1 0.000 0.000 0.000 0.000 utils.py:1201(__init__)
1 0.000 0.000 0.000 0.000 httpserver.py:301(xstart_response)
4/1 0.000 0.000 47.041 47.041 application.py:237(<lambda>)
1 0.000 0.000 0.000 0.000 application.py:109(_cleanup)
1 0.000 0.000 47.041 47.041
application.py:232(handle_with_processors)
1 0.000 0.000 0.000 0.000 os.py:446(get)
2 0.000 0.000 0.000 0.000 utils.py:1234(clear)
1 0.000 0.000 0.000 0.000 utils.py:1246(iteritems)
*400m tile:*
*took 0.258000135422 seconds*
4774 function calls (4765 primitive calls) in 0.859 seconds
Ordered by: internal time, call count
List reduced from 45 to 40 due to restriction <40>
ncalls tottime percall cumtime percall filename:lineno(function)
1 0.348 0.348 0.737 0.737 template.py:22(GET)
1 0.223 0.223 0.223 0.223 encoder.py:212(iterencode)
4137 0.153 0.000 0.153 0.000 template.py:31(geometry_to_points)
360 0.064 0.000 0.093 0.000 application.py:663(check)
182 0.029 0.000 0.029 0.000 genericpath.py:23(exists)
1 0.026 0.026 0.763 0.763 application.py:389(handle_class)
1 0.009 0.009 0.232 0.232 encoder.py:186(encode)
1 0.004 0.004 0.236 0.236 __init__.py:193(dumps)
1 0.001 0.001 0.094 0.094 application.py:659(__call__)
1 0.000 0.000 0.000 0.000 application.py:338(load)
13 0.000 0.000 0.000 0.000 utf_8.py:15(decode)
2 0.000 0.000 0.000 0.000 utils.py:521(re_subm)
1 0.000 0.000 0.859 0.859 application.py:269(wsgi)
1 0.000 0.000 0.000 0.000 httpserver.py:308(log)
1 0.000 0.000 0.000 0.000
basehttpserver.py:475(log_date_time_string)
5/1 0.000 0.000 0.858 0.858 application.py:233(process)
1 0.000 0.000 0.858 0.858 application.py:579(processor)
3 0.000 0.000 0.000 0.000 utils.py:536(group)
8 0.000 0.000 0.000 0.000 utils.py:356(safestr)
1 0.000 0.000 0.000 0.000 utils.py:1210(clear_all)
1 0.000 0.000 0.000 0.000 application.py:75(reload_mapping)
2 0.000 0.000 0.000 0.000 utils.py:487(__call__)
1 0.000 0.000 0.000 0.000 application.py:426(_match)
3/1 0.000 0.000 0.858 0.858 application.py:564(processor)
1 0.000 0.000 0.763 0.763 application.py:388(_delegate)
1 0.000 0.000 0.000 0.000 __init__.py:2035(start_response)
1 0.000 0.000 0.000 0.000 application.py:114(init_mapping)
13 0.000 0.000 0.000 0.000 utils.py:1223(__setitem__)
1 0.000 0.000 0.763 0.763 application.py:228(handle)
7 0.000 0.000 0.000 0.000 utils.py:545(take)
3 0.000 0.000 0.000 0.000 webapi.py:260(header)
1 0.000 0.000 0.000 0.000 application.py:97(_unload)
1 0.000 0.000 0.000 0.000 application.py:109(_cleanup)
4/1 0.000 0.000 0.858 0.858 application.py:237(<lambda>)
1 0.000 0.000 0.000 0.000 httpserver.py:301(xstart_response)
1 0.000 0.000 0.858 0.858
application.py:232(handle_with_processors)
2 0.000 0.000 0.000 0.000 utils.py:1234(clear)
1 0.000 0.000 0.000 0.000 os.py:446(get)
1 0.000 0.000 0.000 0.000 application.py:398(is_class)
1 0.000 0.000 0.000 0.000 utils.py:1246(iteritems)
Why is the run time of the query for the 500m tile in compariosn to
the 400m tile so high?
Are there possibilities to increase the runtime?
--
*Moritz Liebelt, B. Sc.*
Murgstraße 19
76437 Rastatt
E-Mail: mor...@go...
|
|
From: Amaury F. d'A. <ama...@gm...> - 2016-06-03 15:00:37
|
Hi,
There is probably an index on this column, and for some reason it is not
used when the data is unicode (= NVARCHAR on server-side).
My guess is that since the "=" comparison receives different types,
Oracle needs to do a conversion before it can compare the operands.
By default this conversion cannot be unicode->str (because this would lose
information),
so it has to be str->unicode for each value in the table.
In this case the index cannot be used...
My suggestion is force the unicode->str comparison somehow:
- either in Python, with .encode(), this will crash if the value has
non-ascii characters
- or in SQL, with F.LOGICAL_FILE_NAME = TO_CHAR(:logical_file_name), this
can return wrong results,
because the conversion can lose characters and collide with other existing
data.
2016-06-03 16:39 GMT+02:00 Valentin Kuznetsov <vk...@gm...>:
> Hi,
> I identified a problem with cx_Oracle driver and would like to understand
> how
> it can be resolved. Basically, I observed a huge latencies with query if
> I pass unicode string as binded parameter value. Below a stand-alone
> code which reproduce a problem with our Oracle.
>
> We run CERN Scientific Linux distribution (SLC 6.8), the code runs on
> x86_64 platform with gcc493 and cx_Oracle 5.2.1
>
> import cx_Oracle
> def test_cx_oracle(uname, pwd, host, sql, params):
> time0 = time.time()
> connection = cx_Oracle.connect(uname, pwd, host)
> cursor = connection.cursor()
> cursor.execute(sql, **params)
> lfn = params['logical_file_name']
> print("lfn type=%s, cursor execute %s sec" % (type(lfn),
> time.time()-time0))
> for row in cursor:
> print row
> cursor.close()
>
> def main():
> # read password from a file
> ppp = open('ppp').readline().replace('\n','').replace('oracle://', '')
> line = ppp.split('@')
> host = line[-1]
> uname, pwd = line[0].split(':')
> sql = 'SELECT F.LOGICAL_FILE_NAME FROM
> cms_dbs3_int_global_owner.FILES F WHERE F.IS_FILE_VALID <> -1 AND
> F.LOGICAL_FILE_NAME = :logical_file_name'
> lfn =
> '/store/generator/Summer11/ZZTo4e_7TeV_mll8_mZZ95-160-powheg15-pythia6/GEN/START311_V2-v2/00000/BA1D37D5-93EE-E211-ADCF-003048F174AC.root'
> params = {'logical_file_name': lfn}
> test_cx_oracle(uname, pwd, host, sql, params)
> params = {'logical_file_name': u'%s' % lfn}
> test_cx_oracle(uname, pwd, host, sql, params)
>
> if __name__ == '__main__':
> main()
>
> The output of this program is the following:
>
> lfn type=<type 'str'>, cursor execute 0.0578980445862 sec
>
> ('/store/generator/Summer11/ZZTo4e_7TeV_mll8_mZZ95-160-powheg15-pythia6/GEN/START311_V2-v2/00000/BA1D37D5-93EE-E211-ADCF-003048F174AC.root',)
>
> lfn type=<type 'unicode'>, cursor execute 144.633666992 sec
>
> ('/store/generator/Summer11/ZZTo4e_7TeV_mll8_mZZ95-160-powheg15-pythia6/GEN/START311_V2-v2/00000/BA1D37D5-93EE-E211-ADCF-003048F174AC.root',)
>
> As you can see using string parameter we quickly get results from Oracle
> in 0.06 sec, while using unicode we see huge latency 144sec.
>
> Any suggestions why unicode parameters such poorly behaves and where the
> time is spent on?
>
> Thanks,
> Valentin.
>
>
>
>
>
> ------------------------------------------------------------------------------
> What NetFlow Analyzer can do for you? Monitors network bandwidth and
> traffic
> patterns at an interface-level. Reveals which users, apps, and protocols
> are
> consuming the most bandwidth. Provides multi-vendor support for NetFlow,
> J-Flow, sFlow and other flows. Make informed decisions using capacity
> planning reports. https://ad.doubleclick.net/ddm/clk/305295220;132659582;e
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
--
Amaury Forgeot d'Arc
|
|
From: Anthony T. <ant...@gm...> - 2016-06-03 14:59:37
|
Hi Valentin,
I see this is Python 2.7. For that version, cx_Oracle assumes that unicode
data implies your desire to bind as NCHAR data. If the data in the database
is VARCHAR2, though, and indexed that way, the index will be bypassed and a
full table scan will take place instead. You can see what is going on here:
conn = cx_Oracle.Connection("user/pw@tns")
cursor = conn.cursor()
stringVar = cursor.var(str)
stringVar.setvalue(0, u"Testing")
print stringVar
This will show you the result <cx_Oracle.STRING with value 'Testing'>
ncharVar = cursor.var(unicode)
ncharVar.setvalue(0, u"Testing")
print ncharVar
This will show you the result <cx_Oracle.NCHAR with value u'Testing'>
Note the difference between the two. They behave differently when bound.
So if you want to pass unicode data in Python 2.7 you need to use
setinputsizes() to indicate that this is a STRING value and not an NCHAR
value.
Note that this changes for Python 3.x. Since all strings are unicode
strings, the default binding is STRING and you need to use setinputsizes()
to indicate your desire to use NCHAR instead. I decided it would be far too
disruptive to change the Python 2.7 behaviour to match the Python 3.x
behaviour -- but it was not an easy call to make!
Hope that explains things!
Anthony
On Fri, Jun 3, 2016 at 8:39 AM, Valentin Kuznetsov <vk...@gm...>
wrote:
> Hi,
> I identified a problem with cx_Oracle driver and would like to understand
> how
> it can be resolved. Basically, I observed a huge latencies with query if
> I pass unicode string as binded parameter value. Below a stand-alone
> code which reproduce a problem with our Oracle.
>
> We run CERN Scientific Linux distribution (SLC 6.8), the code runs on
> x86_64 platform with gcc493 and cx_Oracle 5.2.1
>
> import cx_Oracle
> def test_cx_oracle(uname, pwd, host, sql, params):
> time0 = time.time()
> connection = cx_Oracle.connect(uname, pwd, host)
> cursor = connection.cursor()
> cursor.execute(sql, **params)
> lfn = params['logical_file_name']
> print("lfn type=%s, cursor execute %s sec" % (type(lfn),
> time.time()-time0))
> for row in cursor:
> print row
> cursor.close()
>
> def main():
> # read password from a file
> ppp = open('ppp').readline().replace('\n','').replace('oracle://', '')
> line = ppp.split('@')
> host = line[-1]
> uname, pwd = line[0].split(':')
> sql = 'SELECT F.LOGICAL_FILE_NAME FROM
> cms_dbs3_int_global_owner.FILES F WHERE F.IS_FILE_VALID <> -1 AND
> F.LOGICAL_FILE_NAME = :logical_file_name'
> lfn =
> '/store/generator/Summer11/ZZTo4e_7TeV_mll8_mZZ95-160-powheg15-pythia6/GEN/START311_V2-v2/00000/BA1D37D5-93EE-E211-ADCF-003048F174AC.root'
> params = {'logical_file_name': lfn}
> test_cx_oracle(uname, pwd, host, sql, params)
> params = {'logical_file_name': u'%s' % lfn}
> test_cx_oracle(uname, pwd, host, sql, params)
>
> if __name__ == '__main__':
> main()
>
> The output of this program is the following:
>
> lfn type=<type 'str'>, cursor execute 0.0578980445862 sec
>
> ('/store/generator/Summer11/ZZTo4e_7TeV_mll8_mZZ95-160-powheg15-pythia6/GEN/START311_V2-v2/00000/BA1D37D5-93EE-E211-ADCF-003048F174AC.root',)
>
> lfn type=<type 'unicode'>, cursor execute 144.633666992 sec
>
> ('/store/generator/Summer11/ZZTo4e_7TeV_mll8_mZZ95-160-powheg15-pythia6/GEN/START311_V2-v2/00000/BA1D37D5-93EE-E211-ADCF-003048F174AC.root',)
>
> As you can see using string parameter we quickly get results from Oracle
> in 0.06 sec, while using unicode we see huge latency 144sec.
>
> Any suggestions why unicode parameters such poorly behaves and where the
> time is spent on?
>
> Thanks,
> Valentin.
>
>
>
>
>
> ------------------------------------------------------------------------------
> What NetFlow Analyzer can do for you? Monitors network bandwidth and
> traffic
> patterns at an interface-level. Reveals which users, apps, and protocols
> are
> consuming the most bandwidth. Provides multi-vendor support for NetFlow,
> J-Flow, sFlow and other flows. Make informed decisions using capacity
> planning reports. https://ad.doubleclick.net/ddm/clk/305295220;132659582;e
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
|
|
From: Valentin K. <vk...@gm...> - 2016-06-03 14:39:51
|
Hi,
I identified a problem with cx_Oracle driver and would like to understand how
it can be resolved. Basically, I observed a huge latencies with query if
I pass unicode string as binded parameter value. Below a stand-alone
code which reproduce a problem with our Oracle.
We run CERN Scientific Linux distribution (SLC 6.8), the code runs on
x86_64 platform with gcc493 and cx_Oracle 5.2.1
import cx_Oracle
def test_cx_oracle(uname, pwd, host, sql, params):
time0 = time.time()
connection = cx_Oracle.connect(uname, pwd, host)
cursor = connection.cursor()
cursor.execute(sql, **params)
lfn = params['logical_file_name']
print("lfn type=%s, cursor execute %s sec" % (type(lfn), time.time()-time0))
for row in cursor:
print row
cursor.close()
def main():
# read password from a file
ppp = open('ppp').readline().replace('\n','').replace('oracle://', '')
line = ppp.split('@')
host = line[-1]
uname, pwd = line[0].split(':')
sql = 'SELECT F.LOGICAL_FILE_NAME FROM cms_dbs3_int_global_owner.FILES F WHERE F.IS_FILE_VALID <> -1 AND F.LOGICAL_FILE_NAME = :logical_file_name'
lfn = '/store/generator/Summer11/ZZTo4e_7TeV_mll8_mZZ95-160-powheg15-pythia6/GEN/START311_V2-v2/00000/BA1D37D5-93EE-E211-ADCF-003048F174AC.root'
params = {'logical_file_name': lfn}
test_cx_oracle(uname, pwd, host, sql, params)
params = {'logical_file_name': u'%s' % lfn}
test_cx_oracle(uname, pwd, host, sql, params)
if __name__ == '__main__':
main()
The output of this program is the following:
lfn type=<type 'str'>, cursor execute 0.0578980445862 sec
('/store/generator/Summer11/ZZTo4e_7TeV_mll8_mZZ95-160-powheg15-pythia6/GEN/START311_V2-v2/00000/BA1D37D5-93EE-E211-ADCF-003048F174AC.root',)
lfn type=<type 'unicode'>, cursor execute 144.633666992 sec
('/store/generator/Summer11/ZZTo4e_7TeV_mll8_mZZ95-160-powheg15-pythia6/GEN/START311_V2-v2/00000/BA1D37D5-93EE-E211-ADCF-003048F174AC.root',)
As you can see using string parameter we quickly get results from Oracle
in 0.06 sec, while using unicode we see huge latency 144sec.
Any suggestions why unicode parameters such poorly behaves and where the time is spent on?
Thanks,
Valentin.
|
|
From: Jani T. <re...@gm...> - 2016-05-25 12:13:26
|
Hi, I've been trying to compile and build installation package from latest cx_Oracle dev version. For some reason I just get following error: (venv) C:\code\cx_oracle>python setup.py bdist_wininst running bdist_wininst running build running build_ext installing to build\bdist.win32\wininst running install_lib warning: install_lib: 'build\lib.win32-2.7' does not exist -- no Python modules to install When checking build directory it does have lib.win32-2.7-11g so why build_install step loses Oracle version? -- Jani Tiainen |
|
From: Marcio D. <mae...@gm...> - 2016-05-18 19:59:20
|
Hi Anthony, I tried to symlink but I found that libaoi.so are not there... in /app/.apt/usr/lib folder there is a symlink and that symlink leads to no file... Looks like libaio is installed in some folder that Heroku does not persist... Ty anyway... I will try to install libaio manually and then create the symlinks Ty Márcio On Wed, May 18, 2016 at 4:10 PM, Anthony Tuininga < ant...@gm...> wrote: > Hi, > > I see that the name of the library is libaio.so on the system but > cx_Oracle is looking for libaio.so.1. Are you able to create a symbolic > link? > > Beyond that, I'm not sure how much more I would be able to help! > > Anthony > > On Wed, May 18, 2016 at 12:45 PM, Marcio Duarte <mae...@gm...> > wrote: > >> Hello everyone, >> >> I'm having a problem running cx_Oracle in Heroku and I know this is not a >> cx_Oracle problem, but since this is the first time I'm seeing this >> problem, I'm trying to get all the help that I can. >> >> I could found nothing in the Web and I'm stuck in this for almost two >> week. I asked a question in stackoverflow with more details about my >> problem: >> >> http://stackoverflow.com/questions/37283524/python-cx-oracle-in-heroku >> >> I already open a ticket at Heroku support but I got no answer yet. >> >> Did you have seeing anything like this before? There's a way that I can >> run cx_Oracle without libaio dependency? >> >> Ty All! >> >> Márcio >> >> >> ------------------------------------------------------------------------------ >> Mobile security can be enabling, not merely restricting. Employees who >> bring their own devices (BYOD) to work are irked by the imposition of MDM >> restrictions. Mobile Device Manager Plus allows you to control only the >> apps on BYO-devices by containerizing them, leaving personal data >> untouched! >> https://ad.doubleclick.net/ddm/clk/304595813;131938128;j >> _______________________________________________ >> cx-oracle-users mailing list >> cx-...@li... >> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users >> >> > > > ------------------------------------------------------------------------------ > Mobile security can be enabling, not merely restricting. Employees who > bring their own devices (BYOD) to work are irked by the imposition of MDM > restrictions. Mobile Device Manager Plus allows you to control only the > apps on BYO-devices by containerizing them, leaving personal data > untouched! > https://ad.doubleclick.net/ddm/clk/304595813;131938128;j > _______________________________________________ > cx-oracle-users mailing list > cx-...@li... > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users > > |
|
From: Anthony T. <ant...@gm...> - 2016-05-18 19:10:20
|
Hi, I see that the name of the library is libaio.so on the system but cx_Oracle is looking for libaio.so.1. Are you able to create a symbolic link? Beyond that, I'm not sure how much more I would be able to help! Anthony On Wed, May 18, 2016 at 12:45 PM, Marcio Duarte <mae...@gm...> wrote: > Hello everyone, > > I'm having a problem running cx_Oracle in Heroku and I know this is not a > cx_Oracle problem, but since this is the first time I'm seeing this > problem, I'm trying to get all the help that I can. > > I could found nothing in the Web and I'm stuck in this for almost two > week. I asked a question in stackoverflow with more details about my > problem: > > http://stackoverflow.com/questions/37283524/python-cx-oracle-in-heroku > > I already open a ticket at Heroku support but I got no answer yet. > > Did you have seeing anything like this before? There's a way that I can > run cx_Oracle without libaio dependency? > > Ty All! > > Márcio > > > ------------------------------------------------------------------------------ > Mobile security can be enabling, not merely restricting. Employees who > bring their own devices (BYOD) to work are irked by the imposition of MDM > restrictions. Mobile Device Manager Plus allows you to control only the > apps on BYO-devices by containerizing them, leaving personal data > untouched! > https://ad.doubleclick.net/ddm/clk/304595813;131938128;j > _______________________________________________ > cx-oracle-users mailing list > cx-...@li... > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users > > |
|
From: Marcio D. <mae...@gm...> - 2016-05-18 18:45:09
|
Hello everyone, I'm having a problem running cx_Oracle in Heroku and I know this is not a cx_Oracle problem, but since this is the first time I'm seeing this problem, I'm trying to get all the help that I can. I could found nothing in the Web and I'm stuck in this for almost two week. I asked a question in stackoverflow with more details about my problem: http://stackoverflow.com/questions/37283524/python-cx-oracle-in-heroku I already open a ticket at Heroku support but I got no answer yet. Did you have seeing anything like this before? There's a way that I can run cx_Oracle without libaio dependency? Ty All! Márcio |
|
From: Jani T. <re...@gm...> - 2016-05-13 05:04:54
|
Hi,
I was trying out bleeding edge version of cx_Oracle and tried to create
SDO_GEOMETRY objects.
For some reason in my environments I had to always prefix SDO_GEOMETRY
with MDSYS schema name. Is there reason that plain SDO_GEOMETRY doesn't
work?
Traceback I get:
(django-tests)jtiai@jns42-l:~/code/django-tests/cx_oracle/samples$
python InsertGeometry.py
Traceback (most recent call last):
File "InsertGeometry.py", line 13, in <module>
typeObj = connection.gettype("SDO_GEOMETRY")
cx_Oracle.DatabaseError: ORA-04043: object SDO_GEOMETRY does not exist
--
Jani Tiainen
|
|
From: Doug H. <djn...@gm...> - 2016-04-29 18:17:32
|
Cast :arg0 to a numeric type.
... select cast(:argo) as number ...
On Apr 18, 2016 1:50 AM, "Mikhail Nacharov" <mn...@ya...> wrote:
Hello everyone!
Does anybody know why this query fails with ORA-01790?
```
>>> import cx_Oracle
>>> con = cx_Oracle.connect('django/django@127.0.0.1/orcl')
>>> cur = con.cursor()
>>> cur.execute('SELECT 1 FROM dual UNION ALL SELECT :arg0 FROM dual',
arg0=None)
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
cx_Oracle.DatabaseError: ORA-01790: expression must have same datatype
as corresponding expression
```
It seems to me that bind variables passes as empty string '' and not as
NULL value.
Moreover, when I execute this query in oracle sqldeveloper I had the
same exception. Is it some oracle "feature"?
P.S.: The purpose of this question is the bug in django:
https://code.djangoproject.com/ticket/22669
--
Mikhail <mn...@ya...>
------------------------------------------------------------------------------
Find and fix application performance issues faster with Applications Manager
Applications Manager provides deep performance insights into multiple tiers
of
your business applications. It resolves application problems quickly and
reduces your MTTR. Get your free trial!
https://ad.doubleclick.net/ddm/clk/302982198;130105516;z
_______________________________________________
cx-oracle-users mailing list
cx-...@li...
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
|
|
From: Anthony T. <ant...@gm...> - 2016-04-26 15:53:55
|
Thanks for these suggestions. It will take some time to consider which
approach to take. I'll reply back again when I have a positive decision
made.
One concern that eliminates a few of these possibilities is portability.
cx_Oracle runs on more than just Windows so Windows-specific code/concepts
can't be used. Creating differently named modules (for client version) or
creating a wrapper module that imports the right sub-module at run-time are
the two top choices I am considering right now. If anyone else has any
thoughts on which approach would be best to take, I'd love to hear from you.
Anthony
On Thu, Apr 21, 2016 at 7:24 PM, Kubo Takehiro <ku...@ji...> wrote:
> Hi,
>
> I know a little about python and nothing about wheels.
> However I have three ideas.
>
> 1. Distribute cx_Oracle compiled for Oracle 10g.
>
> Oracle client library ABI keeps backward compatibility on Windows.
> When a binary file is compiled for Oracle 10g, it works with Oracle
> 10g, 11g and 12c.
> However it cannot use features depends on Oracle 11g and 12c clients.
>
> 2. Check Oracle client version at runtime.
>
> This is ideal for pre-compiled packages but not practical because this
> needs
> too many changes in cx_Oracle.
>
> Add the following code to Module_Initialize() in cx_Oracle.c
>
> HMODULE hMod = GetModuleHandle("OCI.DLL");
> void (*func)(sword*, sword*, sword*, sword*, sword*) = (void
> (*)(sword*, sword*, sword*, sword*, sword*))GetProcAddress(hMod,
> "OCIClientVersion");
> if (func == NULL) {
> oracle_version_hex = ORACLE_VERSION(10, 1)
> } else {
> sword major, minor, update, patch, port;
> (*func)(&major, &minor, &update, &patch, &port);
> oracle_version_hex = ORACLE_VERSION(major, minor);
> }
>
> and rewrite code such as
> #if ORACLE_VERSION_HEX >= ORACLE_VERSION(11,1)
> ...
> #endif
> to
> if (oracle_client_hex >= ORACLE_VERSION(11,1)) {
> ...
> }
>
> Use function pointers retrieved by GetProcAddress() for functions
> added in Oracle 11g and 12c,
>
> 3. Add a bootstrap module if wheels can include more than one module.
>
> Compile cx_Oracle for Oracle 10.1, 10.2, 11.1, 11.2 and 12.1 and rename
> them
> to cx_Oracle.10.1.dll, cx_Oracle.10.2.dll, cx_Oracle.11.1.dll,
> cx_Oracle.11.2.dll and cx_Oracle.12.1.dll respectively.
>
> Compile the following code as cx_Oracle.dll.
>
> // This is incomplete code. no error checks.
>
> typedef int sword;
>
> static void *get_init_func(const char *init_func_name)
> {
> HMODULE hMod;
> void (*func)(sword*, sword*, sword*, sword*, sword*);
> char dll_name[30];
> char dll_full_path[MAX_PATH];
>
> // Get the Oracle client version and fill dll_name.
> hMod = LoadLibrary("OCI.DLL");
> func = (void (*)(sword*, sword*, sword*, sword*,
> sword*))GetProcAddress(hMod, "OCIClientVersion");
> if (func == NULL) {
> strcpy(dll_name, "cx_Oracle.10.1.dll");
> } else {
> sword major, minor, update, patch, port;
> (*func)(&major, &minor, &update, &patch, &port);
> sprintf(dll_name, "cx_Oracle.%d.%d.dll", major, minor);
> }
> FreeLibrary(hMod);
>
> // get the current module handle.
> GetModuleHandleEx(GET_MODULE_HANDLE_EX_FLAG_FROM_ADDRESS,
> &get_init_func, &hMod);
> ... fill dll_full_path with the directory name containing the
> current module by using GetModuleFileName(hMod, ...).
> ... append dll_name to dll_full_path.
>
> // Load the real cx_Oracle module and get the module initializer
> function.
> hMod = LoadLibrary(dll_full_path);
> return GetProcAddress(hMod, init_func_name);
> }
>
> #if PY_MAJOR_VERSION >= 3
> PyMODINIT_FUNC PyInit_cx_Oracle(void)
> {
> PyObject *(*init)(void) =
> (PyObject*(*)(void))get_init_func("PyInit_cx_Oracle");
> return (*init)();
> }
> #else
> void initcx_Oracle(void)
> {
> void (*init)(void) = (void(*)(void))get_init_func("initcx_Oracle");
> (*init)();
> }
> #endif
>
>
> Put cx_Oracle.dll, cx_Oracle.10.1.dll, cx_Oracle.10.2.dll,
> cx_Oracle.11.1.dll, cx_Oracle.11.2.dll
> and cx_Oracle.12.1.dll in a directory.
>
>
> ------------------------------------------------------------------------------
> Find and fix application performance issues faster with Applications
> Manager
> Applications Manager provides deep performance insights into multiple
> tiers of
> your business applications. It resolves application problems quickly and
> reduces your MTTR. Get your free trial!
> https://ad.doubleclick.net/ddm/clk/302982198;130105516;z
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
|
31 messages has been excluded from this view by a project administrator.
