On 5/9/07, Michael Bayer <[EMAIL PROTECTED]> wrote: > > > 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).
The original query did have .query(...).select(limit=11), I just forgot to paste that part into the console because I was so intent on inlining the parameters. Nevertheless, the non-limited queries returned 21 records, which is pretty close. > 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 Of course, that's why I requested the 'autoload_columns' feature. :) > (these > columns can be set as "deferred" to not have them part of a default > query). True, but then I have to specify the columns I'm not interested in rather than the ones I am. :) :) Anyway, this is all just to provide feedback on how ORM-based-on-select behaves in a real-world application. Because the non-ORM code is consistently fast while the ORM code is inconsistently slow, I'd better just stick to non-ORM code for this application. The application is a bit unusual because of the need to exclude certain rows and columns from all queries, which is the only reason I'm basing it on a select -- so I can set the rule in one easy-to-audit place rather than in every query. I haven't tried a database view -- maybe that will have better performance since it's built in into the database engine. -- Mike Orr <[EMAIL PROTECTED]> --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
