Forum Discussion

FieldDepartment's avatar
FieldDepartment
Qrew Member
4 years ago

Tables to Draw Formula Queries from: How Big is Too Big?

Hello,

I'm curious if anyone has a sense at what point a table is just too large for attempting to use a formula query on it. I'm currently trying a Size(GetRecords()) on a table with 3-4 fields in the query, and it seems like it just simply stopped working at over 40,000 records (and yes, this is after trying different iterations of which query comes first, in order to maximize performance). Does that match up with anyone else's experience?

------------------------------
Field Department
------------------------------
formula queries
  • MikeTamoush's avatar
    MikeTamoush
    Qrew Commander
    4 years ago
    I'm trying to find duplicates in a table using a similar query, and am getting 'Formula Query Execution Took too long".

    My table has 87k records so, perhaps formula queries only work on small tables?


    ------------------------------
    Mike Tamoush
    ------------------------------
    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      4 years ago
      I ran into that same issue.

      I think the problem is that the formula query needs to run 87,000 times against a record set of 87,000 records. So it has to touch  87,000×87,000 records which is touching 7 billion records.

      So the formula Queries can be incredibly useful but you kind of have to plan ahead because I imagine it's possible that a Formula Query would work initially for a new app but as the data  builds up it may stop working.

      ------------------------------
      Mark Shnier (YQC)
      mark.shnier@gmail.com
      ------------------------------
      • PaulPeterson1's avatar
        PaulPeterson1
        Qrew Assistant Captain
        4 years ago
        I ran into similar issues as well and may be partially to blame for the error messages.  I used a bit of code from the formula query workshop from Empower 2021 and when I tried to save the field with the query it took every app in my realm down for about 10 minutes.  Shortly after Quickbase closed my care case I noticed the error message appearing in apps where I was trying to use formula queries. Quickbase claimed if you try to use formula queries in a table that has several relations (cross app relationships in particular) that the query may use too much of the system's resources.

        ------------------------------
        Paul Peterson
        ------------------------------