Re: Differences in runtime
Moritz Liebelt <moritz.liebelt-gM/[email protected]> Wed, 3 Aug 2016 11:47:13 +0200
| Newsgroups | gmane.comp.python.db.cx-oracle |
|---|---|
| Message-ID | <CAGsDTb2=MVpk7Om5JCvxmkRemkrHe9kBEPiCO7ZJP+qJNejcQg@mail.gmail.com> |
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 <[email protected]>: > 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 < > moritz.liebelt-gM/[email protected]> 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/[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 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 > > -- *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