On 03/15/2012 04:52 AM, Eduardo wrote:

Hi,
In order to avoid bottlenecks I am force to limit the number of returned results using LIMIT and OFFSET.
Since I am not returning all results upon a query I need to include the number of hits in the result.
somequery.count()
somequery.limit(n).offset(m).all()
The problem is that response time takes twice as long as for either the count query or the query retrieving results.
Is there any way to do this more efficiently, to make a query first, then to count results and return the result chunk defined with LIMIT and OFFSET?
What is the best practice for this?
Thanks
ED

If your DB supports window functions and you are using SQLAlchemy 0.7+, you can include func.count().over() in your query, which effectively includes somequery.count() as a column in each row.

So this:

somequery = session.query(SomeClass).filter(...) count = somequery.count() some_class_instances = somequery.limit(n).offset(m).all()

becomes:

rows = session.query(SomeClass, func.count().over().label("count")).filter(...).limit(n).offset(m).all() if rows: count = rows[0][1] some_class_instances = [row[0] for row in rows] else: # Either no rows matched or the limit+offset is out of range. We will assume the former. count = 0 some_class_instances = []

-Conor

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlalchemy <at> googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.