Below, you can see that establishing the database connection itself to
oracle is taking 1.8 seconds (connect() on the SQLA side). And a huge
2.9 seconds to fetch just 25 rows from your Oracle database
(_fetchone_impl on the SQLA side, which calls cursor.fetchone(), a
cx_oracle native function, directly). The total time spent waiting
for results from cx_oracle, for a total of four statement executions
(do_execute()), is .662 seconds.
So that is a total of 5.3 seconds spent entirely on the cx_oracle side
of the equation, which is the time it's taking your oracle database to
connect, execute four select statements, and fetch a total of 25
rows. 0.2 seconds remain which is the time actually spent within
SQLA's reflection implementation.
It seems you have an extremely slow network connection or an
overloaded Oracle database.
On Aug 28, 2008, at 10:26 AM, vkuznet wrote:
>
> Hi,
> so I modified a code to use hotshot module, basically I added
>
> profiler = hotshot.Profile("profile.dat")
> profiler.run("loadTables(tableNames)")
> profiler.close()
>
> to my code instead of just calling loadTables function. Results are
> below. I played with code a little bit and it seems to me that most of
> the time spent in database/oracle.py, in reflecttable method of
> OracleDialect class.
>
> Anyway, let me know if more profile is necessary.
> Thank you,
> Valentin.
>
>
> create engine 0.11220407486
> engine.connect 1.89971590042
> SELECT table_name FROM all_tables WHERE owner='ZZZ'
> get tables 0.155882120132
> Loading 'triggerpathdescription' table
> 5.56913805008
> Load tables manually ['triggerpathdescription']
> load table 5.57045388222
> 1790 function calls (1717 primitive calls) in 5.570 CPU
> seconds
>
> Ordered by: internal time, call count
>
> ncalls tottime percall cumtime percall
> filename:lineno(function)
> 25 2.933 0.117 2.933 0.117 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/base.py:1607(_fetchone_impl)
> 1 1.809 1.809 1.809 1.809 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/strategies.py:76(connect)
> 4 0.662 0.166 0.662 0.166 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/default.py:128(do_execute)
> 1 0.146 0.146 0.147 0.147 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/pool.py:276(_finalize_fairy)
> 2/1 0.002 0.001 3.611 3.611 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/databases/oracle.py:439(reflecttable)
> 147/111 0.001 0.000 0.002 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/base.py:1590(_get_col)
> 14 0.001 0.000 0.002 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/schema.py:406(__init__)
> 35 0.001 0.000 0.001 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/sql/compiler.py:1001(_requires_quotes)
> 147 0.001 0.000 0.001 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/util.py:121(__getitem__)
> 38 0.001 0.000 0.002 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/databases/oracle.py:366(_normalize_name)
> 4 0.001 0.000 0.001 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/base.py:1423(_init_metadata)
> 25 0.001 0.000 2.934 0.117 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/base.py:1641(fetchone)
> 14 0.001 0.000 0.002 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/schema.py:583(_set_parent)
> 90 0.001 0.000 0.001 0.000 /opt/local/Library/
> Frameworks/Python.framework/Versions/2.4/lib/python2.4/encodings/
> utf_8.py:15(decode)
> 41 0.001 0.000 0.001 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/pool.py:442(__getattr__)
> 16 0.000 0.000 0.001 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/sql/expression.py:2651(__init__)
> 111 0.000 0.000 0.003 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/base.py:1338(__getitem__)
> 10/1 0.000 0.000 0.001 0.001 /opt/local/Library/
> Frameworks/Python.framework/Versions/2.4/lib/python2.4/sre_parse.py:
> 374(_parse)
> 11/1 0.000 0.000 0.000 0.000 /opt/local/Library/
> Frameworks/Python.framework/Versions/2.4/lib/python2.4/sre_compile.py:
> 27(_compile)
> 4 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/pool.py:429(close)
> 21 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/base.py:1299(__init__)
> 11/1 0.000 0.000 0.000 0.000 /opt/local/Library/
> Frameworks/Python.framework/Versions/2.4/lib/python2.4/sre_parse.py:
> 140(getwidth)
> 4 0.000 0.000 0.002 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/databases/oracle.py:216(get_result_proxy)
> 72 0.000 0.000 0.000 0.000 /opt/local/Library/
> Frameworks/Python.framework/Versions/2.4/lib/python2.4/sre_parse.py:
> 182(__next)
> 20 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/util.py:472(__setitem__)
> 1 0.000 0.000 5.570 5.570 test.py:14(loadTables)
> 2/1 0.000 0.000 5.567 5.567 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/base.py:1267(reflecttable)
> 2/1 0.000 0.000 5.569 5.569 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/schema.py:124(__init__)
> 20 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/util.py:568(__setitem__)
> 34 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/sql/expression.py:1574(__contains__)
> 4 0.000 0.000 0.001 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/default.py:136(__init__)
> 5 0.000 0.000 0.002 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/schema.py:260(__extra_kwargs)
> 4 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/default.py:217(proc)
> 4 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/base.py:1502(close)
> 4 0.000 0.000 0.665 0.166 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/base.py:851(_execute_text)
> 14 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/sql/expression.py:1579(contains_column)
> 27 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/base.py:1473(lookup_key)
> 14 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/sql/expression.py:1517(replace)
> 5/1 0.000 0.000 5.569 5.569 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/schema.py:90(__call__)
> 4 0.000 0.000 0.001 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/schema.py:1118(append_element)
> 20 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/util.py:615(__init__)
> 16 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/types.py:319(to_instance)
> 34 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/util.py:496(__contains__)
> 6 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/sql/expression.py:1547(__setitem__)
> 4 0.000 0.000 0.001 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/base.py:
> 946(__create_execution_context)
> 60 0.000 0.000 0.000 0.000 /opt/local/Library/
> Frameworks/Python.framework/Versions/2.4/lib/python2.4/sre_parse.py:
> 201(get)
> 2 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/schema.py:1139(__init__)
> 14 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/schema.py:994(__contains__)
> 4 0.000 0.000 0.001 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/base.py:1393(__init__)
> 4 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/schema.py:1063(__init__)
> 2/1 0.000 0.000 0.001 0.001 /opt/local/Library/
> Frameworks/Python.framework/Versions/2.4/lib/python2.4/sre_parse.py:
> 301(_parse_sub)
> 12 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/util.py:621(add)
> 4 0.000 0.000 0.001 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/schema.py:1111(_set_parent)
> 2 0.000 0.000 0.000 0.000 /opt/local/Library/
> Frameworks/Python.framework/Versions/2.4/lib/python2.4/threading.py:
> 111(release)
> 4 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/base.py:1469(_create_key_cache)
> 14 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/util.py:499(get)
> 5 0.000 0.000 0.002 0.000 /opt/local/Library/
> Frameworks/Python.framework/Versions/2.4/lib/python2.4/sre.py:
> 213(_compile)
> 4 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/schema.py:820(_set_parent)
> 8 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/util.py:460(__init__)
> 59 0.000 0.000 0.000 0.000 /opt/local/Library/
> Frameworks/Python.framework/Versions/2.4/lib/python2.4/sre_parse.py:
> 138(append)
> 4 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/default.py:264(returns_rows_text)
> 4 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/schema.py:1123(_append_fk)
> 14 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/util.py:466(__iter__)
> 4 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/pool.py:351(cursor)
> 68 0.000 0.000 0.000 0.000 /opt/local/Library/
> Frameworks/Python.framework/Versions/2.4/lib/python2.4/sre_parse.py:
> 130(__getitem__)
> 3 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/schema.py:232(_init_existing)
> 4 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/databases/oracle.py:374(_denormalize_name)
> 6 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/schema.py:974(__init__)
> 14 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/util.py:553(itervalues)
> 16 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/sql/expression.py:1726(attr)
> 4 0.000 0.000 0.662 0.166 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/base.py:953(_cursor_execute)
> 8 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/sql/expression.py:1510(__init__)
> 4 0.000 0.000 0.665 0.166 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/base.py:839(execute)
> 5 0.000 0.000 0.002 0.000 /opt/local/Library/
> Frameworks/Python.framework/Versions/2.4/lib/python2.4/sre.py:
> 126(match)
> 1 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/pool.py:167(return_conn)
> 14 0.000 0.000 0.002 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/schema.py:303(append_column)
> 4 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/base.py:798(_autocommit)
> 4 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/base.py:858(__distill_params)
> 4 0.000 0.000 0.001 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/databases/oracle.py:
> 355(create_execution_context)
> 4 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/threadlocal.py:105(in_transaction)
> 19 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/schema.py:46(_init_items)
> 14 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/util.py:550(values)
> 10 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/util.py:475(__getitem__)
> 4 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/default.py:204(__encode_param_keys)
> 2 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/sql/expression.py:2736(__init__)
> 26 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/types.py:159(dialect_impl)
> 6 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/sql/expression.py:1538(add)
> 2 0.000 0.000 0.000 0.000 /opt/local/Library/
> Frameworks/Python.framework/Versions/2.4/lib/python2.4/threading.py:
> 93(acquire)
> 16 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/schema.py:290(primary_key)
> 1 0.000 0.000 1.810 1.810 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/pool.py:156(connect)
> 1 0.000 0.000 1.810 1.810 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/threadlocal.py:16(get_connection)
> 1 0.000 0.000 1.810 1.810 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/pool.py:578(do_get)
> 4 0.000 0.000 0.663 0.166 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/base.py:912(__execute_raw)
> 8 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/util.py:517(__init__)
> 4 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/default.py:274(create_cursor)
> 4 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/schema.py:681(__init__)
> 15 0.000 0.000 0.000 0.000 /opt/local/Library/
> Frameworks/Python.framework/Versions/2.4/lib/python2.4/sre_parse.py:
> 195(match)
> 7 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/schema.py:81(_get_table_key)
> 1 0.000 0.000 0.002 0.002 /opt/local/Library/
> Frameworks/Python.framework/Versions/2.4/lib/python2.4/sre_compile.py:
> 490(compile)
> 1 0.000 0.000 1.810 1.810 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/pool.py:302(__init__)
> 2 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/schema.py:284(_set_primary_key)
> 4 0.000 0.000 0.002 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/default.py:283(result)
> 4 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/types.py:373(__init__)
> 4 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/default.py:270(should_autocommit_text)
> 1 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/queue.py:81(put)
> 5 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/schema.py:272(__post_init)
> 1 0.000 0.000 0.147 0.147 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/threadlocal.py:114(close)
> 1 0.000 0.000 1.809 1.809 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/pool.py:261(__connect)
> 11 0.000 0.000 0.000 0.000 /opt/local/Library/
> Frameworks/Python.framework/Versions/2.4/lib/python2.4/sre_parse.py:
> 90(__init__)
> 1 0.000 0.000 0.000 0.000 /opt/local/Library/
> Frameworks/Python.framework/Versions/2.4/lib/python2.4/sre_compile.py:
> 356(_compile_info)
> 1 0.000 0.000 1.810 1.810 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/pool.py:172(get)
> 1 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/queue.py:124(get)
> 1 0.000 0.000 0.000 0.000 /opt/local/Library/
> Frameworks/Python.framework/Versions/2.4/lib/python2.4/threading.py:
> 236(notify)
> 1 0.000 0.000 0.147 0.147 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/base.py:816(close)
> 1 0.000 0.000 0.001 0.001 /opt/local/Library/
> Frameworks/Python.framework/Versions/2.4/lib/python2.4/sre_parse.py:
> 658(parse)
> 4 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/threadlocal.py:89(session)
> 4 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/pool.py:422(__init__)
> 1 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/threadlocal.py:171(session)
> 26 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/types.py:180(result_processor)
> 2 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/schema.py:1175(add)
> 2 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/sql/expression.py:1614(__init__)
> 1 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/threadlocal.py:84(__init__)
> 1 0.000 0.000 1.809 1.809 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/pool.py:199(__init__)
> 4 0.000 0.000 0.001 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/schema.py:308(append_constraint)
> 4 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/base.py:573(connection)
> 5 0.000 0.000 0.000 0.000 /opt/local/Library/
> Frameworks/Python.framework/Versions/2.4/lib/python2.4/threading.py:
> 672(currentThread)
> 4 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/util.py:119(__init__)
> 4 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/base.py:588(should_close_with_result)
> 1 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/base.py:528(__init__)
> 1 0.000 0.000 0.000 0.000 /opt/local/Library/
> Frameworks/Python.framework/Versions/2.4/lib/python2.4/weakref.py:
> 191(__init__)
> 2 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/schema.py:344(_set_parent)
> 2 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/util.py:463(__len__)
> 2 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/util.py:132(__init__)
> 1 0.000 0.000 0.001 0.001 /opt/local/Library/
> Frameworks/Python.framework/Versions/2.4/lib/python2.4/sre_compile.py:
> 475(_code)
> 1 0.000 0.000 0.000 0.000 /opt/local/Library/
> Frameworks/Python.framework/Versions/2.4/lib/python2.4/weakref.py:
> 77(__setitem__)
> 1 0.000 0.000 1.810 1.810 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/threadlocal.py:179(contextual_connect)
> 4 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/threadlocal.py:36(in_transaction)
> 1 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/pool.py:565(do_return_conn)
> 1 0.000 0.000 0.000 0.000 /opt/local/Library/
> Frameworks/Python.framework/Versions/2.4/lib/python2.4/weakref.py:
> 60(__contains__)
> 1 0.000 0.000 0.147 0.147 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/pool.py:416(_close)
> 1 0.000 0.000 5.570 5.570 <string>:1(?)
> 1 0.000 0.000 0.000 0.000 /opt/local/Library/
> Frameworks/Python.framework/Versions/2.4/lib/python2.4/weakref.py:
> 186(__new__)
> 1 0.000 0.000 0.147 0.147 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/pool.py:411(close)
> 5 0.000 0.000 0.000 0.000 /opt/local/Library/
> Frameworks/Python.framework/Versions/2.4/lib/python2.4/threading.py:
> 44(_note)
> 1 0.000 0.000 1.809 1.809 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/pool.py:137(create_connection)
> 10 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/types.py:65(__init__)
> 4 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/base.py:555(dialect)
> 1 0.000 0.000 1.952 1.952 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/base.py:993(reflecttable)
> 1 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/queue.py:185(_full)
> 2 0.000 0.000 0.000 0.000 /opt/local/Library/
> Frameworks/Python.framework/Versions/2.4/lib/python2.4/sre_compile.py:
> 469(isstring)
> 1 0.000 0.000 0.000 0.000 /opt/local/Library/
> Frameworks/Python.framework/Versions/2.4/lib/python2.4/sre_parse.py:
> 178(__init__)
> 1 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/schema.py:1939(_bind_or_error)
> 1 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/pool.py:362(checkout)
> 1 0.000 0.000 0.000 0.000 /opt/local/Library/
> Frameworks/Python.framework/Versions/2.4/lib/python2.4/UserDict.py:
> 19(__delitem__)
> 3 0.000 0.000 0.000 0.000 /opt/local/Library/
> Frameworks/Python.framework/Versions/2.4/lib/python2.4/sre_parse.py:
> 126(__len__)
> 1 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/pool.py:230(get_connection)
> 1 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/base.py:1531(__iter__)
> 1 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/queue.py:189(_put)
> 2 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/schema.py:279(_export_columns)
> 1 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/threadlocal.py:12(__init__)
> 1 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/engine/threadlocal.py:31(_conn_closed)
> 1 0.000 0.000 0.000 0.000 /opt/local/Library/
> Frameworks/Python.framework/Versions/2.4/lib/python2.4/threading.py:
> 143(_is_owned)
> 1 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/schema.py:1418(bind)
> 1 0.000 0.000 0.000 0.000 /opt/local/Library/
> Frameworks/Python.framework/Versions/2.4/lib/python2.4/sre_parse.py:
> 67(__init__)
> 1 0.000 0.000 0.000 0.000 /opt/local/lib/python2.4/
> site-packages/sqlalchemy/queue.py:181(_empty)
> 1 0.000 0.000 0.000 0.000 /opt/local/Library/
> Frameworks/Python.framework/Versions/2.4/lib/python2.4/weakref.py:
> 53(__getitem__)
> 0 0.000 0.000 profile:0(profiler)
>
>
>
> On Aug 27, 5:16 pm, Michael Bayer <[EMAIL PROTECTED]> wrote:
>> I have never observed 5 seconds to reflect a single table even with
>> Oracle which tends to be the slowest for schema-level operations.
>> The numbers you posted are too coarse grained to indicate where the
>> time is being spent, and if it were within SA reflection code then
>> these kinds of speed issues would be apparent across all dialects.
>> Can you post some "profile" results for your tests ?
>>
>> On Aug 27, 2008, at 3:57 PM, vkuznet wrote:
>>
>>
>>
>>> Hi,
>>> I've been trying to profile why loading a single table from remote
>>> ORACLE DB takes more then 5 seconds.
>>> Here is some numbers:
>>
>>> create engine 0.0740728378296
>>> engine.connect 2.05604815483
>>> SELECT table_name FROM all_tables WHERE owner='ZZZ'
>>> get tables 0.18466091156
>>> Loading 'triggerpathdescription' table
>>> 5.85890698433
>>> Load tables manually ['triggerpathdescription']
>>> load table 5.85950708389
>>
>>> It looks like SQLAlchemy takes 25 fetchone calls in order to
>>> autoload
>>> table, but I tweak a code for ORACLE engine to use fetchmany and
>>> just
>>> slighly reduced the total time. To me it's WAY to long and as far
>>> as I
>>> can tell it internals of building foreign-key relationships. So I
>>> wonder if there is a way to speed up such auto load.
>>
>>> I'm attaching a simple test code below.
>>> Thanks,
>>> Valentin.
>>
>>> #!/usr/bin/env python
>>
>>> import sqlalchemy,types,sys,os,time
>>
>>> def loadTables(tableNames):
>>> for tName in tableNames:
>>> t = tName[0].lower().split(".")[-1]
>>> print "Loading '%s' table"%t
>>> t1=time.time()
>>
>>> tables
>>> [t
>>> ]=
>>> sqlalchemy
>>> .Table
>>> (t
>>> ,dbMeta
>>> ,autoload=True,schema=schema,oracle_renyms=True,useexisting=True)
>>> print time.time()-t1
>>> break
>>
>>> print "Load tables manually",tables.keys()
>>
>>> schema = 'ZZZ'
>>> t1 = time.time()
>>> engine = sqlalchemy.create_engine('oracle://
>>> XXX:[EMAIL PROTECTED]',strategy='threadlocal',threaded=True)
>>> print "create engine",time.time()-t1
>>> t1 = time.time()
>>
>>> con = engine.connect()
>>
>>> print "engine.connect",time.time()-t1
>>> t1 = time.time()
>>> tables={}
>>> dbMeta = sqlalchemy.MetaData()
>>> dbMeta.bind = engine
>>
>>> idx = 0
>>> query="SELECT table_name FROM all_tables WHERE owner='%s'"%schema
>>> print query
>>> tableNames=con.execute(query)
>>> print "get tables",time.time()-t1
>>> t1 = time.time()
>>
>>> loadTables(tableNames)
>>
>>> print "load table",time.time()-t1
>>> t1 = time.time()
> >
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
