Make query more efficient or move to async task

[furf]

We urgently need a performance upgrade to the following endpoint (r/states) that calculates funds raised in each state. It currently times out. We would like a fix to be ready in advance of a press release which will go out on Wednesday.

https://github.com/Lessig2016/pledgeservice/blob/master/backend/handlers.py#L751-L774

[tannewt]

How many requests do you get to that endpoint? It may be worth updating the totals every pledge rather than every request to that page. If it only gets a few hits then you could increase the timeout. Or you could also cache the page entirely.

[furf]

Both are good suggestions, however the query as it stands is inefficient and will not even run the first (pre-cached) time. memcache, as implemented, will probably be an adequate caching solution, but first we need to optimize the call so that it will complete its execution.

[brafkind]

This should help #16

[brafkind]

Doing an in-memory join from two huge lists (users and pledges) is hugely inefficient. Much better would be to join these data sets in the db. Unfortunately, the DB Datastore API lacks join support. Would we be able to use Google Cloud SQL instead which would support db joins?

[aaronlifshin]

This is High Replication Datastore, not DB. It cannot be queried this way
in a web request.

This needs to run in a queue, possibly paginated, write the results out
every 10 minutes or so.

A

On Mon, Sep 14, 2015 at 9:19 PM, brafkind notifications@github.com wrote:

Doing and in-memory join from two huge lists (users and pledges) is hugely
inefficient. Much better would be to do join these data sets in the db.
Unfortunately, the DB Datastore API
https://cloud.google.com/appengine/docs/python/datastore/ lacks join
support. Would we be able to use Google Cloud SQL
https://cloud.google.com/appengine/docs/python/cloud-sql/ instead which
would support db joins?

—
Reply to this email directly or view it on GitHub
#15 (comment)
.

[brafkind]

Maybe we should rethink either the choice of an HRD DB or the choice of splitting the state info from the pledge model since having to join them using a queue is a huge pain. Unfortunately, switching to a relational db or combining all the state info with the pledges would probably be hard to do by Wed, but perhaps one or the other could be a longer term goal.

[aaronlifshin]

The same query, when running in a server-side process, will have a lot
longer to run. Look up gae timeouts.

A

On Mon, Sep 14, 2015 at 9:32 PM, brafkind notifications@github.com wrote:

Maybe we should rethink either the choice of an HRD DB or the choice of
splitting the state info from the pledge model since having to join them
using a queue is a huge pain. Unfortunately, switching to a relational db
or combining all the state info with the pledges would probably be hard to
do by Wed, but perhaps one or the other could be a longer term goal.

—
Reply to this email directly or view it on GitHub
#15 (comment)
.

[brafkind]

Aaron, by "same query" do you mean as a MySQL (relational) query? If that's a concern, then how about at least de-normalizing the pledges and state info so that it's no longer necessary to join with the users?

[tannewt]

Why isn't my suggestion of replacing "model.User.all()" with
"db.Query(model.User,
projection=('state', ))" enough for Wednesday? It'll limit the user data
loaded to just what's used rather than all user fields. I bet the speedup
will be enough in the short term.
On Mon, Sep 14, 2015 at 6:37 PM brafkind notifications@github.com wrote:

Aaron, by "same query" do you mean as a MySQL (relational) query? If
that's a concern, then how about at least de-normalizing the pledges and
state info so that it's no longer necessary to join with the users?

—
Reply to this email directly or view it on GitHub
#15 (comment)
.

[brafkind]

I think you'd need to project with both the email and state fields so that
we can still filter the pledges with each user's email.

On Mon, Sep 14, 2015 at 9:49 PM, Scott Shawcroft notifications@github.com
wrote:

Why isn't my suggestion of replacing "model.User.all()" with
"db.Query(model.User,
projection=('state', ))" enough for Wednesday? It'll limit the user data
loaded to just what's used rather than all user fields. I bet the speedup
will be enough in the short term.
On Mon, Sep 14, 2015 at 6:37 PM brafkind notifications@github.com wrote:

Aaron, by "same query" do you mean as a MySQL (relational) query? If
that's a concern, then how about at least de-normalizing the pledges and
state info so that it's no longer necessary to join with the users?

—
Reply to this email directly or view it on GitHub
<
#15 (comment)

.

—
Reply to this email directly or view it on GitHub
#15 (comment)
.

[tannewt]

Ah maybe. I'd have to read the docs. You don't need the email server side
though.
On Mon, Sep 14, 2015 at 6:51 PM brafkind notifications@github.com wrote:

I think you'd need to project with both the email and state fields so that
we can still filter the pledges with each user's email.

On Mon, Sep 14, 2015 at 9:49 PM, Scott Shawcroft <notifications@github.com

