Re: [cx-oracle-users] Differences in runtime
Brought to you by:
atuining
Menu
▾
▴
Re: [cx-oracle-users] Differences in runtime
|
From: Anthony T. <ant...@gm...> - 2016-08-02 22:54:27
|
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
>
>
|
