SQL is single-handedly the most popular relational database standard and is used by hundreds of thousands of companies around the world. ESS (External SQL Data Source) allows users to connect an existing SQL database to a FileMaker file as an external data source. This is a great way to grant users access to many of the functionalities of a standard FileMaker table while also allowing them to continue using the SQL database as they were.

Why Use ESS?

Since ESS allows for the integration of an existing SQL database, this feature is primarily useful for situations where the user already has an extensive SQL database and those where migrating data from an SQL database is not feasible. Using ESS as an alternative to natively storing data in FileMaker may not be recommended as it tends to be less optimized and more limited than standard FileMaker databases. It's a sacrifice of speed for convenience.

How To Set Up ESS in Your FileMaker Database

The first step to setting up ESS is to ensure your data source is supported by your version of FileMaker. Refer to this list to get that information. Here is an example of that chart for Claris FileMaker 2024:

Once you have determined your data source is supported, you will want to install the supported ODBC driver for that data source. Refer to the same chart to find out what is compatible. Once you have installed and configured the ODBC driver, you will add the ODBC DSN as an external data source in FileMaker. Claris' guide to setting up an ESS data source can be found here.

Creating a new TO in the relationships tab in the database manager using the new data source will create a shadow table that is linked to the SQL table of your choice. This table behaves very similarly to standard FileMaker tables and you can add, edit, and delete records in a layout using it. Here is an example of an ESS shadow table in the database manager:

Limitations of ESS and How To Make the Best of It

One of the main limitations of the ESS shadow table is the lack of capability to alter table schema. Since the table is an external source, you are only able to add summary fields and unstored calculations since they don't reflect on the source database. Additionally, the more data is present in the shadow tables, the slower the operations and load times become on the file.

Here are some ways to improve performance for ESS tables:

• Preload the table data in the startup script: In each new user session, every time a user enters an ESS shadow table layout for the first time, it downloads all the records from the corresponding SQL table. Adding script steps to the startup script that navigate to each ESS layout confines the resulting load times to the beginning of the user session.

• Avoid wildcard SQL queries: Performing finds on ESS shadow table layouts is very unoptimized as any non-exact match find runs a wildcard query on the SQL database. To avoid this, use "==" somewhere in your find to prevent the automatic wildcard. In the case of performing an "OR" find, all parts of the search need the "==" exact match.

• Reduce the complexity of layout sorts: Sorting by fields on ESS shadow table layouts is another very slow functionality. To reduce the performance hit, limit the amount of unstored calculations in the table and limit your sorts to a maximum of 2-3 fields. Any more than that runs the risk of causing a long load time when the sort happens. If there are a lot of records in the table, make sure the sort happens after you reach your desired found set to minimize the number of records sorted through.

• Enter find mode before switching layouts: This prevents the layout from needing to load all of the records from the ESS shadow table, which is especially important for list and report layouts.

• Consider performing heavier scripts on the server: If a script is likely to use a lot of processing power, it can be faster to run it on the server. This is only the case if the server has more processing power than the client machine. Keep in mind that this can have major performance impacts on the server if the script is called frequently in a short time span.

How to Find Performance Outliers

The best way to determine which processes are running slower than normal is to examine the server logs, specifically the stats.log and topcallstats.log files. Before opening the log file, it will need to be converted into the FileMaker readable format.

1. Download your log files from the "Logs" section of the FileMaker Server Admin Console.

2. Open the log file with FileMaker Pro.

3. After choosing the name and location of the converted file, you should see a convert file screen.

4. Make sure to use the first record as field names.

5. To sort these fields properly, you will want to change the data types of all the numeric fields to numbers.

Here is what the converted log file should look like:

The main keys to look for are:

• Total Elapsed Time: The total amount of time it took to complete a process (including wait time).

• Wait Time: Amount of time the process had to wait before starting (often caused by processes maxing out CPU resources).

• Operation: Used to determine which process is running slower than expected.

Sort descending first by total elapsed time to find the processes that took the longest. Then, sort descending by wait time to determine if any processes max out the processing power of the CPU.

• If wait times are high for several processes, it is worth checking whether the server's hardware specs are high enough.

• If the total elapsed time is high but the wait times are generally low, ESS may be slowing down the processes.

Conclusion

For users who already have a robust SQL database, ESS is a valuable tool that allows for nearly complete integration with a FileMaker system. If the correct steps are taken to optimize FileMaker processes, ESS can be implemented into an existing project easily with no change to the database structure. If you want assistance integrating ESS with your FileMaker solution, contact us at DB Services, and we will be happy to help.