Skip to content

Slow Firestore query on large collections #18570

Closed
Closed
Slow Firestore query on large collections#18570
Labels
c: performanceRelates to speed or footprint issues (see "perf:" labels)p: cloud_firestoreObsolete - Firebase Firestore pluginp: firebaseObsolete - Firebase pluginspackageflutter/packages repository. See also p: labels.

Description

@uehsnap
uehsnap
opened on Jun 18, 2018

A simple query like

firestore.collection('small_collection').limit(1).getDocuments()

on a relatively small collection is almost "instantaneous". However, the same query on a collection with more than 100k documents takes more than 10-20s.
I think this is a cloud_firestore plugin issue because this query running on Python on the same large collection is also "instantaneous".

Activity

  1. zoechi commented on Jun 18, 2018

    @zoechi
    zoechi
    on Jun 18, 2018
    Contributor

    Please add the output of flutter doctor and the cloud_firestore version you are using.

    @collinjackson

  2. zoechi
    added
    p: firebaseObsolete - Firebase plugins
    p: first party
    on Jun 18, 2018

  3. uehsnap commented on Jun 18, 2018

    @uehsnap
    uehsnap
    on Jun 18, 2018
    Author

    I'm using cloud_firestore 0.7.3 and below is the output of flutter doctor

    Doctor summary (to see all details, run flutter doctor -v):
[✓] Flutter (Channel beta, v0.4.4, on Mac OS X 10.13.5 17F77, locale en-BR)
[✓] Android toolchain - develop for Android devices (Android SDK 27.0.3)
[✓] iOS toolchain - develop for iOS devices (Xcode 9.4)
[✓] Android Studio (version 3.1)
[✓] Connected devices (1 available)

• No issues found!
Process finished with exit code 0

    Also, when I run the query on a small collection there's no output on Android Studio's console. However, when running on the large collections there're dozens of outputs like these:

    W/CursorWindow( 6915): Window is full: requested allocation 2355 bytes, free space 1925 bytes, window size 2097152 bytes
I/art     ( 6915): Background partial concurrent mark sweep GC freed 5351226(159MB) AllocSpace objects, 0(0B) LOS objects, 35% free, 28MB/44MB, paused 1.453ms total 198.925ms

    Thank you for your help.

  4. EdHuamani commented on Jun 22, 2018

    @EdHuamani
    EdHuamani
    on Jun 22, 2018

    I'm seeing the same problem, with a 10k collection.

    flutter doctor
Doctor summary (to see all details, run flutter doctor -v):
[✓] Flutter (Channel beta, v0.5.1, on Linux, locale en_US.UTF-8)
[✓] Android toolchain - develop for Android devices (Android SDK 27.0.3)
[✓] Android Studio (version 3.1)
[!] VS Code (version 1.24.1)
[✓] Connected devices (1 available)

! Doctor found issues in 1 category.

  5. BerndWessels commented on Aug 19, 2018

    @BerndWessels
    BerndWessels
    on Aug 19, 2018

    I'm seeing this too

    W/CursorWindow(21291): Window is full: requested allocation 253420 bytes, free space 67821 bytes, window size 2097152 bytes
