.... forgot the attachment, sorry.
Matt Doran wrote:
> Hi there,
>
> I'm very new to SQLObject, but I really like it's simplicity. However
> I've encountered an issue that I'm struggling to resolve.
>
> I'm using the latest stable build - sqlobject-0.7.1dev_r1684-py2.4,
> and also pysqlite 2.2.0.
>
> However I've found the performance is very slow. I can do about 10
> inserts a second. This is similar performance to what I can achieve
> with pysqlite directly if I commit after every insert. However, if I
> commit after after a batch insert I can insert 1000 rows per second
> with pysqlite directly.
>
> It appears that SQLObject is performing a commit after every operation
> (i.e. auto-commit), however I have not found a way to disable this
> behaviour. I've tried "connection.autoCommit = False" and tried
> explicitly using transactions, but neither seemed to work.
>
> I've attached a script showing what I've been trying. Here's a summary:
>
> connection = connectionForURI(connection_string)
> connection.autoCommit = False
> sqlhub.processConnection = connection
>
> trans = connection.transaction()
>
> # ... do inserts...
>
> trans.commit()
>
>
>
> Any advice would be greatly appreciated!
>
> Cheers,
> Matt
from sqlobject import *
import os
import datetime
db_filename = os.path.abspath('sqlite.db')
if os.path.exists(db_filename):
os.unlink(db_filename)
if db_filename[1] == ':':
# Fix path for windows
db_filename = db_filename[0] + "|" + db_filename[2:]
connection_string = 'sqlite:/' + db_filename
connection = connectionForURI(connection_string)
connection.autoCommit = False
sqlhub.processConnection = connection
class Test(SQLObject):
number = IntCol()
desc = StringCol()
Test.createTable()
NUM_INSERTS = 100
if True:
trans = connection.transaction()
#connection.debug = True
start = datetime.datetime.now()
for i in range(1, NUM_INSERTS):
#print i
t = Test(number=i, desc="desc" + str(i))
trans.commit()
stop = datetime.datetime.now()
print "sqlobject: Inserting %d rows took: %s Per Insert: %s" % (NUM_INSERTS, str(stop - start), str((stop - start) / NUM_INSERTS))
connection.close()
####################
if True:
from pysqlite2 import dbapi2 as sqlite
con = sqlite.connect("sqlite.db")
cur = con.cursor()
start = datetime.datetime.now()
for i in range(NUM_INSERTS + 1, NUM_INSERTS * 2):
#print i
cur.execute("insert into test (number, desc) values (?, ?);", (i, "desc" + str(i)))
con.commit()
stop = datetime.datetime.now()
print "pysqlite: Inserting %d rows took: %s Per Insert: %s" % (NUM_INSERTS, str(stop - start), str((stop - start) / NUM_INSERTS))
con.close()
