how to optimize this code? I want to fix the DeadlineExceededError issue

Question

I want to extract data from datastore. I am relatively new to programming, so printing out results on a page seems easiest for me. Unfortunately, most of the time (not always!), I get the following error. Could you please help me out?

Error message:

500 Server Error The server encountered an error and could not complete your request.

Logs:

http://pastebin.com/C2LQCArn

DeadlineExceededError

My understanding is that my query is too slow: https://cloud.google.com/appengine/articles/deadlineexceedederrors

How do I speed it up?

class ExpData(db.Model):

        # According to Datastore Admin, there are 20,557 entities in this model.

        created = db.DateTimeProperty(auto_now_add = True)
        username = db.StringProperty(required=True)

        task1n = db.StringProperty(required=False, indexed=False)
        task1a = db.StringProperty(required=False, indexed=False)
        task1c = db.StringProperty(required=False, indexed=False)

        task2n = db.StringProperty(required=False, indexed=False)
        task2a = db.StringProperty(required=False, indexed=False)
        task2c = db.StringProperty(required=False, indexed=False)

        task3n = db.StringProperty(required=False, indexed=False)
        task3a = db.StringProperty(required=False, indexed=False)
        task3c = db.StringProperty(required=False, indexed=False)

        task4n = db.StringProperty(required=False, indexed=False)
        task4a = db.StringProperty(required=False, indexed=False)
        task4c = db.StringProperty(required=False, indexed=False)

        q1 = db.StringProperty(required=False)
        q2 = db.StringProperty(required=False)

        ecus = db.StringProperty(required=False)


    class Viewer(Handler):
        def get(self):

            test = ExpData.all().order('-created')
            expDataDict = {}
            expDataList = []
            for entity in test:

                if (entity.q2 == "-1") or (entity.q2 == ""):
                    continue
                else:
                    expDataList.append(entity.created)
                    expDataList.append(entity.username)
                    expDataList.append(entity.ecus)
                    expDataList.append(entity.q1)
                    expDataList.append(entity.q2)
                    expDataList.append(entity.task1c)
                    expDataList.append(entity.task2c)
                    expDataList.append(entity.task3c)
                    expDataList.append(entity.task4c)
                    expDataDict[entity.created] = {}
                    expDataDict[entity.created]["username"] = entity.username
                    expDataDict[entity.created]["ecus"] = entity.ecus
                    expDataDict[entity.created]["q1"] = entity.q1
                    expDataDict[entity.created]["q2"] = entity.q2
                    expDataDict[entity.created]["task1c"] = entity.task1c
                    expDataDict[entity.created]["task2c"] = entity.task2c
                    expDataDict[entity.created]["task3c"] = entity.task3c
                    expDataDict[entity.created]["task4c"] = entity.task4c

            self.render("viewer.html", otherDict = expDataDict)

Many thanks for help.

Answer 1

Make use of deferred. https://cloud.google.com/appengine/articles/deferred

And from your deferred task create the extract and send it to you as an email.

For deferred tasks the request limit is 10 min. If you extract completes within 10 minutes , you are good.

Pseudocode :

Create a module extract_task.py for the background task.

''' extract_task.py '''

from StringIO import StringIO
from google.appengine.api import mail

def extract():
    output = StringIO()
    test = ExpData.all().order('-created')
    for entity in test:
        # Just extracting 3 fileds per row for the example.
        output.write('%s,%s,%s\n' % (entity.created, entity.username, entity.ecus))

    mail.send_mail(sender="[email protected]",
                   to="Albert Johnson <[email protected]>",
                   subject="Extract",
                   body="""
Extract attached.
""",
                  attachments=[('extract.csv', output)])

Write up a new handler:

from extract_task import extract # import the module cretaed above.
from google.appengine.ext import deferred

class Viewer1(Handler):
    def get(self):
        deferred.defer(extract)
        self.response.write('Exatrcat will be emailed to you soon.')

app.yaml

add the following entry to the builtins section of your app.yaml:

- deferred: on

And the following entry to the handlers section of the same file:

- url: /_ah/queue/deferred
    script: google.appengine.ext.deferred.deferred.application
    login: admin

Answer 2

First, try to add these conditions to your query instead of running them after retrieving the result (entity.q2 == "-1") or (entity.q2 == ""). Second, you should look into pagination of the results in order to view that much data. You should look into Query Cursors instead

from google.appengine.api import memcache
from google.appengine.ext import db

# class Person(db.Model): ...

# Start a query for all Person entities
people = Person.all()

# If the application stored a cursor during a previous request, use it
person_cursor = memcache.get('person_cursor')
if person_cursor:
  people.with_cursor(start_cursor=person_cursor)

# Iterate over the results
for person in people:
  # Do something

# Get updated cursor and store it for next time
person_cursor = people.cursor()
memcache.set('person_cursor', person_cursor)