On Jul 11, 2007, at 2:02 PM, Justus Pendleton wrote:
> > 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? you should definitely call it via executemany() semantics, where you issue just a single execute() call (or a small set of them), each one containing a list of parameter sets to be inserted. the form is the last example here: http://www.sqlalchemy.org/docs/ sqlconstruction.html#sql_insert . using this method, all of the overhead of compiling and executing within SQLAlchemy is cut down to a negligble amount and the work is placed almost totally within the DBAPI. you also optimize the operation on the database side, as various databases can pre-"prepare" a statement handle that is used repeatedly. the performance overhead by calling individual executes() is that of compiling the statement into a string each time, creating an execution context, doing some guesswork on the given bind parameters and checking for defaults, and then executing. a lot of "automatic" stuff is done for you. like ants said, you can reduce the "compile" overhead by compiling the statement ahead of time: statement = table.insert().compile() --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
