From: Ian B. <ia...@co...> - 2005-08-20 04:40:16
|
Rene Dudfield wrote: > On 8/20/05, Ian Bicking <ia...@co...> wrote: > >>Inserts are a bit slow; going around SQLObject to do inserts is >>generally much faster. You can use connection.query() to run SQL on the >>connection. >> >>Perhaps people on the list have other suggestions. >> > > > Hey, > > why are inserts slow? Is it that a complex class has to be > constructed for each row? > > Or are other queries being run as well for the inserts? Yes and yes. I suspect the instantiation of a class is the bigger cost. But profiling would of course be a good idea; though sometimes I don't trust profiling, because it doesn't include measures of things like GC (significant when you create a bunch of objects and immediately forget about them), and of course the database time, which I think needs to be measured in wall time, not CPU time. I'm not sure, I just know that profiling results sometimes look fishy to me. It might be useful to have a method that does an insert without creating an object, like MyClass.insert(**kw), which would perhaps return an id. That would probably be reasonably easy to implement; of course, before actually committing such a method it would be important to see if it actually helps. To really be fast, you'd want to group all the inserts into one query. You can submit multiple queries to Postgres, and with MySQL you can do an insert with multiple value tuples, and I'm not sure what exactly you'd do for other databases. The problem with this is that it requires changes to the database connections (though I guess you could implement a method in dbconnection.DBAPI that would do it the slow way, and let subclasses override it to improve performance). This could potentially be implemented as MyClass.insert(list_of_dicts); I'm just a little uncomfortable with overloading a method like that (using positional for one meaning, keywords for another), but only a little. -- Ian Bicking / ia...@co... / http://blog.ianbicking.org |