Hi everyone,
I'm doing some test to evaluate the performance of querying with
sqlalchemy via ORM. I wrote a simple script to measure the execution
time of a simple select query made on relatively small table (300 000
records, 6 columns) in sqlite. Here is the script:
from sqlalchemy import *
from sqlalchemy.orm import mapper, sessionmaker
from time import clock
engine = create_engine('sqlite:///test.db', echo=False)
metadata = MetaData()
table = Table('projects', metadata,
Column('id', Integer, primary_key=True),
Column('inp1', String(50)),
Column('inp2', String(50)),
Column('inp3', String(50)),
Column('inp4', String(50)),
Column('inp5', String(50)),
)
class Project(object) :
pass
mapper(Project, table)
metadata.create_all(engine)
t = []
for i in range(300000) :
t.append({"inp1":str(i), "inp2":str(i), "inp3":str(i),
"inp4":str(i), "inp5":str(i)})
c = clock()
engine.execute(table.insert(), t)
print "Insert: "+str(clock()-c)
session = sessionmaker(bind=engine)()
c = clock()
res = engine.execute(table.select()).fetchall()
print "Sql query: "+str(clock()-c)
c = clock()
res = session.query(Project).all()
print "Session query: "+str(clock()-c)
On my PC (windows 7, 64-bit, intel i7 2.93 Ghz) this is the output:
Insert: 3.41080167807
Sql query: 1.26728367673
Session query: 19.6452334842
The execution time of the ORM query is about 20 times the SQL one, and
this is definitely discouraging. So I guess if I'm doing something
wrong or if there are some tricks when using ORM that I'm not
considering. Any help is really appreciated.
Thanks in advance!
--
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.
