CODEWITHSUNDEEP
pythongoogle-app-enginegoogle-cloud-datastoreapp-engine-ndb
stefanobaldo
stefanobaldo

Reputation: 2063

Get total rows when using limit/offset on Google DataStore

ListingStore.query().filter(ListingStore.account_id==1).fetch(10, offset=40)

Above is a query to return the 5th page of results from my dataset. My problem is that I have to return the total number of rows as well, like this:

{
    "data": ...,
    "limit": 10,
    "offset": 40,
    "total": 1235
}

I can use count() to get the total, like this:

ListingStore.query().filter(ListingStore.account_id==1).count()

But that seems to take as long as the fetch() to run, causing the whole thing take twice as long.

Is there a better way to do that?

Upvotes: 1

Views: 904

Answers (2)

Brent Washburne
Brent Washburne

Reputation: 13158

Unless you need the count of every account_id all the time, I would use The Memcache Pattern to run the count() when needed, and cache the results for subsequent page queries.

Then, as Alex mentions, delete the appropriate cache entry when the ListingStore changes.

Upvotes: 1

Alex Martelli
Alex Martelli

Reputation: 881685

.count() is more efficient than the equivalent query, but only by some constant factor (depending on how large each entity is -- minimally so compared to a keys-only query).

The only drastic performance acceleration you can get is by denormalizing your data model to "redundantly" keep track of how many ListingStore entities you have with each account_id.

For the purpose, you could just introduce a new entity ListingStoreByAccount with the account_id as the id in the key, so it can be fetched very fast, and an IntegerProperty w/the current count.

You'll need to keep the appropriate entity updated on each creation or deletion of a ListingStore entity, perhaps in a transaction (with a fake key-only "parent" per ListingStoreByAccount as the entity group) if concurrency is otherwise a problem.

Should counter contention become a problem, reach for a high-speed sharded counter instead, e.g per https://cloud.google.com/appengine/articles/sharding_counters and the example https://github.com/GoogleCloudPlatform/appengine-sharded-counters-python as example code.

Upvotes: 4

Related Questions