First I appreciate your responding. But I need more information as to why this is taking so long and this is because of the following:
When I started looking for a DBAPI I started with the MySQLdb module executing queries against local host versus a remote host was faster but even against remote hosts running the queries in my sample still at most took 0.1 seconds and averaging 0.05. (This is with forcing a commit). I decided to move to something else because it didn't support binded variables the way I needed. It is incredibly difficult for me to accept that simple SELECT's are taking 0.1 seconds on average when directly on the DB server I'm looking at under 0.006. So it makes me wonder what is happening under the hood that is eating hundreds of milliseconds. I don't see results like this in PHP or Java so I'm going to have to ask why when I'm basically bypassing all the ORM does it take so long. Currently I'm showing a 2 second round trip for 6 queries which is just unacceptable. On the other side I didn't show any other implementations because there is a severe lack of documentation/samples when it comes to bypassing the ORM layer. I've asked in #sqlalchemy on freenode but it doesn't appear that many people attempt what I am. On Nov 24, 10:29 pm, Michael Bayer <[EMAIL PROTECTED]> wrote: > 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 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 -~----------~----~----~----~------~----~------~--~---
