Seeing slow query performance on a large array

[David Challoner]

I'm seeing very slow query performance on an array with only around ~30k objects.   Even just 10 recent records are taking close to 30 seconds to query which makes me suspect that it isn't hitting an index(not sure how to tell if this is the case). 

I have an index defined in the rules like so:

{

  "rules": {

    "users": {

      "$uid": {

        ".read": "$uid === auth.uid",

        ".write": "$uid === auth.uid",

         "timeseries": {

            ".indexOn": ["timestamp"]

        }

      }

    }

  }

}

schema is:

app/user/$uid/timeseries/$id/timestamp (see attached)

I'm querying like so:

     var getRealtimeData = function(lastSeconds=60){

       var uid=auth.$getAuth().uid;

       var ref =firebase.database().ref("/users/" + uid + "/timeseries");

       var start = Date.now() - (lastSeconds*1000);

       ref=ref.orderByChild("timestamp").startAt(start);

       return $firebaseArray(ref);

     }

Am I hitting the index?  What query times should I expect?   What if my array had 1M or 10M items?  I can think of a few ways to optimize i.e by pre-aggregating the data so I have fewer objects or bucketing it by day in separate paths but I'd like to make sure i'm at least getting expected performance.

Thanks,

David

[Tyler Rockwood]

David,

We recently released a profiler tool to help optimize these things: https://firebase.google.com/docs/database/admin/profile

The profiler will tell you how long things are taking and if you're missing any indexes. I encourage you to try to bucket your data in different ways and see the speed differences. We build your index on the fly so sometimes it can take a bit to build a large index from scratch.

-Tyler

[David Challoner]

So the tool says that the query is indexed (or rather not unindexed) but it also says i'm downloading 18 megabytes which shouldn't be the case if the data is being filtered server side.

The array I get back is indeed filtered but if I look at the websocket frames I see events from yesterday which suggests that the filtering in done client side?  Which would suggest that it isn't being indexed (or maybe, like you said, it's still building but it's been at least a few hours since I pushed the rule)

[Kato Richardson]

David, can you share a minimal repro, security rules, and some sample data? It doesn't necessarily need to be runnable by us, but we should just have a look at how you're running into this. Include debug logging enabled as follows:

JS:  firebase.database.enableLogging(true);

iOS:  [FIRDatabase setLoggingEnabled:YES];

Android:  FirebaseDatabase.getInstance().setLogLevel(Logger.Level.DEBUG);

Note that if you data isn't getting indexed properly on the server, you should see a warning in the client console indicating as much. But logs and code should tell us a bit more.

--
You received this message because you are subscribed to the Google Groups "Firebase Google Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebase-talk+unsubscribe@googlegroups.com.
To post to this group, send email to fireba...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/firebase-talk/d61a8021-b087-4ae3-a967-ea8c1e39039f%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Kato Richardson |

Developer Programs Eng |

kato...@google.com |

775-235-8398

[David Challoner]

So was related to how I was using angularfire.  Still it's clear i'll have to refactor this to get per-second time granularity as i'm seeing only 700 or so records per second which is too slow for my requirements.   The problem is that I need to do aggregate calculations which firebase doesn't support so I need to request all the raw data to do them on the client.   The serialization/transfer time for that much data is the culprit. 

You can close this out but please +1 adding aggregate query support to Firebase.  All the cool kids (mongoDB) do it.   I'll look into the whole ETL to bigquery thing.

Thanks,

David

To unsubscribe from this group and stop receiving emails from it, send an email to firebase-tal...@googlegroups.com.

To post to this group, send email to fireba...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/firebase-talk/d61a8021-b087-4ae3-a967-ea8c1e39039f%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.