Thread: [cx-oracle-users] Differences in runtime
Brought to you by:
atuining
Menu
▾
▴
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: 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 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: 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-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: 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-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... > |
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X