We are also experiencing this issue on a solution on server 15.
The users are very very frustrated
I have found that it stems from using SQL Execute Functions.
They seem to be handled differently and when one gets stuck all end users experience this delay.
I have to disconnect all users and reboot the server.
I ended up passing all SQL Execute Scripts to the server with "Perform Script on Server, Do not Wait for Completion".
This has helped so far but only 1 day in with this change. Also notices if you had an execute sql in a field it would lock up the layouts.
I maybe way off but this it my current thought on the issue at the moment.
Thank you for your post.
Can you describe your solution in more detail?
Are you running under Windows or Mac operating system? What version?
Are you linking to any other files or external data sources? If so, what files and/or data sources?
Any other information about your file and/or computing environment could be helpful.
Your latest post arrived the same time as my questions. Thank you for the information.
Depending on the SQL query, it may take a long time. Are you noticing what SQL queries are taking a longer time to process?
Since you mention "Perform Script on Server", I now know that the file is hosted. What version of FileMaker Server are you using? What operating system and version is on the server?
FileMaker Server 16
Windows Server 2012 R2 Datacenter (Latest Windows Updates Installed)
*This is a dedicated FileMaker Server 16
I also have a 2nd WebDirect server as a Worker Machine.
Both FileMaker Server 16 and the 2nd Worker Machine host web direct.
We are not using web direct yet for clients.
VMware 6.5 ESXi
SSD NAS Storage
Mac and Windows FileMaker Pro 16 Clients are both seeing this problem.
The SQL query has been running for years on FileMaker 12 Server Advanced with FileMaker Pro 12 Clients. It has always ran instantly. It has never frozen up the server. If the SQL query is the root cause of the mysterious dialog then SQL functions got much worse in this release of Filemaker.
I am linking to another FileMaker Database on the same server. However my SQL query is not using any of the tables from that data source.
Here is an example of the Execute SQL :
ExecuteSQL ( "SELECT COUNT(*) FROM \"GCG Order Line Items\" WHERE Backorderedz IS NULL AND \"Reject Order\" IS NULL AND \"Line Item Status\" IS NULL AND fk_PartID > 15000 AND \"Order Submitted\" >TIMESTAMP '2015-12-12 14:35:10'" ; "" ; "" )
This one will freeze up for 5 seconds or longer.
GCG Order Line Items = 10,000 Records
Thank you for the additional information.
Since you now mention that it "has been running for years on FileMaker 12 Server Advanced", I can rule out the SQL query.
Another customer with similar symptoms had the External Data Source path set to a public IP address. As a result, every calculation that processed the records would have to leave the local network, go to the internet, and come back just to talk to the file. Changing from public IP address to relative path solved the issue, so check the path in your file.
Do you have a startup script? If so, does it send any SQL statements?
One of the Problematic SQL Queries is :
Select B.BinLocation, B.Bin, SUM(T.Qty)
FROM Transactions T JOIN Bins B ON T.fk_BinID=B.Bin_pK
WHERE T.fkPartID="&$pid&" AND T.fk_BinID>1000 AND B.ExcludedBin IS NULL
GROUP BY T.fk_BinID, B.BinLocation, B.Bin
It has always worked well until the upgrade.
I just ran it the RazorSQL and the FileMaker 16 JDBC Driver and it returned in 1.33 seconds.
FileMaker is taking a very long time.
Server Side is about 10 seconds
Client Side 1 - 5 min, sometimes locks up.
I do have startup script, but now execute sql or external data sources are used in it.
All my external resources are : File Path:Mydatabase
No public DNS or IP used for the addressing path of the one other FM Database on the same FM Server.
The SQL Statements are running against itself (the file the runs the script) not another data resource.
I am noticing that if you have the execute the SQL on a layout change script, or a layout script trigger that is when it really struggles. If I make a button the layout with the script trigger onload record. Remove the script trigger and just run the execute with a button the client will render in 2-3 seconds.
I need to conduct some more testing to be certain this is the only condition for the intermittent delay.
Okay I was incorrect on that last statement about the layout script triggers.
A Layout button to run one Execute SQL Script. All over the place on 10 test with the same record, and result
Test 1: 540 microseconds
Test 2: 2044 microseconds
Test 3: 1000 microseconds
Test 4: 103433 microseconds
Test 5: 540 microseconds
Test 6: 6040 microseconds
Test 7: 948 microseconds
Test 8: 580 microseconds
Test 9: 21403 microseconds
Test 10: 660 microseconds
I'm not sure what is causing this.
The JDBC Driver Query has almost zero variation in processing time on 10 test.
~ 1300 microseconds
Perform SQL Execute Script on Server Script Step:
Test 1: 4500 microseconds
Test 2: 4200 microseconds
Test 3: 4300 microseconds
Test 4: 4072 microseconds
Test 5: 4300 microseconds
Test 6: 4231 microseconds
Test 7: 4207 microseconds
Perform SQL Execute Script on FM GO 16 iPad Pro: (Latest iOS Version)
Test 1: 2500 microseconds
Test 2: 600 microseconds
Test 3: 15725 microseconds
Test 4: 6140 microseconds
Test 5: 15709 microseconds
Test 6: 15684 microseconds
Test 7: 1502 microseconds
Our Development and Testing departments would like to get a copy of your file for testing purposes. I have sent you a private message with instructions where to send the file.
I will get the file uploaded today. And PM you the dropbox download link.