GAE Datastore performance / potential index issue

52 views
Skip to first unread message

Karoly Kantor

unread,
Apr 22, 2017, 3:10:23 AM4/22/17
to web...@googlegroups.com
My queries on GAE Datastore are very slow. So I decided to try to fetch everything into memory before further processing, to no avail.

For example, the following runs for 8 seconds for only 3000 items, which is very bad because my app might have much much  more than that eventually.

        query = (db.record_text.entity == entity.id)
        result = db(query).select(db.record_text.ALL)
        values['record_text'] = {}
        for r in result:
            values['record_text'][str(r.record)] = {}
            values['record_text'][str(r.record)][str(r.field)] = r.value

I am suspecting maybe there is a problem with my indexes.

I tried to see the actual query by "print db._lastsql" but it returns None, maybe this is a GAE thing?. :-(

Questions: 

1. When I do the query as described above, what fields is web2py really querying on, i.e. what indexes should I have?
2. I there maybe some index required due to auth.signature being ON?

Or maybe the problem is not related to index, any other suggestion would be welcome.


Karoly Kantor

unread,
Apr 24, 2017, 3:51:32 AM4/24/17
to web...@googlegroups.com
Sorry for commenting on my own issue, but i have more information:

The following query takes 20 seconds for only 800 items:

            query=(
                (mydb[record].application == application_id) &
                (mydb[record].entity == entity_id) &
                (mydb[record].is_deleted == False))
            
            result = mydb(query).select()

I don't think this is normal. I really would like to know what web2py might be really doing in terms of raw GAE query, but unfortunately _lastsql still does not give any info.

Any idea / insight would be greatly appreciated!

The index seems to exist and serve in GAE Datastore as follows:

 record application 
 
    
entity 
 
    
is_deleted 


Karoly Kantor

unread,
Apr 24, 2017, 9:15:59 AM4/24/17
to web2py-users
More info again, still hoping someone can help:

I tried to replace the DAL query with native app engine code.

Original DAL:

            query=(
                (mydb[record].application == application_id) &
                (mydb[record].entity == entity_id) &
                (mydb[record].is_deleted == False))
            
            result = mydb(query).select()

Native:

 from google.appengine.ext import ndb
            class record(ndb.Expando):
                pass
            result = record.query().filter(ndb.GenericProperty('application') == application_id, ndb.GenericProperty('entity') == entity.id, ndb.GenericProperty('is_deleted') == False)
 
I was hoping this will speed things up, but it did not. It was the same slowness on the local dev env. On the server, the situation became even worse:

"While handling this request, the process that handled this request was found to be using too much memory and was terminated. "

Anyway, I still find it unreal that a query of 800 rows can last more than 20 seconds. 

Ideas are continue to be most welcome.
  

Áureo Dias Neto

unread,
Apr 24, 2017, 9:18:26 AM4/24/17
to web...@googlegroups.com
Hello,

i have the same problem with sqlite/mysql.. when i fetch some 1000 row, with one column only, its takes about 15/20 seconds to load a page..

dlypka

unread,
May 4, 2017, 12:00:30 PM5/4/17
to web2py-users
Reread the GAE Query documentation.
Your use case is not the use case that GAE is optimized for.
Fetch fewer records per call.
Example:'=

web2pyChildTableClass = db.ChildTable # gives the web2py Table class

theNativeGAEChildTableClass = web2pyChildTableClass._tableobj # magic... into native GAE handle <== This may have changed recently. Somehow get the native handle.

nativeChildTableQuery = theNativeGAEChildTableClass.all()

nativeChildTableQuery.filter('id =', 25)

nativeresults = nativeChildTableQuery.fetch(limit=10)  # Just get a few record at a time, using the 'limit' parameter.

myGAENativeChild = nativeresults[0]

Reply all
Reply to author
Forward
0 new messages