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 [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.
