Re: [SQLObject] Disabling sqlite autocommits to speed up inserts

[Matt Doran]

.... 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()