On Jul 11, 2008, at 5:19 AM, Henk wrote:
> > What we see here is that of the total of 5.965 seconds, the test > spend only about 0.585 seconds performing the actual SQL querys, or > about 10% of the total time. The other 90% of the time we are spending > on SA overhead (except for the times.py line which is somewhere in the > MySQLdb driver). > > If I look at the cummulative output of the profiler (ommitted for > brevity) I see for instance that SA spends 1.3 seconds compiling the > SQL expressions (it apparently recompiles the same SQL expression on > each iteration). > > My question to the SA community would be how much ORM overhead do you > find acceptable? and how does SA compare to for instance to java's > Hibernate in this regard or other ORM solutions? As far as Hibernate, I work quite a bit with Hibernate and am currently creating an almost exact port of a Java/Hibernate application using Pylons and SQLAlchemy. My observation is that SQLAlchemy is probably a little slower on execution overhead, but is faster when fetching large numbers of rows. Your test is very much focused on single execution speed. The funny thing is, the SQLAlchemy version of the application overall is *much* faster than the Hibernate version, simply because SQLAlchemy knows how to issue a lot fewer queries to get something done. The main example is loading a list of A, each of which has a relation to B and then a relation to C - you need to display a slice of the "A" table (using LIMIT/OFFSET) as well as all the B's and C's. This is probably the most common operation one needs to do with an ORM, and Hibernate quite simply cannot do this efficiently - see http://www.hibernate.org/117.html#A12 : "It should be also obvious why resultset row-based "limit" operations, such as setFirstResult(5) and setMaxResults(10) do not work with these kind of eager fetch queries. If you limit the resultset to a certain number of rows, you cut off data randomly. One day Hibernate might be smart enough to know that if you call setFirstResult() or setMaxResults() it should not use a join, but a second SQL SELECT. Try it, your version of Hibernate might already be smart enough. If not, write two queries, one for limiting stuff, the other for eager fetching." As it turns out, SQLAlchemy was "smart enough" to do this correctly from day one (though I'm not currently aware of any other ORM that does this). When querying for rows with a limit/offset in conjunction with eager joins, SQLA wraps the limited results in a subquery, and applies the eager joins to that subquery. The efficiency of the query is much greater than what you'd get with Hibernate since the outer joins are against only a small slice of the total rows, and of course you get the correct results back, and there's no need to fall back to a second per-child-object query, which I've found nearly impossible to prevent Hibernate from doing in many cases. SQLA is very good at not "dropping back" into lazy loading when eager loads are requested. Because of reasons like this, most Hibernate applications speed up significantly when converted to SQLAlchemy, since you'll almost certainly see at least a few areas where Hibernate issued a few hundred queries that SQLA issues only one. SQLA's very dynamic generation of queries is also a reason we don't rely upon caching of the "compiled" form of a SQL query by default - the complexity and memory overhead of doing so would negate any speed improvements. While statement compilation adds some overhead, its not really the primary overhead during normal operation; fetching rows and converting to object instances, along with the associated bookkeeping, is where most time gets spent. In your own application, you can cache the "compiled" form of a SQL query by using statement.compile() and use it in conjunction with an ORM query (via instances()), if there's a particular critical section in which you're trying to optimize; but I think you'll find the aggregate time savings (versus just issuing one less query) are marginal. If you're still skeptical of the overhead of statement compilation, see one very high capacity website which uses it regularly, http://www.reddit.com . In their recently released source code, you can see at http://code.reddit.com/browser/r2/r2/lib/db/tdb_sql.py#L458 that they are using a SQLAlchemy "select()" construct, built fresh on each request and compiled, at the base of their SQL database library. A few hundred function calls here are not the issue; it's the fact that *any* SQL at all is issued which places a burden on the application. So Reddit scales the way any website has to scale; by adding processing power horizontally both at the database level and at the webserver level, and by applying a second level caching solution (more on this below). > I would love to help out trying to reduce this overhead, but I have no > idea what the SA devs have in mind for the future of SA with regard to > speed (e.g. speed vs. feature completeness). In the tutorials and > documentation there is currently a strong emphasis on use cases > highlighting the very dynamic nature of building complex queries using > SA's ORM tools. Not so much on how to perform simple (and static) > queries fast using the ORM. > Maybe there is a way to meoize the results of the compile step so that > this does not need to be redone all the time while the Query remains > the same?. Well in your test, you are clearing the Session on each iteration. If you weren't doing that, then the object you loaded would be pulled straight from the Session each time, so in that case the "memoization" is already complete. Similarly, a Query issued for any number of rows runs much faster when the resulting objects are already present in the Session since they do not need to be re-created or populated again. For a more general form of "memoization" of queries and their results, what you're looking for is a second level cache. Hibernate suppllies a 2nd level caching solution but SQLAlchemy currently does not, although simple ones are fairly straightforward to create based on a product such as Memcached, or even just well-maintained dicts. Any database-driven application which needs very high performing display of data requires a second level cache of some kind, whether or not an ORM is used; while SQLA does not have a built in solution to the second level caching problem, it also didn't create the issue. Overall, the issue of performance within SQLAlchemy has been addressed exhaustively and continues to be on a daily basis; at this point, the path to further speedups lies along two avenues - one is that we constantly review the code and find small incremental gains to be made; in particular we've squeezed out 10-20% more speed in SQLA 0.5 versus 0.4. We have performance tests in our test suite which run through a full battery of SQL and ORM operations, ensuring that funciton call counts fall within an expected range (http://www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/test/profiling/zoomark_orm.py for example). The other avenue in which we are very interested is applying native extensions to SQLA at key performance areas. Two that we have in mind are result set processing and attribute instrumentation. This is a longer road to travel (in particular since we are in need of a C developer with some time to kill) but hopefully we'll get a resource for this. There is another ORM called Storm which is in the process of creating C extensions for their own product (the development of which is fully funded by Storm's parent company Canonical, a luxury SQLAlchemy unfortunately does not have), so that's something else you might want to check out. A comparison of SQLAlchemy and Storm's speeds (with the pre-C-extension version of Storm) is at http://techspot.zzzeek.org/?p=17 , where you'll see that Storm is generally a little faster than SQLA but SQLA loads large numbers of rows more quickly (note that 0.5 is even faster than the 0.4 tested in that post). I'm very satisfied with those results since SQLA is philosophically more feature rich than Storm, yet the speed of both products (sans any native extensions) are not very different. In general, as long as speed is "decent", our users don't have any issues with it. For this reason, while the C-based extension route would be helpful, it's not a super-burning issue either. Any web application, no matter what platform it's running on, is scaled up to higher capacity through second level caching and via the addition of more processing support horizontally. There's plenty of applications running SQLAlchemy, SQLObject, Storm, etc. at this point, all of which introduce significant overhead versus raw SQL, and in general they don't seem to be falling over. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
