[UPDATE] Performance Regression Analysis And Recommendations

[Ocean West]

[blurb]

[color:red] The authors have been advised by FileMaker, Inc, that "FileMaker is

planning a product update that allows FileMaker Server to continue to

process most queries against unstored calculations, and thus addressing

much of the current performance issue."

[big]PERFORMANCE REGRESSION IN FILEMAKER® PRO 8.0V2

AN ANALYSIS AND RECOMMENDATIONS[/big]

By:

Steven H. Blackwell, Management Counseling Services

Deborah A. Fuchs, Aptworks Consulting

Wim Decorte, Connecting Data

The 8.0v2 updates to FileMaker® Pro 8 and FileMaker® Pro 8 Advanced contain a very significant, virtually unheralded, change to the operation of the products. This change will affect almost all solutions, especially when hosted by FileMaker® Server 7 or FileMaker® Server 8.

FileMaker Pro 8.0v1 had a bug whereby certain unstored calculations could not run correctly on the host, and the field containing then became unsearchable. To resolve this issue FileMaker, Inc. made a major change in the product:

In FileMaker Pro 8.0v2 all unstored finds are now performed locally…. This applies to FileMaker Pro 8.0v2 with either FileMaker Server 7 or FileMaker Server 8.

This behavior reverts the product to its pre–FileMaker Pro 7 behavior. As a result, performance of a number of functions on both Local Area Networks and Wide Area Networks is materially to substantially degraded. In this mini–white–paper, we will analyze some of the underlying behavior and offer recommendations for remediation.

[/blurb]

In FileMaker® Pro 7 almost all calculations were evaluated and almost all searches were performed on the server with just the results being returned to the client. This led to fundamental improvements in LAN/WAN based performance on unstored finds. In earlier versions (FileMaker® Pro 5.5 and FileMaker® Pro 6, for example) unstored searches occurred on the client after FileMaker Server had sent down the entire record set (including data from all records in the table being searched, as well as from any other records required to perform the search) to the client. This produced significant bottlenecks and very slow network performance, especially on WAN’s. FileMaker Pro 7 and FileMaker® Server 7, in contrast, performed unstored searches on the server, sending back the resulting record set as a bitmap. That is, the server simply returned a string of ones and zeros indicating, for each record position in a given table, its presence (or lack thereof) in the found set. Furthermore, where the resulting bitmap was “sparse” (e.g., contained long strings of zeros amongst very few ones), compression techniques were used to represent the bitmap in as little space as practical. For example, rather than sending a bitmap like this:

“00000010110000000000000000000000011000000000000000…000”

The server might instead send the same information in the following way:

“1011 at position 7; 11 at position 34”

In either case, the client would be informed that the found set should consist of records 7, 9 and 10, 34, and 35 only.

In FileMaker Pro 6 and FileMaker Server 5.5 the behavior for unstored searches was for the client to request all necessary data from the server in order to determine the search results. This meant that in a WAN situation, the user had to wait on the order of C*p*n seconds for the data transfer alone, where “p” is the round trip latency (ping time) of communication between client and server, where “n” is the number of uncached records involved in the search, and were “C” is a fixed number (greater than 1) based on factors in the environment (hard disk speeds, average record size, etc.).

So, for example, in a search on the unstored calculation “case(status=“open”; sum(line_items::amount))” from the context of an invoice table in a typical invoicing solution, “n” would be the sum of a) the total number of invoice records in the invoice table and the total number of line item records related to any “open” invoice records. With a “p” of 25ms and an “n” of 10000 records, a WAN user might be waiting around upwards of 25 seconds of data transfer in FileMaker 5.5 or 6.0 on a search that might seem almost instantaneous in FileMaker 7. (Note that as of FileMaker 7, records are now sent in packets of 5, so “p” might be replaced by “p/5” in the formula above when calculating speeds for FileMaker Pro 8.0v2. Also note that this formula is only helpful if “p” represents a WAN environment where ping time is large enough to dwarf many other important factors not accounted for here.)

