Hi all,

I'm current running a script on FileMaker Server 2024 every 15 minutes. It starts by going to a layout where the primary table is an ODBC connection to the MySQL server of a website (left side of image). On the layout is also a portal (right side of image) which is the relational data in the FileMaker database. It's a basic relationship using the EmployeeID and Email to match records on both sides.

The left side data consists of users who have registered on the website (WordPress). The right side is the FileMaker membership database used by the staff to maintain membership records. When this script runs, it goes through all the records on the left (basically all the users on the website) and compares it to the data in FileMaker. For each record on the left (MySQL), the script looks to see if there is a related record on the right side (FileMaker).

If there's no record in FileMaker or someone is marked as "Inactive", SQL code is entered into the Access Level field which blocks the user's access on the website. It will also permit access depending on the criteria. It works very well and only takes about two minutes to run through 700 records. 

I am starting to get some intermittent time out errors (error 691) on the server. It ran fine for a week, and I am now getting more errors about once an hour. I was wondering if anyone had any suggestions how to troubleshoot this?

 

I don’t see anything inherently wrong with this script and I would remove the use of global variables in exchange for local. It can be optimized by entering find and then going to a layout. You do not need to show all before finding. You’re not trapping for find errors after line 9. Is line 5 getting a value from a summary field? What’s the definition of the field referenced on line 21? You’re not handling finding more than one record. I’d exit after last On, even though you should exit just before. Can’t see subscripts, but hopefully they don’t rely on the global vars. It’s better to pass parameters. 

sorting may also cost performance  I do not see its purpose and would remove that step.

and overall, a lot of set fields in a loop is a lot of commits. You’ll substantially improve performance If you wrap in a transaction. 
 

more suggestions : think about having just one script that uses the send mail script step and pass parameters to it. This way, if and when you switch to a different method you only have one place to refactor. 
 

at line 21 and 41 what are you doing? Are you checking for a related record? Better method would be to use not isempty (relationship ::primary key). 

Thanks so much for your detailed suggestions!

• I fixed those global variables, it's an old bad habit.
• I'll take a look at passing parameters to a single script. 
• The sorting was originally for testing, I have removed it.
• "I’d exit after last On, even though you should exit just before." - So I was doing this, but I kept seeing a 101 Error in the server log. It is a correct error, but was driving me nuts, so that's why I used that approach.
• "You’ll substantially improve performance If you wrap in a transaction. " - I will take a look at this as well.

Again @bcooney, thanks so much for the help!