Hey Oleg, Everyone,
Please don't take this as 'rude' but, I think that the thing
SQLObject development should -really- focus on is speed. Perhaps I am
doing something fundamentally wrong with the following code but, for the
life of me, I can't figure out why. I know your going to hate me saying
this but.. compare the SQLObject vs SQLAlchemy
Starting benchmarking with 100000 records (inserting and selecting)
.
Inserting 100000 records into SQLite(memory) with SQLAlchemy
Number of records selected: 100000
Time for SQLAlchemy with SQlite db in Memory: 2.71 seconds
Inserting 100000 records into SQLite(Disk) with SQLAlchemy
Number of records selected: 100000
Time for SQLAlchemy with SQlite db on Disk: 2.64 seconds
Versus
Starting benchmarking with 100000 records (inserting and selecting)
.
Inserting 100000 records into SQLite(Memory) with SQLObject
Number of records selected: 100000
Time for SQLObject with db in memory: 38.53 seconds
and when I change it to put the sqlite db onto the -exact- same disk as
SQLAlchemy used
Time for SQLObject with db on disk: 79.74 seconds
So, that's roughly 20 times slower for memory, and 40 times slower for
disk.
As I said, if I am doing something wrong in the example programs, please
do feel free to correct me, but, otherwise, the speed .. well ..
'sucks'. You may ask how 'likely' is 100k inserts, but the figures are
even -worse- for object instantiation from a database .. I mean a LOT
worse :( now, perhaps SQLObject does things like provide sqlmeta or ..
some such.. but.. 20 -times- slower is the trade off ? seems like a bad
idea to me :\
Ideas ? Thoughts ? Am I crazy ?
Regards
Stef
import sqlalchemy as SA
import time, os
def testSA(n):
print "Inserting %s records into SQLite(Disk) with SQLAlchemy"%n
db = SA.create_engine('sqlite:////tmp/tutorial.db')
metadata = SA.MetaData()
metadata.connect(db)
Person_table = SA.Table('Person', metadata,
SA.Column('name', SA.String(40)),
SA.Column('sex', SA.Integer),
SA.Column('age', SA.Integer))
#metadata.engine.echo = False
Person_table.create(checkfirst=True)
i = Person_table.insert()
i.execute([{'name':'John Doe','sex':1,'age':35} for j in xrange(n)])
s = Person_table.select()
r = s.execute()
print "Number of records selected: %s"%(len(r.fetchall()))
Person_table.drop(checkfirst=False)
def testSAMem(n):
print "Inserting %s records into SQLite(memory) with SQLAlchemy"%n
db = SA.create_engine('sqlite://')
metadata = SA.MetaData()
metadata.connect(db)
Person_table = SA.Table('Person', metadata,
SA.Column('name', SA.String(40)),
SA.Column('sex', SA.Integer),
SA.Column('age', SA.Integer))
#metadata.engine.echo = False
#metadata.bind =
Person_table.create(checkfirst=True)
i = Person_table.insert()
i.execute([{'name':'John Doe','sex':1,'age':35} for j in xrange(n)])
s = Person_table.select()
r = s.execute()
print "Number of records selected: %s\n"%(len(r.fetchall()))
Person_table.drop(checkfirst=False)
n=100000
print "Starting benchmarking with %s records (inserting and selecting)\n."%n
t4 = time.clock()
testSAMem(n)
t5 = time.clock()
print 'Time for SQLAlchemy with SQlite db in Memory: %s seconds\n'%(t5-t4)
t6 = time.clock()
testSA(n)
t7 = time.clock()
print 'Time for SQLAlchemy with SQlite db on Disk: %s seconds\n'%(t7-t6)
from sqlobject import *
import time, os
class Person(SQLObject):
name = StringCol()
sex = IntCol()
age = IntCol()
def testSobj(n):
print "Inserting %s records into SQLite(Memory) with SQLObject"%n
[Person(name='john doe',sex=1,age=35) for i in xrange(n)]
print "Number of records selected: %s"%Person.select().count()
sqlhub.processConnection = connectionForURI('sqlite:/:memory:')
sqlhub.processConnection._pool = []
n=100000
print "Starting benchmarking with %s records (inserting and selecting)\n."%n
t0= time.clock()
Person.createTable()
testSobj(n)
Person.dropTable()
t1=time.clock()
print 'Time for SQLObject with db in memory: %s seconds\n'%(t1-t0)
------------------------------------------------------------------------------
Register Now for Creativity and Technology (CaT), June 3rd, NYC. CaT
is a gathering of tech-side developers & brand creativity professionals. Meet
the minds behind Google Creative Lab, Visual Complexity, Processing, &
iPhoneDevCamp asthey present alongside digital heavyweights like Barbarian
Group, R/GA, & Big Spaceship. http://www.creativitycat.com
_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
