I've noticed some large (10x) performance differences between sqlalchemy (no
ORM) and plain DB API when using sqlite and was wondering if that is
something expected even when I'm (trying to) avoid the ORM stuff or if I'm
just doing something wrong.
We have lots of CSV files and I wanted to turn each file into a sqlite db to
make another project easier. I wrote an importer using the raw sqlite3
dbapi and then again using sqlalchemy (no ORM I hope). All the importer
does is loop over the lines of the file and insert rows into the database.
The raw dbapi version can insert ~5000-7000 rows per second whereas the
sqlalchemy version does about ~300-500 rows per second.
When I turned on metadata.engine.echo I didn't see sqlalchemy doing anything
beyond what I expected it to; just a bunch of inserts followed by a commit
when I tell it to.
Is this performance disparity expected?
Normally, I wouldn't mind. Unfortunately, the data I'm importing is *huge*
(each file can have 50,000-100,000 "rows") so the difference between 400
rows per second and 5000 rows per second becomes important for the end-user
app that is being developed.
The essence of the two scripts are reproduced below; maybe I'm doing
something horribly wrong in sqlalchemy.
Thanks for any help,
Justus
sqlimporter-dbapi.py:
connection = sqlite.connect(sqlfile)
cursor = connection.cursor()
for row in get_csvdata(f):
cursor.execute('insert into results value (?,?,?,?,?)', row)
connection.commit()
sqlimporter-alchemy.py:
db = sqlalchemy.create_engine('sqlite:///' + sqlfile)
metadata = sqlalchemy.MetaData()
metadata.connect(db)
results_table = sqlalchemy.Table('results', metadata, autoload=True)
insert_results = results_table.insert()
connection = db.connect()
trans = connection.begin()
for row in get_csvdata(f):
connection.execute(insert_results, test_guid=row[0],
timestamp=row[1], ....)
trans.commit()
---
Justus
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
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
-~----------~----~----~----~------~----~------~--~---
