After upgrading to FM16, the layouts we had been using for years have no started loading with Find in progress.. Processing query.
Even if there isn't a query in process. Sometimes this doesn't show up, and other times it shows up constantly and locks up FileMaker Pro Client. I am not sure what is causing it or why it will appear for 5 - 15 min or freeze filemaker.
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.
NateBrock73072:
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.
TSGal
FileMaker, Inc.
NateBrock73072:
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?
TSGal
FileMaker, Inc.
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
NateBrock73072:
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?
TSGal
FileMaker, Inc.
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
HAVING SUM(T.Qty)>0
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
NateBrock73072:
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.
TSGal
FileMaker, Inc.
I will get the file uploaded today. And PM you the dropbox download link.
We are also seeing this:
NateBrock73072:
Our Testing department has been able to reproduce the issue in both FileMaker Server/Pro 15 and FileMaker Server/Pro 16. The notes from Testing are a bit cryptic, but it appears if you comment out the script step:
Set Variable [ $PID ; ... ]
... the result time is 1 millisecond for all cases.
All information has been sent to Development for further review.
TSGal
FileMaker, Inc.
Should I just remove that variable ?
Sent from my iPhone
NateBrock73072:
I apologize for any confusion. I wasn't making any recommendations. I was simply stating what Testing had discovered.
If you don't need the variable, then simply comment out the line. If you need the variable, perhaps moving the Set Variable step to another line may suffice. Regardless, the information has been sent to Development for further review.
TSGal
FileMaker, Inc.
I am having the exact same problem upgrading to new version but it isn't with execute sql. We had native finds that just keep hanging now. The one has been running for minutes and nothing, I end up having to cancel
cbuck
Chuck,
We also have Enter Find Mode - Load search data - Perform find scripts that are hanging as well.
In the FileMaker Server Statistics for Calls we are seeing stuff like this stack qucikly:
{2839823/0}
Admin
Tommy
Bobby
{2838292/1}
{2389211/0}
Patty
Roger
These id codes instead of user names even though no users are being disconnected when these start stacking up and elapsed time start reaching 20,000,000 everyone experiences slowness problems and gets stuck behind them in a queue like fashion, I am not sure why this behavior is happing. I just upgraded from FM Server 12 Advanced and had never seen this before.
Very strange behavior.
I spoke with Tucker at the FileMaker DevCon and he said you should be seeing tons of these disconnect threads showing up all the time : {2839823/0}
cbuck:
My apologies for the late reply.
What version of FileMaker Server did you upgrade from?
What operating system and version resides on the server?
How many files are being hosted? How many records in the files? How many records in tables?
How many clients are accessing the server? Are the clients FileMaker Pro? FileMaker Go? WebDirect? Custom Web Publishing?
Any other information you can provide about your computing environment may be helpful in narrowing down the cause.
TSGal
FileMaker, Inc.,
Filemaker server is 16
Operating system of server is windows server 2016
Files hosted at most is 10, on average 5k records per file and 1-2 tables per file
At most no more than 20 clients at a time accessing. Mostly via filemaker go
I can't remember how I fixed this but I beleive I had to totally remove the files from server, then readd the files which then when the clients opened it forced it to recalculate. It would be nice to force a recalculate say at 3am or something
My client has exactly the same issue since we upgraded to FileMaker 15.
Here is our environment
FileMaker Server 15, FileMaker Pro 15, Filemaker Advanced 15.
Windows Server 2012 R2, Virtual Server dedicated FileMaker Server 15. No WebDirect
We have tree terminal servers which FileMaker Pro is installed. and Outlook.
40 clients
40 files
We have a main table with 1200 fields et a main interface File with 770 layouts.
When I expect the TopCallStat log file, during the message 'FIND IN PROGRESS', I see Query (Find) on field used in relationship which is used on a portal or in condition of conditional formatting.
And it's not constant. Some times, we see this message very very frequently and some times, the system works very well and no messages.
Do you have any idea ?
Thank you for your help
nadianafi:
Thank you for the information.
Additional notes from Testing mention the issue becomes more noticeable when two clients are executing a Find on the same layout, simultaneously. Other than that, there is nothing new to report. All information is with Development for further review.
TSGal
FileMaker, Inc.
Another day of multi-million ms processes, chugging away.
Your dev team is 100% correct it happens mostly on simultaneous finds on same layout with 2 or more users.Which is very common for an inventory system for many users to work inside a parts catalog.
We are also experiencing this issue on a solution on server 15.
The users are very very frustrated