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