The authors have observed some order of magnitude changes in the following scenarios:

Deployment Scenario on 8.0v1

Search against Server 8 portal to table broadband WAN -12 seconds

Search against Server 8 portal to table LAN - 1 seconds

Search against Server 7 for field value 200K records WAN Broadband - 88 seconds

Search against Server 7 for field value 200K records LAN - 89 seconds

Deployment Scenario on 8.0v2

Search against Server 8 portal to table broadband WAN - 470 seconds

Search against Server 8 portal to table LAN - 10 seconds

Search against Server 7 for field value 200K records WAN Broadband - 1997 seconds

Search against Server 7 for field value 200K records LAN - 385 seconds

What do developers and IS/IT managers need to know and to do about managing the consequences of these changes?

• Analyze your solutions to see to what degree they are using unstored calculations. Then make some preliminary estimates of impact based on the amount of data you expect to have going across the network in each scenario.
  
  
• Analyze your server and workstation hardware profiles, general network and server performance.. This will help reduce factors “C” and “p” by making sure there are no easily avoidable bottlenecks in the increased communication process between FileMaker Server and FileMaker Pro. Both Windows and Macintosh have built-in tools to help you measure and troubleshoot a wide variety of machine subsystems. On Windows, look for the “Task Manager” for a quick overview and for the “Performance Monitor” for in-depth information. On Macintosh OS X look for the “Activity Monitor” and “Server Monitor” respectively. We will provide more detailed information on how to use these tools in relation with FileMaker Server 8 in a future article. In addition to these performance related tools, also check the event logs frequently. These logs are a prime source of information on what is happening on the system, what is not working and– when using quality server hardware–also early warning of things about to fail.
  
  
• If Server side network bandwidth shows to be a problem, remember that unlike FileMaker Server 5.5 and earlier, FileMaker Server 7 and 8 can take advantage of multiple network cards, either by hosting through different IP addresses or by combining the throughput of multiple network cards through one IP address.
  
  
• Adjust the FileMaker Pro cache on your workstations from the default install value of 8 MB to approximately 14 MB. This will help second and subsequent searches and sorts of the same data occur faster.
  
  
• Keep a copy of FileMaker Pro 8.0v1 installed on a machine on the network so that you can use it to test any unexpected slowness that might occur before engaging in too much other troubleshooting. Remember that some finds may fail; fixing that was one reason for the v–rev.
  

In this mini–white–paper we have described a changed behavior in FileMaker Pro 8.0v2 related to unstored calculations, some of external impacts the changed behavior has caused, and some recommendations for managing the effects of that change.

#########

[smaller]FileMaker Solutions Alliance members are independent entities without authority to bind FileMaker, Inc. and FileMaker, Inc. is not responsible or liable for their actions.

