2

I am fetching multiple entities 100+ from datastore using the below Query

return entity.query(ancestor = ancestorKey).filter(entity.year= myStartYear).order(entity.num).fetch()

Which was taking a long time (order of a few seconds) to load.

Trying to find an optimum way, I created exactly 100 entities, found that it takes anywhere between 750ms ~ 1000ms to fetch the 100 entities on local server, which is a lot of course. I am not sure how to get around a single line fetch to make it more efficient!

In a desperate attempt to optimize, I tried

  1. Removing the order part, still got the same results
  2. Removing the filter part, still got the same results
  3. Removing the order & filter part, still got the same results

So apparently it is something else. In a desperate attempt, I tried fetching for keys only then passing the keys to ndb.get_multi() function:

qKeys = entity.query(ancestor = ancestorKey).filter(entity.year= myStartYear).order(entity.num).fetch(keys_only=True)

return ndb.get_multi(qKeys)

To my surprise I get a better throughput! query results now loads in 450 ~ 550ms which is around ~40% better performance on average!

I am not sure why this happens, I would have thought that the fetch function already queries entities in the most optimum time.

Question: Any idea how I can optimize the single query line to load faster?

Side Question: Anyone knows what's the underlying mechanism for the fetch function, and why fetching keys only, then using ndb.get_multi() is faster?

Khaled
  • 907
  • 1
  • 8
  • 18
  • Getting keys first and then the data from the keys can benefit from ndb's ability to cache entities. Check if your improvement % is reproducible, if it's not ndb caching could be a possible explanation. Try maybe wiping your app's memcache before the test. – Dan Cornilescu Nov 13 '17 at 19:09
  • Another thought: ancestor queries are strongly consistent, meaning that mechanisms to ensure the data returned is always consistent need to be engaged for as long as the data for all the entities is collected and returned. For the keys_only search that's a lot shorter time (the get_multi op is not part of the same transactional operation). Donno if this can account for the performance difference, tho. Try to compare with a non-ancestor query returning the same number and size of results. – Dan Cornilescu Nov 13 '17 at 19:40
  • 2
    Be aware that performance characteristics in the cloud may differ from those on your local machine. You really want to be running these tests in the cloud. – snakecharmerb Nov 13 '17 at 19:43
  • @DanCornilescu for you strong consistency note, do you mean that get_multi does not follow strong consistency? I believe I read somewhere in the docs that getting entities by their keys is always strongly consistent, no? I'll try wiping the memcache and post the results – Khaled Nov 13 '17 at 21:08
  • @snakecharmerb usually on the cloud the response is faster, but you are right, testing where it should run is best, will post the results soon – Khaled Nov 13 '17 at 21:09
  • @DanCornilescu So: wiped memcache, tried with fetch(), got results in around 800ms ~ 1100ms (higher than before wiping memcache). Then wiped cache and tried with fetch(keysonly) then get_multi, first time was high ~1480ms then subsequent refreshes dropped to ~450ms. Not sure what to make out of this, but it makes sense memcache was wiped in both cases. A note though, I believe NDB already takes care of caching data. – Khaled Nov 13 '17 at 21:20
  • @snakecharmerb you were right on your suggestion! Just tested on the cloud it's actually the other way around on the cloud in terms of performance. fetch() ~550ms, fetch(keysonly) then get_multi was ~700ms seems that fetch() works better on the cloud! – Khaled Nov 13 '17 at 23:47
  • 1
    @Khaled - no, I only meant that getting the keys from the query and getting the values from keys aren't tied together - entities can still be changed or even dissapear in between the 2 ops (that doesn't mean not strongly consistent), while when getting the values directly from the queries I don't think they can change (otherwise they query wouldn't be strongly consistent). – Dan Cornilescu Nov 14 '17 at 04:04
  • @snakecharmerb - That should be an answer IMHO. – Dan Cornilescu Nov 14 '17 at 04:05
  • Thanks DanCornilescu and snakecharmerb, now that we know metrics should not come from the local server but rather to test on the cloud, can we discuss the question of how to optimize the single query line to load faster? I'm open for any suggestion at the moment.. – Khaled Nov 15 '17 at 20:06

1 Answers1

2

FWIW, you shouldn't expect meaningful results from datastore performance tests performed locally, using either the development server or the datastore emulator - they're just emulators, they don't have the same performance (or even the 100% equivalent functionality) as the real datastore.

Credit goes to @snakecharmerb, who correctly identified the culprit, confirmed by OP:

Be aware that performance characteristics in the cloud may differ from those on your local machine. You really want to be running these tests in the cloud. – snakecharmerb yesterday

@snakecharmerb you were right on your suggestion! Just tested on the cloud it's actually the other way around on the cloud in terms of performance. fetch() ~550ms, fetch(keysonly) then get_multi was ~700ms seems that fetch() works better on the cloud! – Khaled yesterday

Dan Cornilescu
  • 39,470
  • 12
  • 57
  • 97