Hi,<br><br>Thanks for this insightful comments on Sqlalchmy and Twisted.<br><br><br>I hope I had read your mail before I start my current project. <br><br>At that time, I simply thought using deferToThread would solve <br>
everything since the twisted.enterprise.adbapi simply use threads.deferToThreadPool<br>to make database interface asynchronous.<br><br>Today, I suddenly feel that, as sqlalchemy is not thread safe, using deferToThread might cause unexpected problems.<br>
And I found this mail and it seems that all the code I wrote this week is going to be useless.<br><br>But, I got another question:<br><br>Since adbapi uses deferToThreadPool to make db api asynchronous, does this mean that all python db apis are *thread safe*?<br>
<br>BTW: what does 'query generator' means? Is it a software genrating SQLs?<br><br><div class="gmail_quote">On Fri, May 9, 2008 at 9:39 AM, Valentino Volonghi <span dir="ltr"><<a href="mailto:dialtone@gmail.com">dialtone@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div class="Ih2E3d">On Thu, May 8, 2008 at 3:03 PM, alex clemesha <<a href="mailto:clemesha@gmail.com">clemesha@gmail.com</a>> wrote:<br>
> But, I hoping to solve some immediate problems with Twisted and<br>
> SQLAlchemy, so ...<br>
<br>
</div>I've long worked on trying to integrate sqlalchemy and twisted in a<br>
useful way but that can only be done when using simple queries. There<br>
are countless reasons behind this limitation, like the fact that<br>
sqlalchemy objects can do a lot of stuff behind the scenes or because<br>
code that you write in other places might use some assumptions because<br>
it's sync rather than async, and there are also problems with possible<br>
bugs and assumptions within sqlalchemy itself. Basically it's not safe<br>
and I wouldn't recommend it for anyone to use the orm together within<br>
a Twisted process.<br>
<br>
What I ended up doing is creating subprocesses that have fairly<br>
complex logic and usually spend more than a few seconds doing their<br>
job. By doing this I simply use sqlalchemy synchronously and without<br>
any problems (I was starting to go completely crazy while trying to<br>
debug time-dependent errors when I decided to refactor my code to use<br>
subprocesses instead, everything started working smoothly without any<br>
relevant change in the logic or in the test code and now I can also<br>
run the subprocess as a system script to check and verify stuff or<br>
debug (with pdb)).<br>
<br>
Considering that an ORM is only useful (?) for very complex logic I<br>
consider my approach completely sane. Use the query generator for<br>
everything and reserve the orm for long running tasks.<br>
<br>
nadbapi and storm-twisted tries to solve the orm problem in a similar<br>
way but nadbapi is no longer maintained, anyway both currently suffer<br>
from an incredible number of deferreds that they generate making the<br>
use of defgen an absolute requirement (and thus lowering overall<br>
performance). If you look hard enough in glyph's old blog or in this<br>
list (I can't remember where exactly) you can find a post about<br>
letting the database deal with concurrency and not spread it in<br>
application code (aka having tons of deferreds). It is true that not<br>
using threads increases speed and memory usage but for this particular<br>
case 99% of the time you are not going to need neither of them anyway.<br>
<br>
closing: I recently refactored some code that used sqlalchemy orm into<br>
using just the query generator and instead of taking 17 seconds it now<br>
takes 7 seconds and memory usage has decreased considerably. It's not<br>
really just because an orm is slower than simply executing queries,<br>
but rather because querying the db was so easy; it was basically done<br>
"recklessly" without thinking too much about the performance hit of<br>
going back and forth to the database. This is basically why I'd rather<br>
not use ORMs unless forced by legacy code or because generating a<br>
query would introduce so much complexity to make it an application in<br>
itself (very rare case IMHO).<br>
<font color="#888888"><br>
--<br>
Valentino Volonghi aka Dialtone<br>
Now running MacOS X 10.5<br>
Home Page: <a href="http://www.twisted.it" target="_blank">http://www.twisted.it</a><br>
</font><div><div></div><div class="Wj3C7c"><br>
_______________________________________________<br>
Twisted-Python mailing list<br>
<a href="mailto:Twisted-Python@twistedmatrix.com">Twisted-Python@twistedmatrix.com</a><br>
<a href="http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python" target="_blank">http://twistedmatrix.com/cgi-bin/mailman/listinfo/twisted-python</a><br>
</div></div></blockquote></div><br><br clear="all"><br>-- <br>今天实在是没有任何遗憾的地方，可以拉上被子睡觉了。<br>