On Apr 19, 2007, at 8:50 PM, Karthik Krishnamurthy wrote:
> How does sqlalchemy behave when querying tables with large number > of rows. I see it constructing a query and getting all results and > mapping > them into instances before iterating through them for a query like > > for node in opsdb.queryNodes(): > print node > > > Is there a way to change this behaviour to have it return an > iterator as a result to the query and have it build instances one > at a time. > this behavior is only within the ORM; when using ResultProxy directly there is no pre-fetching behavior implemented within SA (except in the case of a new buffering result proxy in the trunk that is required for usage with Postgres server-side cursors). but note that several DBAPIs prefetch all the results anyway, such as is the default behavior for postgres, so you arent necessarily getting any performance savings from an IO perspective. for iterating through very large tables the ResultProxy is probably more appropriate since rows are discarded immediately (not so with the ORM). within ORM it is not trivial to implement because a single instance may be comprised of many rows due to eager loading and a "lookahead" buffering scheme would be required. this would add a little bit of latency to the instances() method since more complex decisionmaking would have to occur for each row fetched, and we also get a performance boost out of being able to call fetchall() which shows definite performance advantages with some DBAPIs. the method is optimized for the much more common "fetchall()" case right now....i think its exceedingly rare that someone wants to load mapped objects but not fetch the entire result, without already knowing the number of results they want in which case they use LIMIT. keep in mind that mapped objects, when loaded, are all stored in the Session immediately, so even if there were iterable behavior, youd still have a fully-buffered "collection" of what youve fetched so far im memory. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
