Problem Solving a Performance Issue (Blog)

Discussion created by philmodjunk on Jun 1, 2017
Latest reply on Jun 10, 2017 by philmodjunk

This is just a "My problem and how I solved it" post that might be helpful to others.


I was working with a series of list view reports where the header showed fields from a parent record on a layout based on the child table. These are list view layouts based on the child table with 1 to 20 or so child records listed in the found set.


The problem:


I also have a list view layout based on the parent table that serves as a kind of "table of contents". Click a row in this list view and Go To Related Records (GTRR) changes layouts to the list view layout and brings up a found set of records to list with fields from the selected parent in the header. Nothing fancy here, pretty plain vanilla layout with narrow tables and the tables themselves had less than 10,000 records and I was working over a LAN. This is something that I've done more times than I can count with many different versions of FileMaker.


But when I clicked that row in the TOC layout, It took 3-4 seconds to bring up the records on the other layout--not good and definitely not expected behavior. So I started checking for possible performance issues to rule out:


a) the tables were fairly "narrow". Not only were there not a large number of records (and hence indexes should not be too huge) but there were less than 30 fields in the parent table and bout the same number of fields in the child table.

b) there was a conditional format formula set on the 8 fields listed in the body. But it was a simple = comparison of a constant to a simple stored text field in the child table. (Plus the delay occurred even if there was only one record in the found set produced by the GTRR.)

c) at first it looked like the GTRR itself was "slow" as that was the step where things "hung" for a few seconds when I tested the GTRR script in the debugger, but then I realized that with the debugger up, the screen refreshed on every script step so that really wasn't showing me what I thought. This false conclusion on my part did focus my attention issues related to the data model at first.

d) the relationship between parent and child was Multi-predicate. There were 4 match fields involved, but a very similar relationship with a different pair of tables and 10x's more records didn't show any delays.

e) this was a "legacy" file--the original copy was created many version ago by someone else and I was retrofitting/refactoring it to be a much better version of the original. (And one that replaced 11 files with identical type of data (but from 11 different time periods) with one unified file.) Being a legacy file meant that hidden corruption could easily lurk somewhere within the file and be the source of this issue.


So my next steps were to test for issues at the data level: Relationships, and indexing. I temporarily removed a number of TO's from the data model. I didn't consider it likely that they were a cause of the issue, but I was considering removing them anyway since they weren't needed for this particular table occurrence so I checked that first. Unsuprisingly, it made no difference.


I then took a copy of the file down off the server and recovered it. No issues found. I then recovered the original copy again, but used advanced recover options to only rebuild all indexes and delete cached data. I put that copy back up on the server and tested. That made no difference.


I then started checking for layout issues. Since there were no obvious performance bottlenecks in the layout design that I could identify--no filtered portals, only very simple conditional formatting, only a few unstored calculations that all stemmed from a sum calculation that summed a number field in the child table (which might only have 1 child record and still had the same delay). I created a completely blank, new layout based on the same occurrence of the child table and redirected the GTRR step to go to that layout. Bam! the delay disappeared. Now I was getting somewhere!


Being no stranger to "Divide and conquer" algorithms, I set about systematically creating copies of the original layout and deleting different groups of layout objects before redirecting the GTRR and testing. Each time, I tried to remove about half of the suspect objects to get to the problem objects as quickly as possible. I quickly determined that none of the Header objects were the source of the delay. It was something to do with those eight fields in the body....


Since all 8 where conditionally formatted with the same formula and color, I first removed that just in case there was some issue with that very simple formula. I was unsurprised to discover that it was not the source of the delay. I then quickly narrowed the field to four field objects on the layout. It then got a bit tricky as I discovered that more than one field object was causing the same delay. Out of the 4 remaining field objects, 3 were corrupted in some way that was causing a delay getting the layout to update. But eventually I worked out which were which. By deleting those three field objects and using the field tool to place brand new copies of those three fields onto the layout, I was finally able to exorcise this particular performance demon.


In retrospect, given the fairly simple layout involved, I probably would have been able to fix this more quickly by just creating a brand new layout and rebuilding it "from scratch", but part of me really wanted to know exactly what objects on that layout were the culprit.