W/CursorWindow(21291): Window is full: requested allocation 253420 bytes, free space 68329 bytes, window size 2097152 bytes
I/zygote64(21291): Background concurrent copying GC freed 535155(13MB) AllocSpace objects, 5(1240KB) LOS objects, 50% free, 17MB/35MB, paused 60us total 102.836ms
I/zygote64(21291): Background concurrent copying GC freed 586382(13MB) AllocSpace objects, 9(1508KB) LOS objects, 49% free, 24MB/48MB, paused 102us total 212.880ms

    And I do only have a few dozens of documents returned from the request.

    This also seems to put huge load on the main thread and the navigation stutters (at least in debug, haven't build the release version yet).

    Any ideas?

    Doctor summary (to see all details, run flutter doctor -v):
[√] Flutter (Channel dev, v0.5.8, on Microsoft Windows [Version 10.0.17134.228], locale en-NZ)
[√] Android toolchain - develop for Android devices (Android SDK 27.0.3)
[√] Android Studio (version 3.1)
[√] IntelliJ IDEA Community Edition (version 2017.3)
[√] Connected devices (1 available)

• No issues found!

  6. aureliopetrone commented on Sep 8, 2018

    @aureliopetrone
    aureliopetrone
    on Sep 8, 2018

    @BerndWessels it's the same for me. I've a collection of 5k documents and it takes 10s to load.

    Is there any workaround?

  7. zoechi
    added
    c: performanceRelates to speed or footprint issues (see "perf:" labels)
    on Sep 10, 2018
  8. zoechi
    added this to the milestone on Sep 10, 2018

  9. LEXschmidt commented on Oct 3, 2018

    @LEXschmidt
    LEXschmidt
    on Oct 3, 2018

    I'm seeing the same issue.

    Doctor summary (to see all details, run flutter doctor -v):
[√] Flutter (Channel dev, v0.9.5, on Microsoft Windows [Version 10.0.17134.285], locale de-DE)
[√] Android toolchain - develop for Android devices (Android SDK 27.0.3)
[√] Android Studio (version 3.2)
[√] VS Code, 64-bit edition (version 1.27.2)
[√] Connected device (1 available)

• No issues found!

    My collection has about 3000 documents. The size of the whole collection exported as JSON file is about 2 MB, so it's not that big. It takes about 60s to load.

    W/CursorWindow(13376): Window is full: requested allocation 24 bytes, free space 16 bytes, window size 2097152 bytes
I/zygote  (13376): Background concurrent copying GC freed 46209(1525KB) AllocSpace objects, 0(0B) LOS objects, 50% free, 2MB/5MB, paused 4.151ms total 712.877ms
I/zygote  (13376): Do full code cache collection, code=122KB, data=73KB
I/zygote  (13376): After code cache collection, code=110KB, data=59KB
I/zygote  (13376): Background concurrent copying GC freed 77872(2MB) AllocSpace objects, 0(0B) LOS objects, 49% free, 3MB/7MB, paused 55.588ms total 1.023s
I/zygote  (13376): Do partial code cache collection, code=121KB, data=68KB
I/zygote  (13376): After code cache collection, code=121KB, data=68KB
I/zygote  (13376): Increasing code cache capacity to 512KB
I/zygote  (13376): Background concurrent copying GC freed 96590(2MB) AllocSpace objects, 0(0B) LOS objects, 49% free, 5MB/10MB, paused 29.085ms total 1.044s
I/zygote  (13376): Background concurrent copying GC freed 158837(4MB) AllocSpace objects, 0(0B) LOS objects, 44% free, 7MB/13MB, paused 39.992ms total 919.234ms
I/zygote  (13376): Background concurrent copying GC freed 204354(6MB) AllocSpace objects, 0(0B) LOS objects, 33% free, 11MB/17MB, paused 18.317ms total 1.303s
I/zygote  (13376): Background concurrent copying GC freed 262954(8MB) AllocSpace objects, 1(16KB) LOS objects, 32% free, 12MB/18MB, paused 2.629ms total 1.302s
I/zygote  (13376): Background concurrent copying GC freed 215115(6MB) AllocSpace objects, 0(0B) LOS objects, 31% free, 13MB/19MB, paused 48.454ms total 2.243s
I/zygote  (13376): Background concurrent copying GC freed 232018(6MB) AllocSpace objects, 0(0B) LOS objects, 24% free, 18MB/24MB, paused 1.186ms total 155.298ms
I/zygote  (13376): Background concurrent copying GC freed 185677(4MB) AllocSpace objects, 4(256KB) LOS objects, 22% free, 21MB/27MB, paused 688us total 299.955ms
I/zygote  (13376): Background concurrent copying GC freed 76930(3MB) AllocSpace objects, 3(1804KB) LOS objects, 21% free, 22MB/28MB, paused 677us total 174.428ms
I/zygote  (13376): Do full code cache collection, code=242KB, data=131KB
I/zygote  (13376): After code cache collection, code=231KB, data=123KB
I/zygote  (13376): Background concurrent copying GC freed 70181(3MB) AllocSpace objects, 1(2MB) LOS objects, 19% free, 24MB/30MB, paused 565us total 365.649ms
I/zygote  (13376): Background concurrent copying GC freed 126822(7MB) AllocSpace objects, 0(0B) LOS objects, 17% free, 27MB/33MB, paused 3.779ms total 408.329ms
  10. zoechi
    added
    plugin
    on Oct 3, 2018

  11. kyleorin commented on Oct 14, 2018

    @kyleorin
    kyleorin
    on Oct 14, 2018

    Any fixes?

  12. xqwzts commented on Oct 25, 2018

    @xqwzts
    xqwzts
    on Oct 25, 2018
    Contributor

    Started seeing this as well on previously stable versions. Upgrading dependencies did not resolve it.

    A few notes of what I'm seeing:

    Web versions of the same query work fine.

    The collection being queried is large, but the query result only contains a single document.

    This behavior started appearing for no obvious reason, no packages were upgraded, the queries were not modified - but the collection did grow in size.

    Running in debug mode (with a simple DateTime.now() comparison for timing) to resolve a query that returns a single document.takes ~20 seconds on first run, and ~10 seconds on subsequent runs.

    Resolving the query to a Future (via .getDocuments()) or a Stream (via .snapshots()) makes no difference.

    Previous versions: Flutter 0.8.2 / cloud_firestore 0.7.3:

    [✓] Flutter (Channel beta, v0.8.2, on Linux, locale en_US.UTF-8)
    • Flutter version 0.8.2 at /home/vic/devel/flutter
    • Framework revision 5ab9e70727 (7 weeks ago), 2018-09-07 12:33:05 -0700
    • Engine revision 58a1894a1c
    • Dart version 2.1.0-dev.3.1.flutter-760a9690c2

    Upgraded to latest (beta): Flutter 0.9.4 / cloud_firestore 0.8.2+1

    [✓] Flutter (Channel beta, v0.9.4, on Linux, locale en_US.UTF-8)
    • Flutter version 0.9.4 at /home/vic/devel/flutter
    • Framework revision f37c235c32 (4 weeks ago), 2018-09-25 17:45:40 -0400
    • Engine revision 74625aed32
    • Dart version 2.1.0-dev.5.0.flutter-a2eb050044

[✓] Android toolchain - develop for Android devices (Android SDK 27.0.3)
    • Android SDK at /home/vic/Android/Sdk
    • Android NDK location not configured (optional; useful for native profiling support)
    • Platform android-27, build-tools 27.0.3
    • Java binary at: /usr/local/android-studio/jre/bin/java
    • Java version OpenJDK Runtime Environment (build 1.8.0_152-release-915-b01)
    • All Android licenses accepted.

  13. xqwzts commented on Oct 25, 2018

    @xqwzts
    xqwzts
    on Oct 25, 2018
    Contributor

    Resolved by disabling firestore persistence, it seems that Firestore persists by loading the whole database [just the collection being queried?] and then querying locally. So even a query with limit(1) would load all the data.

    My assumption is that the data has grown to such a point that it can no longer be held in memory, so loading it seems to thrash while the GC clears up space to continue and query [?!].

    Surprisingly this happens on every query, not just the very first, so I guess as long as the app is online, every query will download the whole collection 😕

    Disabling persistence results in sub-second loading:

      await Firestore.instance.settings(
    persistenceEnabled: false,
    timestampsInSnapshotsEnabled: true,
  )

  14. blaneyneil commented on Nov 4, 2018

    @blaneyneil
    blaneyneil
    on Nov 4, 2018

    can confirm that performance really degraded once a collection i was querying got to over 1k records. i'm querying about 12 snapshots concurrently:

        List<Future<QuerySnapshot>> _feeds = [];

    for (var i = 0; i < docs.length; i++) {

      String _id = docs[i].data['id'];

      _feeds.add(dbTable
        .where('id', isEqualTo: _id)
        .limit(1)
        .getDocuments()
      );
    }

   Stopwatch stopwatch = new Stopwatch()..start();

    Future.wait(_feeds, eagerError: false, cleanUp: (val) {
      print(val);
    }).then((result){
      print('took ${stopwatch.elapsed}');
    }).catchError(print);

    prior to the last several days, when dbTable only had 100-200 records, this always finished in less than a second (i wasn't using Stopwatch til now, so don't have an actual number). now, after batch inserting over 1k records, it takes 6-7 seconds to return. and the above query is stripped down - the real one had 2 more where clauses, an orderby, and a limit of 50.

    cloud_firestore: "^0.8.2+1"

    [✓] Flutter (Channel dev, v0.10.2, on Mac OS X 10.14 18A391, locale en-US)
    • Flutter version 0.10.2 at /Users/neil/flutter
    • Framework revision d8cbb80206 (10 days ago), 2018-10-26 01:30:21 -0400
    • Engine revision 6c2ade9fa2
    • Dart version 2.1.0-dev.8.0.flutter-bf26f760b1

[✓] Android toolchain - develop for Android devices (Android SDK 27.0.3)
    • Android SDK at /Users/neil/Library/Android/sdk
    • Android NDK location not configured (optional; useful for native profiling support)
    • Platform android-27, build-tools 27.0.3
    • Java binary at: /Applications/Android Studio.app/Contents/jre/jdk/Contents/Home/bin/java
    • Java version OpenJDK Runtime Environment (build 1.8.0_152-release-1136-b06)
    • All Android licenses accepted.

[✓] iOS toolchain - develop for iOS devices (Xcode 10.1)
    • Xcode at /Applications/Xcode.app/Contents/Developer
    • Xcode 10.1, Build version 10B61
    • ios-deploy 1.9.2
    • CocoaPods version 1.5.3

[✓] Android Studio (version 3.2)
    • Android Studio at /Applications/Android Studio.app/Contents
    • Flutter plugin version 29.1.1
    • Dart plugin version 181.5656
    • Java version OpenJDK Runtime Environment (build 1.8.0_152-release-1136-b06)

[✓] Connected device (1 available)
    • iPhone X      • 09913D8F-AAA2-4959-B2DA-06D3229A683A     • ios • iOS 12.1 (simulator)

• No issues found!

  15. 8 remaining items

  16. rave1502 commented on Feb 10, 2019

    @rave1502
    rave1502
    on Feb 10, 2019

    I am seeing the same behavior on Android libraries also.

    I have a data size of 50K records, if I query few records from it, it is very slow on mobile phone. Actually it if faster on my Android emulator than my real device.

  17. collinjackson commented on Feb 13, 2019

    @collinjackson
    collinjackson
    on Feb 13, 2019
    Contributor

    This seems to me to be an issue with the underlying iOS/Android Firebase SDK rather than Flutterfire. You could open up an issue against https://github.com/firebase/firebase-ios-sdk if the issue reproduces there.

  18. MichalMisiaszek commented on Apr 20, 2019

    @MichalMisiaszek
    MichalMisiaszek
    on Apr 20, 2019

    I have the same issue, for example .limit() is completely ignored. I read somewhere that full collection if downloaded to client and the query conditions applied (very inefficient). There is no way to disable persistenceEnabled for single query (only for full connection in plugin ?) as this method is depreciated.

  19. pschneider commented on Apr 27, 2019

    @pschneider
    pschneider
    on Apr 27, 2019

    This seems to me to be an issue with the underlying iOS/Android Firebase SDK rather than Flutterfire. You could open up an issue against https://github.com/firebase/firebase-ios-sdk if the issue reproduces there.

    This is definitely a problem of the SDKs as this also happens for non flutter users. There are multiple issues related (some of it is related to missing indexing of the local db) to this but it looks like there is no solution in sight right now.

    firebase/firebase-ios-sdk#1868

    We're seeing performance issues on 4k documents already.

  20. collinjackson commented on Apr 29, 2019

    @collinjackson
    collinjackson
    on Apr 29, 2019
    Contributor

    If this issue is showing up in the native SDKs then unfortunately I don't think there's much we can do in the cloud_firestore plugin. Closing the issue for now but happy to reopen if anyone has any ideas.

  21. collinjackson
    closed this as completedon Apr 29, 2019

  22. shasaf1986 commented on May 21, 2019

    @shasaf1986
    shasaf1986
    on May 21, 2019

    In the native SDKs there is a cache size option.
    I think it can solve it the issue, if we could set it via dart code ...
    https://firebase.google.com/docs/firestore/manage-data/enable-offline

  23. algrid commented on Jun 24, 2019

    @algrid
    algrid
    on Jun 24, 2019

    I'm having a similar problem with the native lib, I added an issue there: firebase/firebase-android-sdk#565

  24. kroikie commented on Oct 13, 2019

    @kroikie
    kroikie
    on Oct 13, 2019

    @uehsnap

    This issue has been moved to firebase/flutterfire#791. Any further collaboration will be done there.

  25. kroikie
    added
    transferred
    on Oct 13, 2019
  26. engine-flutter-autoroll
    added a commit that references this issue on May 26, 2020

    1a83498 Revert "Update CanvasKit to 0.15.0 (flutter#18570)" (flutter-…

    475d857
  27. liyuqian
    added a commit that references this issue on May 26, 2020

    Roll Engine from 9ce1e5c to 1a83498 (69 revisions)

    0516672

  28. github-actions commented on Aug 17, 2021

    @github-actions
    github-actionsbot
    on Aug 17, 2021

    This thread has been automatically locked since there has not been any recent activity after it was closed. If you are still experiencing a similar issue, please open a new bug, including the output of flutter doctor -v and a minimal reproduction of the issue.

  29. github-actions
    locked as resolved and limited conversation to collaborators on Aug 17, 2021
  30. flutter-triage-bot
    added
    packageflutter/packages repository. See also p: labels.
    on Jul 5, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    c: performanceRelates to speed or footprint issues (see "perf:" labels)p: cloud_firestoreObsolete - Firebase Firestore pluginp: firebaseObsolete - Firebase pluginspackageflutter/packages repository. See also p: labels.

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions