On Nov 24, 2008, at 8:50 PM, TheShadow wrote:
> > http://pastebin.ca/1266308 > > In the link above you will see some output and code. > > Some questions I have: > > 1) Why are these queries taking on a magnitude of 10 times slower than > they should be? > > 2) Could it be the implementation I am using? If so what is > recommended for using the system as you see it (I can't use ORM based > on application requirements) > > 3) Is the connection being reset between queries? > > I'd appreciate any insight. I find sqlalchemy's DB connection scheme > fits the framework of the rest of the app but if these queries can't > be sped up then I'll have to find a different solution. The "magnitude of 10 times slower" figure is questionable since you didn't post any alternate implementation which illustrates it running ten times faster, but I'm assuming that's against a system without autocommit. As for the code, you are relying upon connectionless execution without an explicit transaction so there is a COMMIT occuring after each INSERT and UPDATE. This is probably what is making the operation run slower than it seems it should be. Connections are not "reset" by SQLAlchemy, they are pooled, and in this case each execution results in a connection pool checkout, but that's it. If you're going for speed you'll get much better results without the ORM. The SQLA expression/execution system without the ORM might add a 10-20% latency over raw DBAPI but nothing severe, provided you control your transactional scope. The "threadlocal" strategy is also not needed here unless you plan on calling begin()/commit() on your engine - but in this case if you did issue your statements within a begin()/commit() pair, the latency of each COMMIT would be removed and you'd also eliminate any connection pool traffic until the commit(). --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected] 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 -~----------~----~----~----~------~----~------~--~---
