Скачать или смотреть Understanding Slow FileMaker Statistics Queries and How to Optimize Them

Learn why your FileMaker statistics queries may be slow and discover effective strategies to optimize performance for better efficiency.
---
This video is based on the question https://stackoverflow.com/q/68562544/ asked by the user 'Tom' ( https://stackoverflow.com/u/982364/ ) and on the answer https://stackoverflow.com/a/68563114/ provided by the user 'Josh Ormond' ( https://stackoverflow.com/u/11366013/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Statistics of all/many tables in FileMaker

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Understanding Slow FileMaker Statistics Queries and How to Optimize Them

When working with FileMaker solutions, many developers encounter performance issues, particularly when gathering statistics from multiple tables. For instance, if you find that querying your data takes an unexpectedly long time—like the reported 10 seconds for querying around 20,000 records across 10 tables—you may wonder what could be causing the delay and how to improve the situation.

In this post, we'll delve into the possible reasons for these slow statistics queries and provide actionable insights to help you optimize your FileMaker solution.

The Problem: Slow Query Performance

When executing queries in FileMaker, particularly using the ExecuteSQL function, the performance can vary significantly depending on several factors. Here’s an overview of why you might experience slow performance:

Database Schema Size and Complexity: A complex schema with a large number of records can slow down queries.

Unstored Calculations: If your statistics table uses unstored calculations, it can drastically affect performance since every recalculation requires evaluating all related records.

Relationships and Dependencies: Slow queries can often stem from complicated relationships between tables or large datasets being processed.

Analyzing the Solution

To tackle the performance issues, we’ll break down some strategies that can be implemented to improve your FileMaker queries.

1. Use Database Design Reports (DDR)

Instead of relying solely on your custom statistics table, consider generating a Database Design Report (DDR). Tools like FMPerception can provide insights about your schema and outputs without running extensive queries.

Generate DDR: Utilize the built-in functionalities in FileMaker to create a DDR. This can give you an overview of potential optimizations for your tables and their relationships.

2. Optimize Your Statistics Table

Use Unstored Calculations Judiciously

Limit Unstored Calculations: If your statistics table contains unstored calculations, consider how often these are being recalculated. Unstored calculations can be expensive in terms of performance, especially with large datasets.

3. Simplify Your Queries

Count Records Efficiently

Using a relationship-based approach can improve performance for counting records:

Instead of using Count that touches every record, create an unstored calc field in the target table. This allows you to get the record count through the relationship without fetching all records to the client.

Summarizing Records

Utilizing summary fields can be more effective than aggregate functions. A summary field in your target table can sum up records based specifically on a relationship without triggering heavy calculations.

4. Testing and Iteration

Effective query performance often requires testing various configurations to locate an optimal setup. Here are a few strategies:

Run Basic Queries: When testing your ExecuteSQL performance, start with simple queries that rely on indexed lookups.

Iterate Changes: Implement changes incrementally so you can assess their impact on performance.

5. Scheduled Script for Updates

If your calculations seem slow and recurring calculations cause delays:

Scheduled Scripts: Consider setting up scheduled scripts to update your statistics table periodically rather than relying on dynamic calculations that trigger on record access.

Conclusion

While slow statistics queries in FileMaker can be frustrating, understanding the underlying causes and their solutions can lead to significant performance improvements. Whether you are optimizing database design or improving the efficiency of your queries, taking systematic steps can help you streamline data process