The views and recommendations expressed in this White Paper are solely those of the authors, and may not necessarily reflect those of FileMaker, Inc. FileMaker Pro® and FileMaker(® Server are registered trademarks of FileMaker, Inc. of Santa Clara, California.

Copyright. ©, 2006, Wim Decorte, Deborah A. Fuchs, and, Steven H. Blackwell. All rights reserved.

[/smaller]

RegressionArticleFinalv2.pdf

[Steven Cappiello]

I had a problem that seems to be related to this bug..

I (think I) got around it by creating another field which was a lookup of the unstored field.

[gdurniak]

in tech info answer 5678, filemaker makes it sound like unstored finds on the server never worked quite right:

"This was an issue with certain unstored calculations such as calculations that use a Get(StatusAreaState) or something else that must be done locally. Unfortunately, in some more complex cases, the calculation could not run correctly on the host, and the field became unsearchable"

I am curious how this feature evolved. It sounds like it was doomed from the start

[Steven Cappiello]

Since this slowdown seem to be so significant..

I would like to know how 8.0v2 compares to the latest version of 7

which , as I understand, does not have the bug ?

Granted.. I'm a super newbie compared to almost everyone on this board... but, If 7 didn't have the bug... it leads me to believe that there is hope that they can fix this problem without having to trade off the speed. Is there any chance ?

[Ocean West]

Update...

The authors have been advised by FileMaker, Inc, that "FileMaker is

planning a product update that allows FileMaker Server to continue to

process most queries against unstored calculations, and thus addressing

much of the current performance issue."

:tigger:

[J]

Hmmm.

That was the whole reason I switched to Filemaker. I have spent over $4,000 for software and I am about to deploy.

I hope they get this fixed, and fixed FAST!

Are there any potential workarounds that are not to hard? What about just making them stored? What will the result be?

It sounds like a problem that can be fixed. It is probably going to be very server intensive.

[Steven H. Blackwell]

They have said they are planning an update to address this issue. When they are ready, they will make an announcement that it is available. If you can avoid making a calculation unstored, then do that. Also please read the article's recommendations at the end of the paper.

Steven

[xochi]

My guess is that even when they fix the bug, it will still always be true that searches on stored indexed fields will be faster than on unstored calcs.

So, if you find yourself doing a lot of searching on unstored calcs, you might want to consider making them stored fields, either traditional lookups, or a triggered auto-enter field. In either case, you will need to make sure you are updating these when appropriate...

[Ender]

From the mini white paper:

In FileMaker Pro 8.0v2 all unstored finds are now performed locally…. This applies to FileMaker Pro 8.0v2 with either FileMaker Server 7 or FileMaker Server 8.

It's not clear from this or the rest of the article, but I first encountered a change in behavior after updating my server to 8v2. My clients were running version 7. With this configuration, finds on unstored calcs failed (as if it weren't a criteria.) There were no such problems with Server 8v1. Updating the clients to 8v2 allowed these finds to perform correctly (with the noted slower performance as indicated in the mini white paper.)

This change with Server 8v2 is significant, as it forces us to upgrade all our clients to version 8 at a significant expense, or rework a number of Finds so they can perform correctly.

[Ender]

So, if you find yourself doing a lot of searching on unstored calcs, you might want to consider making them stored fields, either traditional lookups, or a triggered auto-enter field.

Yes, this method of using Lookups or auto-enter calcs is a technique to get around this problem in some situations, but it doesn't work for some cases, and I don't like to de-normalize a solution.

The bigger problem for me, is dealing with calcs that are necessarily unstored, like things based on the current date, or finds on aggregate calcs like sum(), count(), or last(). I've resorted to first finding by the stored criteria, then using a looping script to reduce the found set by that unstored criteria. Not pretty, but it works for those reports my FM7 clients need until I can get everyone upgraded to 8.

[Ocean West]

Update...

http://filemaker.custhelp.com/cgi-bin/filemaker.cfg/php/enduser/std_adp.php?p_faqid=5907&source=Pug%20News

[Ender]

Thanks for the update, Stephen. Good to know.

[jmkimmel]

The focus of most of the concern seems to be on unstored calculations in 8v2, has anyone been noticing a change in performance related to accessing records in a portal, or related table (like setting a field to a certain value)?

I just switched our company from a 6.0 solution to 8.0v2 with the 8 server and I am seeing problems with some of my related files that were not an issue in that version, and not a problem in my development version, but now it is sometimes taking 12 seconds for something that should be taking a fraction of a second.

I am just wondering if anyone else is seeing similar issues. I am in the process of turning off all of my indexes to see if any of them just got messed up along the way.

[xochi]

I just switched our company from a 6.0 solution to 8.0v2 with the 8 server and I am seeing problems with some of my related files that were not an issue in that version, and not a problem in my development version, but now it is sometimes taking 12 seconds for something that should be taking a fraction of a second.

Could it be a file reference problem, where FM8 is looking for a non-existent file reference on the network, then finally giving up?

[IT_boy]

check this out: http://www.filemaker.com/support/downloads/index.html

filemaker 8.03 updates

Edited April 26, 2006 by Guest

[Steven Cappiello]

Anyone know how well these updates address the problem ?

[jaydonne]

Anyone know how well these updates address the problem ?

Looking good here so far!