On Nov 20, 2008, at 7:07 PM, SinJax wrote:
> > Hi, I've made this post already on my blog but it was suggested i post > here as it might be an interesting point of discussion. > > The first scenario is a single table with 24,000 rows. The problem is > that using SQLAlchemy through Elixir to map this table to an object, > and performing a fairly naive MappedThing.query().all() the process > takes roughly 4.8 seconds to return every item! Compared to 0.3 > seconds total time taken to get and store every row in a tuple using > MysqlDb and a cursor. > > Furthermore, when i attempt to use the object as i'd actually want in > practise, i.e. join the values of this 24,000 row table to appropriate > other tables as defined by the database's foreign keys, the whole > process takes a really long time, running a very inefficient join > query taking roughly 10 seconds to complete. Again this is compared to > a set of queries taking under 1 second in pure SQL > > The scenario where i'd want such a query where i get all the data is > primarily administrative and subsequently arguably it doesn't matter > if its slow. However similarly unusable speeds are found even when > running slightly cut down versions for users. My solution at the > moment is to simply use SQLAlchemy as a glorified MysqlDb connection > with a very fancy SQL statement generation library. I'd like to use > the ORM sides more but with these speeds i'm not sure i can! > > The question is, is SQLAlchemy supposed to be this slow? Is this the > cost of wanting your data as objects? Has anyone else got experience > with SQLAlchemy working with larger databases and if so how does it > fair on such queries? Are they just avoided entirely.... or am i just > approaching this problem from the wrong direction, in which case any > advice would be much appreciated! > SQLAlchemy's ORM is extremely fast, and in particular is super- optimized for fetching rows. However, an ORM is by nature much slower than a raw cursor. A discussion of this can be found here: http://techspot.zzzeek.org/?p=17 . Note also that SQLA 0.5 is a further 15% faster when fetching ORM rows than the 0.4 version profiled there. The only faster ORM in Python is Storm when used with their newly released C extensions, which allows it to fetch ORM rows as fast as SQLAlchemy's ResultProxy. Without their C extension in use, SQLA 0.5 is about 40% faster. Needless to say C extensions are also on the roadmap for SQLAlchemy so we hope to catch up to them again hopefully within the next year. But in general, SQLAlchemy's ORM is considered to be quite performant, and the wiki lists about a dozen websites currently using it. As far as the joins issued, its not possible to comment on that without seeing what joins in question you're referring to, or if you're referring to the contrast between a JOIN and just issuing individual SELECT statements for each row fetched in the primary result set. The SQLA ORM renders joins using "select * from tablea JOIN tableb ON <criterion>", so there is no extra inefficiency introduced in that use case versus a hand-rendered join. But overall, ORMs are designed to be used for fine-tuned persistence/ query operations with a high degree of automation, and not bulk operations such as dumping an entire table of 24,000 rows. This is why SQLAlchemy offers two levels of functionality, the ORM as well as the SQL expression language, which compiles whatever SQL you want and returns rows wrapped in ResultProxy objects, which are thin layers on top of cursor objects that provide convenience functionality as well as Python type conversion. If you are dealing with many thousands of rows, this is the appropriate API to be used, introducing relatively minimal overhead versus a raw cursor, with variability introduced by how many row-processing TypeEngine objects are in use. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
