Upgraded from FM12 to FM16, Find in Progress... Delays

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.

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

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

I will get the file uploaded today. And PM you the dropbox download link.