On Mar 22, 2011, at 3:35 AM, Wouter Overmeire wrote: > > > Before using the intersect i tried also the query you suggest but it > takes very long to complete, compared to the intersect. > articles = > session.query(Article).join(Article.tags).filter(Tag.tag=='tag1').join(Article.authors).filter(Author.name=='author1').all() > takes 75seconds. > > qArticleByTag = > session.query(Article).join(Article.tags).filter(Tag.tag == 'tag1') > qArticleByAuthor = > session.query(Article).join(Article.authors).filter(Author.name == > 'author1') > articles = qArticleByTag.intersect(qArticleByAuthor).all() takes 0.8 > seconds > > That`s almost two decades of difference in speed for the same result! > This i don`t understand, i would expect the chain of joins to be > faster, since the join with Article.authors is on a smaller table > compared to qArticleByAuthor and no intersect is needed. > Could it be the chain of join is making a monster table first joining > all tags and then joining all authors and from this table select the > ones with tag='tag1' and author='author1'. Instead of doing the join > of Article.authors on the filtered articles table (by tag)
well the best way to figure that kind of thing out is to take a look at the plan your database is coming up with. On Postgresql this would be via EXPLAIN. For Oracle, MySQL, the commands are different, take a look at the docs for the DB you're using. It will show how indexes and such are being used (or not). It may mean that you just need to adjust your indexes for this particular query. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
