On May 9, 2007, at 2:46 PM, Mike Orr wrote:
> > The actual SQL time is 1.04 second for this ORM-inspired query: > > SELECT entries.category AS entries_category, entries.title AS > entries_title, entries.thumb200 AS entries_thumb200, entries.creator > AS entries_creator, entries.doctype AS entries_doctype, > entries.filename AS entries_filename, entries.content AS > entries_content, entries.entry_id AS entries_entry_id, > entries.entry_date AS entries_entry_date, entries.is_public AS > entries_is_public, entries.size AS entries_size, entries.orr_id AS > entries_orr_id FROM (SELECT `Entry`.entry_id AS entry_id, > `Entry`.orr_id AS orr_id, `Entry`.entry_date AS entry_date, > `Entry`.creator AS creator, `Entry`.title AS title, `Entry`.category > AS category, `Entry`.content AS content, `Entry`.filename AS filename, > `Entry`.thumb200 AS thumb200, `Entry`.doctype AS doctype, `Entry`.size > AS size, `Entry`.is_public AS is_public FROM `Entry` WHERE > `Entry`.is_public) AS entries WHERE entries.orr_id = 6153 and > entries.category in (1, 2, 3, 4, 6, 7, 8, 9, 10, 11); > > vs 0.14 seconds for this non-ORM one: > > SELECT `Entry`.entry_id, `Entry`.orr_id, `Entry`.category, > `Entry`.title, `Entry`.entry_date, `Entry`.filename, `Entry`.thumb200 > FROM `Entry` WHERE (`Entry`.is_public AND `Entry`.orr_id = 6153) AND > `Entry`.category IN (1,2,3,4,5,6,7,8,9,10,11) order by > `Entry`.entry_date DESC LIMIT 11; > > vs 0.13 seconds for my manual equivalent: > > select entry_id, orr_id, category, title, entry_date, filename, > thumb200 from Entry where is_public and orr_id = 6153 and category in > (1,2,3,4,5,6,7,8,9,10,11) order by entry_date desc limit 11; > > > vs 0.07 seconds for the same with *: > > select * from Entry where is_public and orr_id=6153 and category in > (1,2,3,4,6,7,8,9,10,11); > > I'm tempted to say ORM is good but not when based on a select, at > least not with this particular dataset. this test has many issues that prevents any comparison of the nested select - your non-ORM query has LIMIT 11 inside of it whereas your ORM query does not (use "limit" or limit() with query() for this). also I am assuming you are doing a full fetch - the ORM query has a lot more columns in it which will take time to be fetched (these columns can be set as "deferred" to not have them part of a default query). to time this accurately, you need to put the same external LIMIT on both queries, and run the queries in the database console directly - that will test just the time for the DB to do the initial execution without any fetching. then you need to run both queries in a simple DBAPI application and do a full fetch to see how much additional overhead comes from the fetch. beyond that, the ORM has some additional overhead in creating objects but its more on the order of 50% slower, not 1000% slower. the strategy i would take with this is to use the databases query plan as a guide, to see that the optimizer does in fact flatten the nested selects into something negligible...ive been reading up on MySQL's execution plan view commands and was planning to work with them a little bit. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
