I'm writing a small project and I decided to try pysqlite. The database
consists of one master table with five columns and two detail tables
with one and two columns each (not counting foreign key columns). The
program scans an input file and inserts data into those three tables.
First I used pysqlite (ver 2). It took a few seconds to parse and
populate one thousand of main record and five-six thousand detail
records (including a print statement for each main record). This is
acceptable.
Then I decided to give a try to sqlobject (I had to revert to pysqlite
1.x). I created something like this:
class T1(SQLObject):
col1 = StringCol()
col2 = StringCol(lengt h=5)
...
det1 = MultipleJoin('T 2')
det2 = MultipleJoin('T 3')
class T2(SQLObject):
...
t1 = ForeignKey('T1' )
class T2(SQLObject):
...
t1 = ForeignKey('T1' )
My main loop looks like this:
for r1 in par.parse(filen ame):
# r1 is an intermediary object because I didn't
# know how to instantiate an instance without
# creating a record automatically, especially
# because I didn't have all mandatory values
# up until the end of the parse so I had to
# keep the values in paralel instead of
# storing them directly to my brand new class
print r
t1 = T1(col1=r.col1, ...)
for r2 in r1.det1:
t2 = T2(..., t1=t1)
for r3 in r1.det2:
t2 = T3(..., t1=t1)
Now this takes around half a second for ONE master record!!!
When I turned on the debug mode of connection I could see a lots of
*select* and *commit* statements. I tried to disable autocommit but
with no success. I also tried to explicitly provide ID column (hoping
to avoid select) but also with no success.
"autocommit off" attempt:
connection_stri ng = 'sqlite:/' + db_filename +'?autoCommit=0 '
"no select" attempt:
t1 = T1(id=t1id, col1=r.col1, ...)
Any ideas how to make sqlobject work as fast as plain pysqlite.
P.S. I used 0.6.1 version of sqlobject, but later I downloaded fresh
version from SVN (upgraded pysqlite to 2.x, downloaded formencode,
ez_setup, setuptools and maybe others) but it still doesn't work any
better.
qvx
consists of one master table with five columns and two detail tables
with one and two columns each (not counting foreign key columns). The
program scans an input file and inserts data into those three tables.
First I used pysqlite (ver 2). It took a few seconds to parse and
populate one thousand of main record and five-six thousand detail
records (including a print statement for each main record). This is
acceptable.
Then I decided to give a try to sqlobject (I had to revert to pysqlite
1.x). I created something like this:
class T1(SQLObject):
col1 = StringCol()
col2 = StringCol(lengt h=5)
...
det1 = MultipleJoin('T 2')
det2 = MultipleJoin('T 3')
class T2(SQLObject):
...
t1 = ForeignKey('T1' )
class T2(SQLObject):
...
t1 = ForeignKey('T1' )
My main loop looks like this:
for r1 in par.parse(filen ame):
# r1 is an intermediary object because I didn't
# know how to instantiate an instance without
# creating a record automatically, especially
# because I didn't have all mandatory values
# up until the end of the parse so I had to
# keep the values in paralel instead of
# storing them directly to my brand new class
print r
t1 = T1(col1=r.col1, ...)
for r2 in r1.det1:
t2 = T2(..., t1=t1)
for r3 in r1.det2:
t2 = T3(..., t1=t1)
Now this takes around half a second for ONE master record!!!
When I turned on the debug mode of connection I could see a lots of
*select* and *commit* statements. I tried to disable autocommit but
with no success. I also tried to explicitly provide ID column (hoping
to avoid select) but also with no success.
"autocommit off" attempt:
connection_stri ng = 'sqlite:/' + db_filename +'?autoCommit=0 '
"no select" attempt:
t1 = T1(id=t1id, col1=r.col1, ...)
Any ideas how to make sqlobject work as fast as plain pysqlite.
P.S. I used 0.6.1 version of sqlobject, but later I downloaded fresh
version from SVN (upgraded pysqlite to 2.x, downloaded formencode,
ez_setup, setuptools and maybe others) but it still doesn't work any
better.
qvx
Comment