Re: Differences in runtime
Moritz Liebelt <moritz.liebelt-gM/[email protected]> Tue, 2 Aug 2016 14:14:14 +0200
| Newsgroups | gmane.comp.python.db.cx-oracle |
|---|---|
| Message-ID | <CAGsDTb0kMhDPU6-4yn5Zu_sRaXCPfcUPYTe-0eqbHbsPF71Jew@mail.gmail.com> |
sadly that doesn´t change anything. For both options the runtime is the same... 2016-08-02 12:02 GMT+02:00 Jani Tiainen <[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") > 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: moritz.liebelt-gM/[email protected] > > > ------------------------------------------------------------------------------ > > > > _______________________________________________ > cx-oracle-users mailing listcx-oracle-users-5NWGOfrQmneRv+LV9MX5ugtCRVl27V+i0wdF1cv0I5s@public.gmane.org://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 > > -- *Moritz Liebelt, B. Sc.* Murgstraße 19 76437 Rastatt E-Mail: 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