On 07/01/2011 08:45 AM, RVince wrote: > I have a query using join() where my tables have a few hundred > thousand records in Postgres, that takes about 30-45 seconds > regardless of how the tables are indexed: > > query = > Session.query(SmartdataEligibilityRecord).join(Member).filter(Member.id==SmartdataEligibilityRecord.member_id).order_by(Member.last_name.asc()).all() > > If I rewrite this as: > > connection = engine.connect() > trans = connection.begin() > qs = "select * from smartdata_eligibility_records,members where > members.id=smartdata_eligibility_records.member_id order by > members.last_name asc;"; > query =connection.execute(qs) > connection.close() > > it flies, taking only a few seconds. My problem is that now the query > object is a ResultProxy type. It looks like under the former way I was > doing this, using join() it returned tuples. Now I am getting errors > such as: > > "TypeError: Sorry, your collection type is not supported by the > paginate module. You can provide a list, a tuple, a SQLAlchemy select > object or a SQLAlchemy ORM-query object." > > Is there a way I can casr this ResultProxy obect to a usable object > here (like tuples?) Thanks, RVince
In your second example, you have not yet retrieved any rows out of the DBAPI cursor. I believe psycopg2 collects all the rows for you anyway at the execute() step, but this is not guaranteed. A more realistic comparison would be to use .execute().fetchall() instead of just .execute(). Anyway, using fetchall() will give you the rows as a list, which you can then hand off to paginate. However, since you are using pagination, it does not make sense to fetch all the rows anyway: just let paginate modify the query to fetch the correct subset of rows. via ORM: remove the .all(): query = Session.query(SmartdataEligibilityRecord) query = query.join(Member) query = query.filter(Member.id==SmartdataEligibilityRecord.member_id) query = query.order_by(Member.last_name.asc()) via SQL layer (assuming you are using declarative): query = select([SmartdataEligibilityRecord.__table__, Member.__table__]) query = query.where(Member.id == SmartdataEligibilityRecord.member_id) query = query.order_by(Member.last_name.asc()) Note how the above does not use .all() or .execute(), because we will let paginate take care of that. When using paginate, the overhead of ORM should be negligible, so I recommend staying with the ORM in this case. -Conor -- 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.
