SQLObject just inserts rows as you create the objects - its a simple
map of attributes to table columns and you're in. The unit of work
pattern is a far more complex operation than a straight row insert
since it organizes any number of dependencies and resolves them in
very open-ended way. Its not optimized for bulk operations, for
that the table.insert() operation will give you near-DBAPI speeds.
The test can be more than doubled in speed (155 sec to 63 sec) by
making the collections act like SQLObject collections, which don't
load anything unless iterated and don't store anything in memory.
Otherwise, the ever-growing in-memory collection, as well as the
collection growing on the other side, will add overhead to the flush
process:
projects = relation('Project',lazy='dynamic',
secondary=projects_users_table,backref=backref('user', lazy='dynamic'))
as well as batching data together to be flushed, so that all 20 users
are flushed at once. note that autoflush kicks in before every query.
for p in range(0,1000):
project = Project(project_id=p+1,name='project'+str(p))
session.add(project)
for u in range(0,20):
# create user object
user_num+=1
user = User(user_id=user_num,login='login'+str(user_num))
session.add(user)
for u in range(0,20):
# find previous user object and add
user = session.query(User).filter(User.user_id==p + u +
1).first()
user.projects.append(project)
in the real world, the pattern of flushing only when needed works out
a lot better than it does in an artificial test. this test is very
artificial. If you remove the needless query for the user object that
was just created and comment out the second "u" loop, the time goes
down to 26 seconds (of course the SQLObject test would be much faster
as well without that).
SQLObject's simpler approach is definitely faster than an ORM like
SQLAlchemy. But if SQLA's approach didn't have advantages too, then
you wouldn't be running these tests ;).
On Feb 19, 2009, at 2:07 PM, Spes wrote:
>
> Hi guys,
>
> I'm working on some project which fetches data across the network and
> stores tham on single place into database. I want to easily work with
> data so I'm looking on different Python ORMs. I tried SQLAlchemy,
> Elixir, SQLObject, ZODB, ORM in Django. For each I wrote simple
> example which looks like this:
>
> Have:
> - project class
> - user class
> and binding table between projects and users.
>
> Repeat this 1000 and measure time of whole task:
> 1. create project
> 2. repeat 20 times:
> 2.1 create new user
> 2.2 get this user (to see slow down of additional SELECT)
> 2.3 assign user to project
>
> On Core2 Duo 2.40GHz, 3MB L2 cache, 2GB memory, Fedora x86-64, MySQL
> MYISAM backend and SQLAlchemy version in 0.4.8 I get these times:
> - SQLAlchemy: 164 s
> - SQLObject: 26.59s
> (there is not big difference between versions 0.4.8 and 0.5.2)
>
> You can find source codes here:
> http://www.fi.muni.cz/~xholer/tmp/orm_sqlobject.py
> http://www.fi.muni.cz/~xholer/tmp/orm_alchemy.py
>
> Can anybody show me what's wrong with my examples and why is the
> performance of SQLAlchemy (and subsequently of the Elixir) so
> terrible?
>
> Many thanks, have a nice day!
> Vlastimil Holer
>
> >
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
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
-~----------~----~----~----~------~----~------~--~---
