Speed up App Engine local SDK DB query when multiple order properties present?

Question

I have a query that looks like this:

query = (models.Foo.all()
  .filter('x =', x)
  .filter('y =', y)
  .filter('z =', z)
  .filter('zz =', zz)
  .order('-a'))

It runs on the local SDK in ~100ms, and runs in cloud at acceptable speeds. When I add a second order (so it looks like this:)

query = (models.Foo.all()
  .filter('x =', x)
  .filter('y =', y)
  .filter('z =', z)
  .filter('zz =', zz)
  .order('-a')
  .order('-b'))

..it takes ~10s (100x longer) on the local SDK, and runs at the same speed as before in the cloud. I need to have the second order property.

A few details about the setup:

• Windows SDK version 1.9.50
• Python 2.7
• Using the db model, not ndb
• I have started with a fresh local database (replaced the datastore.db) and rebuilt the records from scratch
• There are ~1200 Foo entities locally (~3M in the cloud)
• I ran sqlite3 datastore.db "PRAGMA integrity_check on the local db and no errors were reported

Question: how can I make the query run quicker locally? (It's really difficult to do development with the 10s lag there the whole time.)

Answer 1

This may not be the answer you want, but slow datastore performance on the development server is a long standing known issue which is being tracked on the public issue tracker.

One reason why this is the case is partially due to how emulation is handled. If you take a look at google/appengine/datastore/datastore_sqlite_stub.py in the SDK, you can see that calls to db methods are somewhat naively translated into basic SQL queries which are fed into the locally running SQLite database.

There's not much you can do at the application level to improve performance in these cases. The solution is to have the SDK perform smarter translation of queries on the development server, which is on the SDK engineering team to implement.