Re: Differences in runtime
Jani Tiainen <[email protected]> Tue, 2 Aug 2016 13:02:04 +0300
| Newsgroups | gmane.comp.python.db.cx-oracle |
|---|---|
| Message-ID | <[email protected]> |
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-tJOn8oSH3ga/[email protected]:1521/sdetest
> <http://limo1013-tJOn8oSH3ga/[email protected]: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: moritz.liebelt-gM/[email protected]
> <mailto:moritz.liebelt-gM/[email protected]>
>
>
> ------------------------------------------------------------------------------
>
>
> _______________________________________________
> cx-oracle-users mailing list
> cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
------------------------------------------------------------------------------
_______________________________________________
cx-oracle-users mailing list
cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users