mac | pc Instances of when a server-side script is slower than client-side?

[James Gill]

I'm currently working on a solution that has a fairly complex client-side export that takes place multiple times per day by multiple users.  Users are able to create their own custom exports using an interface with a list of available fields and then export to a formatted spreadsheet with custom header rows for readability.  Some of the fields that they are exporting contain large un-stored calculations that need to be calculated on the fly, so larger exports (in my case, 800ish records with many, many sub-records) can take in excess of 10 minutes to process.

 

I was hoping to speed up that process by offloading the export to the server however I've found that for my export, processing via Perform Script on Server actually results in a slower time to execute rather than a speed increase.  Are there specific scenarios where you're better off keeping exports on the client rather than performing them on the server?

 

I've also tried to narrow down the performance issues by looking at performance logs and the Resource monitor that's built into windows.  Unfortunately I haven't really found anything useful with the Resource Monitor, because neither CPU, RAM, Disk, or Network resources even go above 5% utilization when the export is being processed.  The only indication that there's something going on and is causing a bottleneck is seen in the Filemaker Server monitor window, where Elapsed Time (~17,000) and Wait Time (~10,000) both spike while the export is being processed.

[Wim Decorte]

 

I was hoping to speed up that process by offloading the export to the server however I've found that for my export, processing via Perform Script on Server actually results in a slower time to execute rather than a speed increase.  Are there specific scenarios where you're better off keeping exports on the client rather than performing them on the server?

 

Lots of factors.   You can't simply use PSoS and expect it to be faster,  The performance on the server depends on how busy the server is and how fast the server is.

You need to do a baseline performance analysis on the server to see how well it copes with its current tasks before you can decide how it can handle the extra load.

[James Gill]

Lots of factors.   You can't simply use PSoS and expect it to be faster,  The performance on the server depends on how busy the server is and how fast the server is.

You need to do a baseline performance analysis on the server to see how well it copes with its current tasks before you can decide how it can handle the extra load.

 

Baseline performance indicator is this:  FMServer never uses more than 10% CPU usage, RAM is 30% usage under heavy load, Disk Activity is absurdly low except for when backups are running.  That's what's so frustrating about some of the performance issues I run across with Filemaker server.  With the aforementioned exception of backups, during normal daily use this server is barely being taxed at all, yet during these extremely complex exports the server seemingly isn't struggling with high CPU usage or abnormal disk I/O and yet these exports take forever to complete regardless of if they are performed on the server or client.

[Wim Decorte]

Baseline performance indicator is this:  FMServer never uses more than 10% CPU usage, 

 

Measured how and how over how long?

CPU usage on the OS is not a good indicator.  What you are looking for are the FMS counters "elapsed time per call" and "wait time per call" and you have to collect them over a reasonable length of time to see patterns.  Meaning weeks, not days.

yet during these extremely complex exports the server seemingly isn't struggling with high CPU usage or abnormal disk I/O and yet these exports take forever to complete regardless of if they are performed on the server or client.

 

Here again, what is the "elapsed time/call" and "wait time/call" say?

 

How many records are imported, into how many fields?

How many of those fields are indexed?  How many are stored calculations?

If you are on a layout that actually shows fields: any unstored calculations or portals on that layout>

[xochi]

Some of the fields that they are exporting contain large un-stored calculations that need to be calculated on the fly, so larger exports (in my case, 800ish records with many, many sub-records) can take in excess of 10 minutes to process.

 

James, this doesn't answer your question exactly, but have you thought about converting some of these unstored calculations into stored ones?

 

I use a technique which I call "triggered auto enter calculation" fields:

• Add a new field to your table called "TriggerField"
• For each field that you want to optimize...
• Copy the calculation formula for the field
• Convert the field from Calculation to normal (Text, Number, Date)
• Turn on an auto-enter calc and add this formula:

Let(
  // this is a triggered auto-enter field
  // to update the value, simply set TriggerField to any value

   triggerMe = TriggerField;  // this triggers the calculation

  // put your calculation here
  a*b*c* Sum(RelatedField::something)
)

To use: whenever you need an updated value, just touch the value in TriggerField.  Or, to update an entire table, do a ReplaceFieldContents(TriggerField;1).Since the field can be fully indexed, it can be MUCH faster.

 

What you are doing is trading size and convenience for speed.   Size: since the field is stored, it takes up more room.  Convenience:  you have to script the Triggers at the right time.

 

I've seen 1000x speed improvements with this technique.