I am adapting our Seedcode 5.7-based solution for DayBack. I am using an Event Details window (derived from your To-Do event window) rather than the popovers. The Calendar's events table is internal to it (I had previously used data separation), with ~140,000 event records imported from our previous version. Things are generally working fine.

When I click on the Repeat button in the Event Detail Window when the Calendar resides locally, the Repeat window comes up immediately.

However, when the Calendar is hosted on a remote server (with Datatrium), clicking on the Repeat button causes a spinning beachball hang for several minutes before the Repeat window appears.

The hang occurs if the Event itself has no repeats. If it does have repeats, a message pops up immediately informing me of the repeats.

Watching the progression with the Script debugger, I see that the hang occurs in the "Does Event Have Repetitions" script. That script has just one script statement that executes an SQL search of some kind.

Any thoughts on the problem, and how to mitigate it?

Hi wsmiii,

I'm not sure exactly what might be causing this query to take so long, but it could be caused by increased latency to the server. One thing that you might want to try is disabling PSOS by modifying the option in the "Load Calendar Settings - On Startup..." script. You can find more information on PSOS and instructions on disabling it in our docs here: https://www.seedcode.com/pmwiki/index.p ... Maker.PSOS

Let me know if that makes a difference.

Regards,

KC

Hello -

Thanks for your response -

Unfortunately, turning off PSOS didn't make any difference.

I notice there is no delay when invoking Repeat on my Windows test bed, accessing data on the same host, etc. Mac only.

Hi wsmiii,

Thanks for trying it with PSOS off. Since that didn't seem to make a difference, you probably want to change that setting back.

Are your Mac and Windows systems in the same location on the same network? I'd like to verify there's not a noticeable difference in latency between the two systems. You can verify this by pinging your server from the client machines.

Ping test in Windows: https://iihelp.iinet.net.au/How_to_run_a_ping_test
Ping test in MacOS: https://www.wikihow.com/Ping-on-Mac-OS

Let me know the results of those ping tests on the machines and we'll have an idea of where to look next.

Thanks,

KC

I'll check the ping this evening.

In the meantime, I simply stepped around the SQL query and dropped the "Does the Event have Repetitions" FileMaker Pro script from version 5.** of the Calendar into the script, adjusting for the DBK_repeating_ID field.

Seems to work fine, with no delay. If the SQL functionality is needed for other platforms (mobile?), then I can detect the platform and take that detour only on Macs.

Thanks, wsmiii.

That's interesting to hear that the old script is working quicker than the newer SQL query. If you can let me know the results of the ping test, I'll dive into a test file on our server and see if I can replicate the same results.

Regards,

KC

10 pings from the Mac Network Utility to Datatrium:

64 bytes from 67.205.80.19: icmp_seq=0 ttl=110 time=49.889 ms
64 bytes from 67.205.80.19: icmp_seq=1 ttl=110 time=48.010 ms
64 bytes from 67.205.80.19: icmp_seq=2 ttl=110 time=48.703 ms
64 bytes from 67.205.80.19: icmp_seq=3 ttl=110 time=53.336 ms
64 bytes from 67.205.80.19: icmp_seq=4 ttl=110 time=46.647 ms
64 bytes from 67.205.80.19: icmp_seq=5 ttl=110 time=50.449 ms
64 bytes from 67.205.80.19: icmp_seq=6 ttl=110 time=57.214 ms
64 bytes from 67.205.80.19: icmp_seq=7 ttl=110 time=44.703 ms
64 bytes from 67.205.80.19: icmp_seq=8 ttl=110 time=51.167 ms
64 bytes from 67.205.80.19: icmp_seq=9 ttl=110 time=51.740 ms

8 pings from the Windows Command Prompt to Datatrium

