On 03/15/2012 10:26 AM, Conor wrote:
> 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
>
Follow-up: if somequery contains joinedloads, then using
func.count().over() will not give you the same result as
somequery.count(). You should be able to get around this by using
func.count(SomeClass.id.distinct()).over() instead.
-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.
