Good morning!
I hope I'm not tipping any sacred cows here, but for simple SqlA
objects, it seems to be a heckuva lot faster to just insert into the
table directly (using SqlA expression language) than to insert the
objects via session.flush(). In the attached tests, I'm observing a
10x -20x speedup. I'm still new to SqlA, so I was hoping the list
here would be kind enough to verify my observation.
My questions:
1. If so, why? I assume it's because session_flush() does seperate
insert statments (as verified when echo = True is on).
2. In test 3, is this a reasonable away to "convert" from "session"
to direct table insert? Is there a simpler way than the Thing.to_dict
method I hacked together.
3. Are these valid tests? I don't want to have all the embarrassment
of some others who have 'slammed' SqlA without a proper grounding.
I'm no expert, and I want to make sure what I have is something
approximating idiomatic SqlA. I tried to be generous about what to
include in the timed section of each test. I do have autoflush off,
and I'm using Sqlite (in memory), which might affect things.
4. If there is a faster way to flush out a session, I'm all ears! I
understand the Big Win (tm) of the ORM is programmer simplicity and
power, but if I can get that without major hits to performance, I'd
like to be able to Be Greedy (tm) and have it all.
-----------------
#!/usr/bin/env python2
import sys
import itertools
import time
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table, Column, Integer, String, MetaData,
ForeignKey, Boolean
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class Thing(Base):
__tablename__ = 'asn_change'
id = Column(Integer, primary_key=True, nullable=False)
datum = Column(String, primary_key=True, nullable=False)
start = Column(Integer, primary_key=True, nullable=False)
stop = Column(Integer, primary_key=True, nullable=False)
def to_dict(self):
return dict(id=self.id, datum=self.datum, start=self.start,
stop=self.start)
def dummy_setup(connstring='sqlite:///:memory:'):
engine = create_engine(connstring, echo=False)
Session = sessionmaker(bind=engine, autoflush=False, autocommit=False)
session = Session()
Base.metadata.bind = engine
Base.metadata.create_all()
return session, engine
def clean_up(session):
session.expunge_all()
session.query(Thing).delete()
session.commit()
## Tests
class Tests(object):
def setUp(self):
self.session, self.engine = dummy_setup()
self.R = 10000
def test_01_orm(self):
session = self.session
clean_up(session)
for ii in xrange(self.R):
session.add(Thing(id=ii,datum="some data %i" %ii,
start=0,stop=999999999))
now = time.time()
session.flush()
session.commit()
t = time.time() - now
print "timing: %f2.2" % t
assert True
def test_02_direct_insert(self):
session = self.session
clean_up(session)
stm = Thing.__table__.insert().compile()
ipfx = [dict(id=ii,datum="some data %i"
%ii,start=0,stop=999999999) for ii in xrange(self.R)]
now = time.time()
stm.execute(ipfx)
t = time.time() - now
print "timing: %f2.2" % t
assert True
def test_03_convert_from_session(self):
session = self.session
clean_up(session)
stm = Thing.__table__.insert().compile()
for ii in xrange(self.R):
session.add(Thing(id=ii,datum="some data %i" %ii,
start=0,stop=999999999))
# count the conversion time as part of the test
now = time.time()
ipfx = [x.to_dict() for x in session.new]
stm.execute( ipfx)
session.new.clear()
t = time.time() - now
print "timing: %f2.2" % t
assert True
-----
$ nosetests -v -s insertion_test.py
insertion_test.Tests.test_01_orm ...
timing: 8.2513552.2
ok
insertion_test.Tests.test_02_direct_insert ... timing: 0.5210562.2
ok
insertion_test.Tests.test_03_convert_from_session ... timing: 0.8730292.2
ok
----------------------------------------------------------------------
Ran 3 tests in 14.247s
OK
--~--~---------~--~----~------------~-------~--~----~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