Reply from 67.205.80.19: bytes=32 time = 51 ms TTL 110
Reply from 67.205.80.19: bytes=32 time = 54 ms TTL 110
Reply from 67.205.80.19: bytes=32 time = 49 ms TTL 110
Reply from 67.205.80.19: bytes=32 time = 53 ms TTL 110
Reply from 67.205.80.19: bytes=32 time = 68 ms TTL 110
Reply from 67.205.80.19: bytes=32 time = 47 ms TTL 110
Reply from 67.205.80.19: bytes=32 time = 64 ms TTL 110
Reply from 67.205.80.19: bytes=32 time = 47 ms TTL 110

These obtained at the same time, on the same network, etc. So I don't think that's the issue.

Diagnostically, it seems important to me that on the Mac, and when hosted, if there ARE repeats for the Event in question the SQL response is essentially instantaneous (as it is with the old script). It is only when the query is unsuccessful (there are no repeats) that it hangs - for perhaps 4 minutes. So it doesn't handle an SQL null result gracefully under those circumstances.

BTW, the search performed with the old script, on an Event with no repeats, is essentially instantaneous too.

Thanks for the further details, wsmiii.

That should help with troubleshooting a bit.

Regards,

KC

Hi wsmiii,

Testing this on a stock copy of DayBack, the repeat window comes up promptly as expected. After chatting with the team, it sounds like FileMaker's implementation of SQL will have performance issues once you get to about 40,000 records, which would explain why it's not working well in your solution.

If the older version of the script is working well for you, it's probably best to stick with that, rather than trying to make the SQL query perform better. We'll take a deeper look on our end and consider whether this script can be improved in future versions of DayBack.

Thanks for working with me on this one!

KC

Hi wsmiii,

Wonder if there might be something else going on. When you're about to click on the repeat button, can you open the Data Viewer and see if Get ( RecordOpenCount ) returns any open records? If it does, there's a good chance it's the record you're on so see if you can click off to commit that record and return Get ( RecordOpenCount ) to zero before you click the repeat button.

With Get ( RecordOpenCount ) at zero does the performance problem improve?

Reason I suggest this is that if there is an open record, FMS decides that only the client has enough information to process the request and will transfer all the required date to the client before performing the query there. With a large number of records, that could explain the problem you're seeing.

If this pans out, someone should have put a commit record step on the beginning of the repeat button's script. Let us know what you find.

Thanks for the followup. This is the right track.

The bad news: Get ( OpenRecordCount) returns 0 immediately prior to the SQL search. Clicking out of the event, then back in doesn't change anything. Nor does a commit records script step prior to the search.

The good news: Activity Monitor shows FileMaker Pro (15 Advanced) pulling ~51 mb of data, during which it becomes unresponsive. I have about 140,000 records in my events table. So something is triggering a request for the entire table.

A second repeat request shows no download (we've already got the records) and no delay, either with same or a different event record.

Attempting a first repeat on an event that is already in a chain of repeats doesn't trigger the download, but rather immediately returns "This event already has repetitions."

This is Mac only (Sierra in this case. I suppose I could try it in High Sierra). Problem with the FileMaker Pro 15 for MacOS implementation of the SQL search?

Progress =)

Mind trying this:

Edit the script "Show Repeating Events Options Window" and wrap line 18 (the one performing "Does Event Have Repetitions") in an IF statement so it only calls that script if...

not isempty ( GetField ( $$sc_FieldForRepeatingID ) )

Thanks!

The result is that "Does Event Have Repetitions" is not performed.

And it doesn't really need to be if there is no ID in that field =)

My question to you, is if upon creating a new event (one that does not yet have repetitions) if the repetition window now comes up quickly or if you still have the delay. A second question would be if the "this event already has repetitions dialog comes up quickly when there ARE repetitions.

Without this new IF statement, we were protecting against the unlikely case that an event had repetitions but someone had deleted the repetition identifier from the event you're working on. That also means sending a SQL query with no query parameter and I wonder if that's part of what was causing the slow down.

Let me know... and thanks for playing around on this with me.