Hi,
Pasted below is a profile of the earlier code posted. I did update it with your
new definition of ARRAY Michael, but that only shaved off 18 seconds (down to
468s total) when run without the profiler.
The large number of __new__ calls roughly tracks with the number of "numeric"
values coming back from the database - 1000 rows x 4 columns x 4634 per array,
or 18,536,000 new objects. It looks like Conor's suggestion that the bottleneck
is the str-to-Decimal conversion is correct. (This would explain why our
initial imports were taking an extremely long time as well...).
I intend to use lazy loading on these columns, but before that I'm certainly
open to any suggestions to cut the time down significantly! We'll also look
into psycopg2's Decimal class.
If the type in the database is numeric[], how can I retrieve it as a string?
Thanks again for the help!
Cheers,
Demitri
----
Mon Jul 19 20:29:16 2010 prof_stats
194856576 function calls (194856471 primitive calls) in 538.608 CPU
seconds
Ordered by: cumulative time
List reduced from 460 to 20 due to restriction <20>
ncalls tottime percall cumtime percall filename:lineno(function)
1 1.968 1.968 538.608 538.608 <string>:1(<module>)
1 0.000 0.000 536.430 536.430 ./examples/db_test.py:56(main)
1 0.036 0.036 536.414 536.414
/Library/Python/2.6/site-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/orm/query.py:1447(all)
1001 0.001 0.000 530.894 0.530
/Library/Python/2.6/site-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/orm/query.py:1619(instances)
1 0.000 0.000 529.213 529.213
/Library/Python/2.6/site-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/engine/base.py:2379(fetchall)
1 0.000 0.000 529.211 529.211
/Library/Python/2.6/site-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/engine/base.py:2360(_fetchall_impl)
1 31.366 31.366 529.211 529.211 {method 'fetchall' of
'psycopg2._psycopg.cursor' objects}
18538000 124.784 0.000 497.845 0.000
/System/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/decimal.py:511(__new__)
18551033 290.353 0.000 290.353 0.000 {built-in method __new__ of type
object at 0xf6360}
18538021 45.568 0.000 45.568 0.000 {built-in method match}
74152000 17.219 0.000 17.219 0.000 {built-in method group}
18538630 9.908 0.000 9.908 0.000 {isinstance}
18538000 6.356 0.000 6.356 0.000 {method 'lstrip' of 'str' objects}
1 0.000 0.000 5.484 5.484
/Library/Python/2.6/site-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/orm/query.py:1560(__iter__)
1 0.000 0.000 5.484 5.484
/Library/Python/2.6/site-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/orm/query.py:1567(_execute_and_instances)
1 0.000 0.000 5.484 5.484
/Library/Python/2.6/site-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/orm/session.py:703(execute)
1 0.000 0.000 5.483 5.483
/Library/Python/2.6/site-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/engine/base.py:1148(execute)
1 0.000 0.000 5.483 5.483
/Library/Python/2.6/site-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/engine/base.py:1212(_execute_clauseelement)
1 0.000 0.000 5.482 5.482
/Library/Python/2.6/site-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/engine/base.py:1255(__execute_context)
1 0.000 0.000 5.482 5.482
/Library/Python/2.6/site-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/engine/base.py:1351(_cursor_execute)
Time spent within each function
Mon Jul 19 20:29:16 2010 prof_stats
194856576 function calls (194856471 primitive calls) in 538.608 CPU
seconds
Ordered by: internal time
List reduced from 460 to 20 due to restriction <20>
ncalls tottime percall cumtime percall filename:lineno(function)
18551033 290.353 0.000 290.353 0.000 {built-in method __new__ of type
object at 0xf6360}
18538000 124.784 0.000 497.845 0.000
/System/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/decimal.py:511(__new__)
18538021 45.568 0.000 45.568 0.000 {built-in method match}
1 31.366 31.366 529.211 529.211 {method 'fetchall' of
'psycopg2._psycopg.cursor' objects}
74152000 17.219 0.000 17.219 0.000 {built-in method group}
18538630 9.908 0.000 9.908 0.000 {isinstance}
18538000 6.356 0.000 6.356 0.000 {method 'lstrip' of 'str' objects}
1 5.482 5.482 5.482 5.482 {method 'execute' of
'psycopg2._psycopg.cursor' objects}
18538016 2.852 0.000 2.852 0.000 {method 'strip' of 'str' objects}
1 1.968 1.968 538.608 538.608 <string>:1(<module>)
9270145 1.487 0.000 1.487 0.000 {len}
4000 0.634 0.000 0.634 0.000
/Library/Python/2.6/site-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/dialects/postgresql/base.py:201(copy_value)
8000 0.300 0.000 0.300 0.000
/Library/Python/2.6/site-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/dialects/postgresql/base.py:209(compare_values)
12000 0.037 0.000 1.048 0.000
/Library/Python/2.6/site-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/orm/attributes.py:1336(from_attribute)
1 0.036 0.036 536.414 536.414
/Library/Python/2.6/site-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/orm/query.py:1447(all)
3000 0.029 0.000 1.124 0.000
/Library/Python/2.6/site-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/orm/state.py:422(_get_modified)
1000 0.023 0.000 1.538 0.002
/Library/Python/2.6/site-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/orm/state.py:374(commit_all)
12000 0.021 0.000 1.090 0.000
/Library/Python/2.6/site-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/orm/attributes.py:509(check_mutable_modified)
12000 0.019 0.000 1.069 0.000
/Library/Python/2.6/site-packages/SQLAlchemy-0.6.3-py2.6.egg/sqlalchemy/orm/attributes.py:500(get_history)
12159 0.015 0.000 0.015 0.000 {hasattr}
Time taken: 538.641896009 seconds.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
