On Jul 19, 2010, at 8:33 PM, thatsanicehatyouh...@mac.com wrote: > 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?
psycopg2 returns the Decimal (its a Python built in) natively. You register a new type adapter as they mention here: http://initd.org/psycopg/docs/faq.html#problems-with-type-conversions however I don't know if their float() recipe is calling float() on a string or a Decimal. I'm pretty sure if you used the FLOAT type instead of NUMERIC (i.e. change your table in the DB, or use CAST) it returns floats directly without the decimal thing. > > 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. > -- 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.
