Differences in runtime
Moritz Liebelt <moritz.liebelt-gM/[email protected]> Tue, 2 Aug 2016 10:20:31 +0200
| Newsgroups | gmane.comp.python.db.cx-oracle |
|---|---|
| Message-ID | <CAGsDTb1SEcSmCv3yDTOJenM7f3QOPasHzfBWNcvN56bVs1J_vw@mail.gmail.com> |
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 list
cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users