On Thu, May 8, 2008 at 3:03 PM, alex clemesha clemesha@gmail.com wrote:
But, I hoping to solve some immediate problems with Twisted and SQLAlchemy, so ...
I've long worked on trying to integrate sqlalchemy and twisted in a useful way but that can only be done when using simple queries. There are countless reasons behind this limitation, like the fact that sqlalchemy objects can do a lot of stuff behind the scenes or because code that you write in other places might use some assumptions because it's sync rather than async, and there are also problems with possible bugs and assumptions within sqlalchemy itself. Basically it's not safe and I wouldn't recommend it for anyone to use the orm together within a Twisted process.
What I ended up doing is creating subprocesses that have fairly complex logic and usually spend more than a few seconds doing their job. By doing this I simply use sqlalchemy synchronously and without any problems (I was starting to go completely crazy while trying to debug time-dependent errors when I decided to refactor my code to use subprocesses instead, everything started working smoothly without any relevant change in the logic or in the test code and now I can also run the subprocess as a system script to check and verify stuff or debug (with pdb)).
Considering that an ORM is only useful (?) for very complex logic I consider my approach completely sane. Use the query generator for everything and reserve the orm for long running tasks.
nadbapi and storm-twisted tries to solve the orm problem in a similar way but nadbapi is no longer maintained, anyway both currently suffer from an incredible number of deferreds that they generate making the use of defgen an absolute requirement (and thus lowering overall performance). If you look hard enough in glyph's old blog or in this list (I can't remember where exactly) you can find a post about letting the database deal with concurrency and not spread it in application code (aka having tons of deferreds). It is true that not using threads increases speed and memory usage but for this particular case 99% of the time you are not going to need neither of them anyway.
closing: I recently refactored some code that used sqlalchemy orm into using just the query generator and instead of taking 17 seconds it now takes 7 seconds and memory usage has decreased considerably. It's not really just because an orm is slower than simply executing queries, but rather because querying the db was so easy; it was basically done "recklessly" without thinking too much about the performance hit of going back and forth to the database. This is basically why I'd rather not use ORMs unless forced by legacy code or because generating a query would introduce so much complexity to make it an application in itself (very rare case IMHO).