On Nov 25, 2008, at 12:51 AM, TheShadow wrote:
> > 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. a 2 second round trip for 6 queries, where the SELECT statements return just one or two rows, sounds very slow and indicates either network latency or some issue with the MySQL database. But I'm still not clear on what results you get with which kinds of setup. In any case you should be running the Python profiler, preferably hotshot, to see where time is being spent. It sounds like you're going to see most of the time spent sending/receiving over the cursor. MySQLdb and SQLA expressions shouldn't add very much overhead. > 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. I have to disagree here. The majority of SQLAlchemy's documentation chapters do not deal with the ORM. The ORM layer does not need to be "bypassed", it simply is not used. This is described on the first page of the documentation at http://www.sqlalchemy.org/docs/05/intro.html#overview_overview as well as in the following section on "tutorials". The example you did show is using SQLAlchemy, without the ORM. Information on how the engine deals with execution and transactions are here: http://www.sqlalchemy.org/docs/05/dbengine.html . If you'd like to compare SQLAlchemy to a pure DBAPI implementation, the documentation for DBAPI itself is outside of SQLAlchemy's domain. For that, you want to go here: http://www.python.org/dev/peps/pep-0249/ Similarly, documentation for how to profile Python programs is also outside of SQLAlchemy's domain. For that, you'd want to go here: http://www.python.org/doc/2.5.2/lib/profile.html and then here: http://www.python.org/doc/2.5.2/lib/module-hotshot.html . We've observed that hotshot shows more accurate results. When running the profiler, make sure you look at the total time spent for operations. MySQLdb and SQLAlchemy are both going to add several hundred Python function calls to the profile - but despite this, they will mostly have extremely negligible amounts of time added - its usually the case that the network calls on the cursor is where more than half the time actually adds up. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---