Re: [cx-oracle-users] Differences in runtime
Brought to you by:
atuining
|
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...://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...
|