# SLOW BASIC SQL **URL:** \ **Category:** Questions\ **Created:** [April 4, 2025, 12:40pm UTC](https://the.fmsoup.org/t/slow-basic-sql/4792 "2025-04-04T12:40:15Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![OliverBarrett](https://avatars.discourse-cdn.com/v4/letter/o/f19dbf/32.png) [@OliverBarrett](https://the.fmsoup.org/u/OliverBarrett)\ **Post date:** [April 4, 2025, 12:40pm UTC](https://the.fmsoup.org/t/slow-basic-sql/4792/1 "2025-04-04T12:40:15Z")
We have a simple SUM SQL on less than 30K rows (400 fields per row) using ExecuteSQL(). No joins. No Where. Just SUM. In MariaDB, _this query runs in sub-second,_ but in FileMaker ... it hangs! No idea why such a simple query would slow to the point of non-usability so I thought I'd ask for any suggestions. Thanks. ---
**Author:** ![MonkeybreadSoftware](https://yyz2.discourse-cdn.com/flex030/user_avatar/the.fmsoup.org/monkeybreadsoftware/32/361_2.png) [@MonkeybreadSoftware](https://the.fmsoup.org/u/MonkeybreadSoftware)\ **Post date:** [April 4, 2025, 1:13pm UTC](https://the.fmsoup.org/t/slow-basic-sql/4792/2 "2025-04-04T13:13:10Z")
slow network connection? Anyone of these records locked? FileMaker may start downloading the whole table to local cache to run SQL locally. You can avoid that by doing SQL in a script on server (via PSoS). Also if one record is locked on your client, that slows down the whole thing a lot, so better commit/revert before doing SQL. ---
**Author:** ![OliverBarrett](https://avatars.discourse-cdn.com/v4/letter/o/f19dbf/32.png) [@OliverBarrett](https://the.fmsoup.org/u/OliverBarrett)\ **Post date:** [April 4, 2025, 1:41pm UTC](https://the.fmsoup.org/t/slow-basic-sql/4792/3 "2025-04-04T13:41:29Z")
Thanks, but no to all of these. FMP database is local. 128 GB RAM. Fast Computer. No other users. Appreciate your reply. ---
**Author:** ![MonkeybreadSoftware](https://yyz2.discourse-cdn.com/flex030/user_avatar/the.fmsoup.org/monkeybreadsoftware/32/361_2.png) [@MonkeybreadSoftware](https://the.fmsoup.org/u/MonkeybreadSoftware)\ **Post date:** [April 4, 2025, 2:04pm UTC](https://the.fmsoup.org/t/slow-basic-sql/4792/4 "2025-04-04T14:04:48Z")
The cache size is a GB or so? So the table has a chance to fit into the cache. ---
**Author:** ![OliverBarrett](https://avatars.discourse-cdn.com/v4/letter/o/f19dbf/32.png) [@OliverBarrett](https://the.fmsoup.org/u/OliverBarrett)\ **Post date:** [April 4, 2025, 2:42pm UTC](https://the.fmsoup.org/t/slow-basic-sql/4792/5 "2025-04-04T14:42:35Z")
How do I set the cache size? Not sure I understand what you're suggesting, but it sounds promising. ---
**Author:** ![bdbd](https://yyz2.discourse-cdn.com/flex030/user_avatar/the.fmsoup.org/bdbd/32/620_2.png) [@bdbd](https://the.fmsoup.org/u/bdbd)\ **Post date:** [April 4, 2025, 3:56pm UTC](https://the.fmsoup.org/t/slow-basic-sql/4792/6 "2025-04-04T15:56:54Z")
FileMaker Preferences, under Memory. ---
**Author:** ![OliverBarrett](https://avatars.discourse-cdn.com/v4/letter/o/f19dbf/32.png) [@OliverBarrett](https://the.fmsoup.org/u/OliverBarrett)\ **Post date:** [April 4, 2025, 5:57pm UTC](https://the.fmsoup.org/t/slow-basic-sql/4792/7 "2025-04-04T17:57:13Z")
Great tip, thank you!!!! ---
**Author:** ![MonkeybreadSoftware](https://yyz2.discourse-cdn.com/flex030/user_avatar/the.fmsoup.org/monkeybreadsoftware/32/361_2.png) [@MonkeybreadSoftware](https://the.fmsoup.org/u/MonkeybreadSoftware)\ **Post date:** [April 6, 2025, 12:47pm UTC](https://the.fmsoup.org/t/slow-basic-sql/4792/8 "2025-04-06T12:47:27Z")
I had a client last year, where MySQL was horrible slow for some queries. Like 2000 records is done in a second and 20000 records needed 2 minutes, 50000 records needed 10 minutes. We wondered why things got so slow with many data. Until we realized that the server had a cache size, which was smaller than the size of that table. For every run over the records to find something like a related record, the MySQL software had to read the whole table into memory to find the record. Once cache size setting got upgraded, so the table fits into the cache, the searches got faster. Now it's like 5 seconds for 50000 records. Same for FileMaker. If an all data needed for the query fit in the cache in-memory, then the query is much faster, than if FileMaker needs to remove data from the cache to make space for new data. On the other side, all the cache management slows down data access. You need to find a good balance between a too much cache or not enough cache. ---
**Author:** ![weetbicks](https://yyz2.discourse-cdn.com/flex030/user_avatar/the.fmsoup.org/weetbicks/32/206_2.png) [@weetbicks](https://the.fmsoup.org/u/weetbicks)\ **Post date:** [April 6, 2025, 8:34pm UTC](https://the.fmsoup.org/t/slow-basic-sql/4792/9 "2025-04-06T20:34:50Z")
30k records is still a sum of a lot of records. FileMaker SQL is not as optimised as you might think. To sum records, records have to be downloaded from server to client, so if you have 400 fields of data per record, that's a lot of data. You do say local but I'm not sure if you mean local server or local on a machine? Also if you are editing any of those records at the time, SQL will perform far more terribly. What are you summing also? Make sure it's not a summary field πŸ™‚ ---
**Author:** ![Dutchman](https://yyz2.discourse-cdn.com/flex030/user_avatar/the.fmsoup.org/dutchman/32/491_2.png) [@Dutchman](https://the.fmsoup.org/u/Dutchman)\ **Post date:** [April 6, 2025, 11:02pm UTC](https://the.fmsoup.org/t/slow-basic-sql/4792/10 "2025-04-06T23:02:44Z")
Does using PSOS obviate the problem of everything getting downloaded to the client? Also, while I'm asking dumb questions, if not using PSOS, does the data need to be downloaded to the client for each and every eSQL call? Say they are seconds apart and that the underlying data has not changed. I'm assuming the answer is yes, but I'm not sure. ---
**Author:** ![Malcolm](https://yyz2.discourse-cdn.com/flex030/user_avatar/the.fmsoup.org/malcolm/32/196_2.png) [@Malcolm](https://the.fmsoup.org/u/Malcolm)\ **Post date:** [April 7, 2025, 12:14am UTC](https://the.fmsoup.org/t/slow-basic-sql/4792/11 "2025-04-07T00:14:04Z")
> [@Dutchman](#): > > Does using PSOS obviate the problem of everything getting downloaded to the client? Yes. This can be a significant factor in the speed bump you get with PSOS. On the other side of the coin, any PSOS call means generating a session on the server. That requires resources ( nothing is free ). If you create many PSOS calls you must ensure that your server has capacity. ---
**Author:** ![xochi](https://avatars.discourse-cdn.com/v4/letter/x/0ea827/32.png) [@xochi](https://the.fmsoup.org/u/xochi)\ **Post date:** [April 7, 2025, 2:57pm UTC](https://the.fmsoup.org/t/slow-basic-sql/4792/12 "2025-04-07T14:57:37Z")
Running some tests on a large test system. This is a table with about 200 fields and over 8 million records. I'm running this query, which gets about 30k records: ```auto Set Variable $result = ExecuteSQL ( "SELECT SUM (Salary) FROM WorkerDate WHERE \"Date\" = '4/1/2025' "; ","; "ΒΆ" ) ``` **Running script on my mac, server is on LAN w/Gigabit Ethernet** ```auto Pro 21.1.1 SELECT SUM of 33236 records took 0:00:09 seconds for a rate of 3693 records/second Pro 21.1.1 SELECT SUM of 33236 records took 0:00:01 seconds for a rate of 33236 records/second ``` **Running script on server using PSOS** ```auto Server 20.3.4 SELECT SUM of 33236 records took 0:00:02 seconds for a rate of 16618 records/second Server 20.3.4 SELECT SUM of 33236 records took 0:00:02 seconds for a rate of 16618 records/second ``` Interesting findings: - running on FMPro, record caching is very important: the second time I run the script the records are already cached locally, so the speed is nearly 10x higher - running on FMServer, using PSOS is much faster, but it also appears there is no benefit of caching (which makes sense, as each PSOS script run basically is it's own separate login session). Notes: - both the Date field I'm searching on, and the Salary field I'm SUM() ing are stored, indexed fields. ---
**Author:** ![xochi](https://avatars.discourse-cdn.com/v4/letter/x/0ea827/32.png) [@xochi](https://the.fmsoup.org/u/xochi)\ **Post date:** [April 7, 2025, 3:00pm UTC](https://the.fmsoup.org/t/slow-basic-sql/4792/13 "2025-04-07T15:00:06Z")
> [@OliverBarrett](#): > > We have a simple SUM SQL on less than 30K rows Are you sure you are only selecting 30K rows? The first time I benchmarked this, I did something like this: ```auto Go to layout Perform Find # find only 30k records ExecuteSQL "SELECT SUM (salary) from Table" ``` And it seemed to hang forever. Oops! I had forgotten that ExecuteSQL **does not inherit the current found set** , so it was operating on over 8 million records. ---
**Author:** ![OliverBarrett](https://avatars.discourse-cdn.com/v4/letter/o/f19dbf/32.png) [@OliverBarrett](https://the.fmsoup.org/u/OliverBarrett)\ **Post date:** [April 8, 2025, 2:28pm UTC](https://the.fmsoup.org/t/slow-basic-sql/4792/14 "2025-04-08T14:28:15Z")
Ours was just a SUM on a single field. Your results are very interesting. In our case FMP hangs whether using standalone or via FMS. ---
**Author:** ![xochi](https://avatars.discourse-cdn.com/v4/letter/x/0ea827/32.png) [@xochi](https://the.fmsoup.org/u/xochi)\ **Post date:** [April 8, 2025, 3:11pm UTC](https://the.fmsoup.org/t/slow-basic-sql/4792/15 "2025-04-08T15:11:43Z")
Can you share your SQL? ---
**Author:** ![OliverBarrett](https://avatars.discourse-cdn.com/v4/letter/o/f19dbf/32.png) [@OliverBarrett](https://the.fmsoup.org/u/OliverBarrett)\ **Post date:** [April 8, 2025, 5:48pm UTC](https://the.fmsoup.org/t/slow-basic-sql/4792/16 "2025-04-08T17:48:41Z")
It was just like: SELECT SUM(Quantity) FROM TABLE ---
**Author:** ![xochi](https://avatars.discourse-cdn.com/v4/letter/x/0ea827/32.png) [@xochi](https://the.fmsoup.org/u/xochi)\ **Post date:** [April 8, 2025, 6:02pm UTC](https://the.fmsoup.org/t/slow-basic-sql/4792/17 "2025-04-08T18:02:01Z")
`SELECT SUM(Quantity) FROM TABLE` That will sum records from the entire table, since ExecuteSQL doesn't care about the current Found set. If your table only has 30K records, then that's what you intended, but if you meant to select a 30K subset from a larger table, you need to include a ```auto ... WHERE X = Y ``` clause to limit the rows. ---
**Author:** ![OliverBarrett](https://avatars.discourse-cdn.com/v4/letter/o/f19dbf/32.png) [@OliverBarrett](https://the.fmsoup.org/u/OliverBarrett)\ **Post date:** [April 8, 2025, 6:20pm UTC](https://the.fmsoup.org/t/slow-basic-sql/4792/18 "2025-04-08T18:20:58Z")
We want to sum the entire table as it only has 30K rows. No WHERE clause used for that reason. I think the problem is what I thought was a numeric field was actually a calculation field, possibly unstored so SQL is waiting on each row's updated calculation. ---
**Author:** ![steve\_ssh](https://yyz2.discourse-cdn.com/flex030/user_avatar/the.fmsoup.org/steve_ssh/32/160_2.png) [@steve\_ssh](https://the.fmsoup.org/u/steve_ssh)\ **Post date:** [April 8, 2025, 7:57pm UTC](https://the.fmsoup.org/t/slow-basic-sql/4792/19 "2025-04-08T19:57:36Z")
> [@OliverBarrett](#): > > I think the problem is what I thought was a numeric field was actually a calculation field, possibly unstored so SQL is waiting on each row's updated calculation. That sounds like a super-plausible explanation. I was kind of wondering if that might have been the case. ---
**Author:** ![OliverBarrett](https://avatars.discourse-cdn.com/v4/letter/o/f19dbf/32.png) [@OliverBarrett](https://the.fmsoup.org/u/OliverBarrett)\ **Post date:** [April 9, 2025, 10:40am UTC](https://the.fmsoup.org/t/slow-basic-sql/4792/20 "2025-04-09T10:40:46Z")
Thanks Steve. πŸ™‚