Speed differences 16 -> 17 for large tables

Discussion created by user27087 on Jun 11, 2018
Latest reply on Aug 21, 2018 by TSGal

We were initially very happy with the overall speed results of switching server from 16 to 17, but have found one potential problem that we do not think has been an issue prior.


We have one huge table with 300M+ rows, and it runs quite well for both FMP and Web users. Searches and sorts can get a little complex, but most reports (search, sort, summarize) run between 5 and 60 seconds, which is much faster than the SQL products that we compete against. There are several reasons we beat the competitors in speed, but that is another topic.


All issues are with FM Pro clients, assumed to be with Go clients as well.


FMS 16: when running the first report (or viewing a layout with the large table or managing the database, etc.) there was always a 20 second overhead on a good remote connection, much longer for iOS and poor connections. I understand this was some kind of internal key passing thing to open new tables. Not a problem, because it happened only once and subsequent reports were fast.


FMS 17: "initialization" of the data table is almost instantaneous, or about 2-3 seconds. This is a bonus because we do not have to apologize and explain the additional time for the first report when demonstrating. Nice.


But when two people run a report at the same time, FMS 17 chokes and the total time for both users is 3-10x longer as the server muddles through the find request. This can be seen in the TopCallStats. It's as though the server doesn't know whether to cut fish or bait, and instead of parsing half the time between them or allowing one to finish first, it simply slows both users to a crawl.


This happens if we are running two different searches and/or are at two different locations (so it is not local issue or packet storms.) Server CPU goes to 20% (as per admin console) and stays there, then releases both users at the same time.


Anyone notice differences on large tables? I think the table has to be over 10M before you'd notice the overhead in first reference.


Anyone notice search "collisions" between two users? Note: this could have been a problem prior to 17, so I'll report back when we test on an older server.