wrote:

Why isn't my suggestion of replacing "model.User.all()" with
"db.Query(model.User,
projection=('state', ))" enough for Wednesday? It'll limit the user data
loaded to just what's used rather than all user fields. I bet the speedup
will be enough in the short term.
On Mon, Sep 14, 2015 at 6:37 PM brafkind notifications@github.com
wrote:

Aaron, by "same query" do you mean as a MySQL (relational) query? If
that's a concern, then how about at least de-normalizing the pledges
and
state info so that it's no longer necessary to join with the users?

—
Reply to this email directly or view it on GitHub
<

#15 (comment)

.

—
Reply to this email directly or view it on GitHub
<
#15 (comment)

.

—
Reply to this email directly or view it on GitHub
#15 (comment)
.

[brafkind]

The email address is the foreign key from the pledge to the user. How else
would you know which pledges goes with which states?

On Mon, Sep 14, 2015 at 10:02 PM, Scott Shawcroft notifications@github.com
wrote:

Ah maybe. I'd have to read the docs. You don't need the email server side
though.
On Mon, Sep 14, 2015 at 6:51 PM brafkind notifications@github.com wrote:

I think you'd need to project with both the email and state fields so
that
we can still filter the pledges with each user's email.

On Mon, Sep 14, 2015 at 9:49 PM, Scott Shawcroft <
notifications@github.com

wrote:

Why isn't my suggestion of replacing "model.User.all()" with
"db.Query(model.User,
projection=('state', ))" enough for Wednesday? It'll limit the user
data
loaded to just what's used rather than all user fields. I bet the
speedup
will be enough in the short term.
On Mon, Sep 14, 2015 at 6:37 PM brafkind notifications@github.com
wrote:

Aaron, by "same query" do you mean as a MySQL (relational) query? If
that's a concern, then how about at least de-normalizing the pledges
and
state info so that it's no longer necessary to join with the users?

—
Reply to this email directly or view it on GitHub
<

#15 (comment)

.

—
Reply to this email directly or view it on GitHub
<

#15 (comment)

.

—
Reply to this email directly or view it on GitHub
<
#15 (comment)

.

—
Reply to this email directly or view it on GitHub
#15 (comment)
.

[brafkind]

By the way, we'd need an index to be created with whichever combo of fields
we want to project per the section Indexes for projections
https://cloud.google.com/appengine/docs/python/datastore/projectionqueries#Python_Indexes_for_projections
which
states: Projection queries require all properties specified in the
projection to be included in a Datastore index
https://cloud.google.com/appengine/docs/python/datastore/indexes.

On Mon, Sep 14, 2015 at 10:25 PM, Barry Rafkind barry.rafkind@gmail.com
wrote:

The email address is the foreign key from the pledge to the user. How else
would you know which pledges goes with which states?

On Mon, Sep 14, 2015 at 10:02 PM, Scott Shawcroft <
notifications@github.com> wrote:

Ah maybe. I'd have to read the docs. You don't need the email server side
though.
On Mon, Sep 14, 2015 at 6:51 PM brafkind notifications@github.com
wrote:

I think you'd need to project with both the email and state fields so
that
we can still filter the pledges with each user's email.

On Mon, Sep 14, 2015 at 9:49 PM, Scott Shawcroft <
notifications@github.com

wrote:

Why isn't my suggestion of replacing "model.User.all()" with
"db.Query(model.User,
projection=('state', ))" enough for Wednesday? It'll limit the user
data
loaded to just what's used rather than all user fields. I bet the
speedup
will be enough in the short term.
On Mon, Sep 14, 2015 at 6:37 PM brafkind notifications@github.com
wrote:

Aaron, by "same query" do you mean as a MySQL (relational) query? If
that's a concern, then how about at least de-normalizing the pledges
and
state info so that it's no longer necessary to join with the users?

—
Reply to this email directly or view it on GitHub
<

#15 (comment)

.

—
Reply to this email directly or view it on GitHub
<

#15 (comment)

.

—
Reply to this email directly or view it on GitHub
<
#15 (comment)

.

—
Reply to this email directly or view it on GitHub
#15 (comment)
.

[brafkind]

In #17 , I propose to increase the cache timeframe from 10 min to 60 min

[aaronlifshin]

This would cause the query to fail 6 times less often. But I worry it will
still never actually get the results until its moved into a task queue.

A

On Mon, Sep 14, 2015 at 10:54 PM, brafkind notifications@github.com wrote:

In #17 #17 , I propose
to increase the cache timeframe from 10 min to 60 min

—
Reply to this email directly or view it on GitHub
#15 (comment)
.