On Tue, Jun 21, 2011 at 6:05 AM, Eduardo <ruche...@googlemail.com> wrote:
> What is the best practice: to chain filters or to collect queries in a > list and then apply intersect_all()? > Overall efficiency will depend on the underlying database engine, but I can't help but expect that most databases will be more efficient with the chained filters query. It would take a really smart optimizer to make the intersect method as efficient as the chained filter. Using an unrealistic set of queries, but it shows the principle. Using intersect_all will generate SQL like this: q1 = sess.query(Book).filter(Book.title=='A') q2 = sess.query(Book).filter(Book.title=='B') q3 = sess.query(Book).filter(Book.title=='C') q4 = sess.query(Book).filter(Book.title=='D') q5 = q1.intersect_all(q2,q3,q4) SELECT anon_1.book_bookid AS anon_1_book_bookid, anon_1.book_title AS anon_1_book_title, anon_1.book_authorid AS anon_1_book_authorid FROM (SELECT book.bookid AS book_bookid, book.title AS book_title, book.authorid AS book_authorid FROM book WHERE book.title = ? INTERSECT ALL SELECT book.bookid AS book_bookid, book.title AS book_title, book.authorid AS book_authorid FROM book WHERE book.title = ? INTERSECT ALL SELECT book.bookid AS book_bookid, book.title AS book_title, book.authorid AS book_authorid FROM book WHERE book.title = ? INTERSECT ALL SELECT book.bookid AS book_bookid, book.title AS book_title, book.authorid AS book_authorid FROM book WHERE book.title = ?) AS anon_1 Chaining filters generates this SQL: q7 = sess.query(Book).filter(Book.title=='A') q7 = q7.filter(Book.title=='B') q7 = q7.filter(Book.title=='C') q7 = q7.filter(Book.title=='D') SELECT book.bookid AS book_bookid, book.title AS book_title, book.authorid AS book_authorid FROM book WHERE book.title = ? AND book.title = ? AND book.title = ? AND book.title = ? -- Mike Conley -- 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.