App Engine Java advance query types

Question

I realize there are a lot of questions already on here about querying the App Engine Datastore, but I want to get opinions on my particular case.

I'm querying scores to build a high score table, so I want them ordered by score descending. Now, it is possible for two scores to have the same score value, so paging through based on this value is not possible, because you could end up skipping scores based on the page's filter.

I want to build the query around a dynamic date. For instance, the top 10 scores in the past month. What is the best way to go about this?

I obviously don't want to have to page through every score every time. Do I need to load these scores into the memcache and update when a submitted score is greater than one in the top 10? This doesn't make sense because I would have to define my time ranges ahead of time, but setting up a cron to refresh it every day to get the scores that have reached the time limit out would be doable. Can this be done efficiently dynamic? Another option would be to just have a cron run through all the scores every N hours or something, but I would really prefer to stay away from needing to do that.

Is there literature out there that would help me make these decisions?

Answer 1

So here is one solution that I got to work, but probably not the preferred way to do things.

In my Score class, I added a Long variable. When I set the date, I remove the hours, minutes, seconds, and milliseconds and do a getTime().

When the request happens for N days or N months worth of scores, I will generate however many days worth of equality checks on the query string: "dateAsLong == N days ago || dateAsLong == N - 1 days ago" etc etc etc.

This request works for up to 29 days into the past before I catch an "Illegal argument" when attempting to .execute(). I assume I'm hitting some sort of max on length of query or number of filters. Any longer would require the query to be split up, which is very doable. The lists can then be taken and appended.

The limitation of doing it this way is the time and CPU the request takes. When querying for the past 29 days, the request takes around 550ms and 750ms of CPU time.

Answer 2

Three options:

1. You can still paginate the 'naive' way - all queries have their key as an implicit final sort order, so you just need to include the key into your pagination.
2. Use the new query cursor support
3. Do what the authors of this library did, and build a tree of score entries.